Oracle Data Structures - Oracle Essentials - page 109

accessed as part of a SQL operation.A storage index is created when the 1 MB section
is initially accessed.
Storage indexes will have the optimal effect when used on data that is sorted and then
loaded. This process would limit the range of values for the sorted columns, which
would make the access elimination more efficient. Storage indexes do not negatively
impact any SQL, but could have different impacts on a statement based on the storage
characteristics of data.
In one sense, a storage index provides the same benefits as a standard index, in that the
storage index improves query response time by reducing I/O operations. However, an
index is used to navigate to a particular row, while a storage index is used to filter the
retrieval of rows needed to satisfy a query.
Partitioning
With the Enterprise Editions of Oracle8 and beyond, you can purchase the Partitioning
Option. As the name implies, this option allows you to partition tables and indexes.
Partitioning a data structure means that you can divide the information in the structure
among multiple physical storage areas. A partitioned data structure is divided based on
column values in the table. You can partition tables based on the range of column values
in the table (often date ranges), or as the result of a hash function (which returns a value
based on a calculation performed on the values in one or more columns). As of Ora‐
cle9 i you can also use a list of values to define a partition, which can be particularly
useful in a data warehouse environment.
Oracle Database 11 g added several partitioning types over its releases. Interval parti‐
tioning provides the ability to automatically generate a new partition of a fixed interval
or range when data to be inserted does not fit into existing partition ranges. Reference
partitioning is used where a parent-child relationship can be defined between tables and
the child table inherits the same partitioning characteristics as the parent. Virtual
column-based partitioning enables partition keys to be defined by virtual columns.
You can have two levels of partitions, called composite partitions , using a combination
of partition methods. Prior to Oracle Database 11 g , you could partition using a com‐
posite of range and hash partitioning. Oracle Database 11 g added the ability to combine
list partitioning with list, range, or hash partitioning, or range partitioning with a
different range partitioning scheme. Oracle Database 12 c adds interval reference par‐
titioning.
Oracle is smart enough to take advantage of partitions to improve performance in two
ways:
• Oracle won't bother to access partitions that don't contain any data to satisfy the
query.