Database Reference
In-Depth Information
Indexed columns populated by current date or current timestamp values can suffer a similar fate because rows
inserted at the same time will have the same column values. So, recent values will be populated in the current rightmost
leaf block of the index. The issue is monotonically increasing values populating index entries in the rightmost leaf block
of the index. Hence, even non-unique indexes can induce right-hand index growth. The root cause of this problem is an
invalid application design issue, and unfortunately the effects are magnified in a RAC database.
There are at least four options to resolve a right hand growth index contention issue:
1.
Partition the indexes by a hash partitioning method with one or more indexed columns as
partitioning keys.
2
With this hash partitioning technique, multiple index trees are created
for an index and therefore, contention is distributed among multiple current rightmost leaf
blocks of the index partitions. Figure
6-3
shows an index partitioned by a hash with two
partitions. Because each partition has its own index tree, the values are spread between
two leaf blocks, thereby reducing contention by half. So, if you partition an index by the
hash partitioning method with 64 partitions, then the values are distributed among 64 leaf
blocks of the index, dividing the contention 64-fold.
Rootblock
Rootblock
1000
1001
1002
1003
1005
1004
Figure 6-3.
Hash-partitioned index
Further, a hash-partitioned index supports the index range scan operation. Hence, the ill
effects of hash partitioning an index are minimal.
2.
Partition the table by hash and recreate indexes as locally partitioned indexes. This
technique has advantages similar to hash-partitioned indexes, but the difference is that
the table is also partitioned by hash. This technique has an added advantage of reducing
the contention in the table blocks. I should caution you that if your queries do not achieve
partition pruning,
3
then logical reads can increase because all index partitions must
be accessed to search for matching rows. For example, consider the EMP table, hash
partitioned by employee_id, with a locally partitioned index on the dept_id column.
Because the dept_id column is not part of the partitioning key, predicates specifying the
dept_id column must search in all index partitions of the dept_id index. This increase in
logical reads will pose a problem only if the SQL statement is executed too frequently.
However, the advantages of a partitioned table outweigh this concern.
2
You need a license for a partitioning option to implement this solution.
3
Partition pruning is an SQL optimization technique to limit the search to few partitions.
