No matter how simple a topic you pick, a few minutes thought invariably allow you to conjure up some new anomalies that could appear in the right (or possibly wrong) circumstances.
Yesterday I made a few comments about hash partitioning and performance. Today I suddenly realised that global indexes on hash-partitioned tables could exhibit an unfortunate behaviour pattern that would make them pretty useless – unless hacked or hinted. Consider the following table:
create table t1 partition by hash(id) -- partitions 1 partitions 4 as with generator as ( select --+ materialize rownum id from all_objects where rownum <= 3000 ) select trunc(sysdate) + trunc((rownum-1)/100) trade_date, rownum id, lpad(rownum,10,'0') small_vc, rpad('x',100) padding from generator v1, generator v2 where rownum <= 100000 ; create index t1_i1 on t1(trade_date, id); -- now use dbms_stats to generate statistics
There are 100,000 rows, and I’ve generated data in a way that is essentially time and sequence based. It’s a fairly common pattern of data generation. Choosing to create the specific index as a global index is perhaps a little unusual – but not beyond belief.
Notice how I’ve shown two options for creating this table – four partitions or one partition (which is a lazy way of emulating a non-partitioned table, and also happens to be the basis of a cunning trick to help you migrate from non-partitioned to partitioned tables).
Here’s the issue: based on a block size of 8KB, and in the absence of ASSM (automatic segment space management) the clustering_factor of the index is 1,815 if the table is not partitioned – or has a single partition. The clustering_factor is 75,251 when you partition the table four ways.
Switch from a non-partitioned table to a hash-partitioned table with global indexes, and you may find some cases where the optimizer stops using an index because the clustering_factor makes the cost of using the index appear much too big.
Chapter 5 of my book mentioned a few ways in which structural strategies (multiple freelists, ASSM, reverse indexes, compacting tables) could affect the clustering_factor of an index – hash partitioning is another one. By hash-partitioning, you can reduce contention on hot blocks by having multiple data insertion points in a table – but creating multiple data insertion points will change the clustering_factor of some indexes for the worse.