Answer: Let's
start with a few major areas of partitioned index performance:
- Unique vs non-unique: The
docs note that global partitioned indexes and unique, local
partitioned indexes provide better performance than nonunique
local indexes because they minimize the number of index
partition probes.
- DML vs. SQL: We
need to understand that the primary benefit of local indexes is
manageability and improved DML performance, and not necessarily
SQL performance. In
many ways, the ability to locally partition Oracle indexes has more
performance potential than table partitioning, since index contention is a big
issue as the base tables are updated.
- Parallelism: For
index fast full scans (Index FFS) and multi-partition range
scans, Oracle parallel query will automatically fire-off
additional processes to read each local index partition
separately. But beware, for SQL that only probes for
individual rows, parallelism has no effect.
- Partition pruning: In
some cases of full index scans, local indexes can be isolated to
reduce the total I/O required to satisfy the SQL request.
Index partitioning with Oracle
A
local partitioned index creates a one-for-one match between the
indexes and the partitions in the table. Of course, the key value
for the table partition and the value for the local index must be
identical. The second method is called GLOBAL and allows the
index to have any number of partitions.
The partitioning of the indexes is transparent to all SQL queries. The great
benefit is that the Oracle query engine will scan only the index partition that
is required to service the query, thus speeding up the query significantly. In
addition, the Oracle parallel query engine will sense
that the index is partitioned and will fire simultaneous queries
to scan the indexes.
Local partitioned indexes
Local partitioned indexes allow us to take individual
partitions of a table and indexes offline for maintenance (or
reorganization) without affecting the other partitions and
indexes in the table.
In a local partitioned index, the key values and number of index
partitions will match the number of partitions in the base table.
CREATE INDEX year_idx
on all_fact (order_date)
LOCAL
(PARTITION name_idx1),
(PARTITION name_idx2),
(PARTITION name_idx3);
Oracle will automatically use equal partitioning of the index
based upon the number of partitions in the indexed table. For
example, in the above definition, if we created four indexes on
all_fact, the CREATE INDEX would fail since the partitions do not
match. This equal partition also makes index maintenance easier,
since a single partition can be taken offline and the index
rebuilt without affecting the other partitions in the table.
Global partitioned indexes
A global partitioned index is used for all other indexes
except for the one that is used as the table partition key.
Global indexes partition OLTP (online transaction processing)
applications where fewer index probes are required than with
local partitioned indexes. In the global index partition scheme,
the index is harder to maintain since the index may span
partitions in the base table.
For example, when a table partition is dropped as part of a
reorganization, the entire global index will be affected. When
defining a global partitioned index, the DBA has complete freedom
to specify as many partitions for the index as desired.
Now that we understand the concept, let's examine the Oracle
CREATE INDEX syntax for a globally partitioned index:
CREATE INDEX item_idx
on all_fact (item_nbr)
GLOBAL
(PARTITION city_idx1 VALUES LESS THAN (100)),
(PARTITION city_idx1 VALUES LESS THAN (200)),
(PARTITION city_idx1 VALUES LESS THAN (300)),
(PARTITION city_idx1 VALUES LESS THAN (400)),
(PARTITION city_idx1 VALUES LESS THAN (500));
Here, we see that the item index has been defined with five
partitions, each containing a subset of the index range values.
Note that it is irrelevant that the base table is in three
partitions. In fact, it is acceptable to create a global
partitioned index on a table that does not have any partitioning.
