Database Reference
In-Depth Information
The second problem with local partitioned indexes is that they can make the performance worse for SQL
statements that are unable to take advantage of partition pruning. The causes of such situations are described
in the “Range Partitioning” section earlier in this chapter. The impact on index scans might be very high. The
following example, based on the range-partitioned table in Figure
13-5
, shows what the problem might be. At first,
a nonpartitioned index is created. With it, a query retrieves one row by performing four logical reads. This is good.
Notice that the
TABLE ACCESS BY GLOBAL INDEX ROWID
operation indicates that the rowid comes from a global or
nonpartitioned index:
SQL> CREATE INDEX i ON t (n3);
SQL> SELECT * FROM t WHERE n3 = 3885;
------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 4 |
| 1 | TABLE ACCESS BY
GLOBAL
INDEX ROWID| T | 1 | 1 |
4
|
|* 2 | INDEX RANGE SCAN | I | 1 | 1 | 3 |
------------------------------------------------------------------------------
2 - access("N3"=3885)
For the second part of this test, the index is re-created. This time it's a local index. Because the table has 48
partitions, the index will have 48 partitions as well. Because the test query doesn't contain a restriction based on
the partition key, no partition pruning can be performed. This is confirmed not only by the
PARTITION RANGE ALL
operation but also by the
Pstart
and
Pstop
columns. Also notice that the
TABLE ACCESS BY LOCAL INDEX ROWID
operation indicates that the rowid comes from a local partitioned index. The problem with this execution plan is
that instead of executing a single index scan like in the previous case, this time an index scan is performed for each
partition (notice the
Starts
column for operations 2 and 3). Therefore, even if only a single row is retrieved, 50 logical
reads are necessary:
SQL> CREATE INDEX i ON t (n3)
LOCAL
;
SQL> SELECT * FROM t WHERE n3 = 3885;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows | Buffers |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 | 50 |
| 1 | PARTITION RANGE ALL | | 1 | 1 | 48 | 1 | 50 |
| 2 | TABLE ACCESS BY
LOCAL
INDEX ROWID| T | 48 | 1 | 48 | 1 |
50
|
|* 3 | INDEX RANGE SCAN | I | 48 | 1 | 48 | 1 | 49 |
----------------------------------------------------------------------------------------------
3 - access("N3"=3885)
In summary, without partition pruning, the number of logical reads increases proportionally to the number
of partitions. Therefore, as pointed out previously, sometimes it can be better to use a nonpartitioned index than
a partitioned one. Or, as a compromise, it could be good to have a limited number of partitions. Note, though, that
sometimes you have no choice. For example, bitmap indexes can be created only as local indexes.
