A question recently appeared on an article I wrote about partitioning a few months ago:
We are planning to create 8 HASH partitions. Looking only at PERFORMANCE would be there be any improvements, if we go for 16 or 32 partitions (maintainance and availability is not a problem in our case). There are only 2 indexes on our 350M table – one is LOCAL, another non-partitioned index.
The question raised is probably one of the more important considerations in hash partitioning and, as so often happens in Oracle, it’s a question about trade-offs. (It’s also a question I happen to have addressed in my first book).
On the plus side: if you increase the number of hash partitions from 8 to 16 then any query of the type “select where hash_key = constant” may cause a little less contention because there are now 16 root blocks on the critical index, and any one query will hit just one root block.
For highly concurrent inserts, the same is true for exactly the same reason – with the added benefit that if the column is a sequence-based column you have also eased contention on the right-hand edge of the index and on the table block – without resorting to fudges like multiple freelists or reverse key indexes.
On the minus size: if you have queries which perform range scans through the partitioned index then you have introduced an overhead of an extra eight partition range scans on top of the eight that you already have. Moreover, having doubled the number of partitions, you may also have to visit double the number of table blocks as well – although that isn’t necessarily the case if the hash-key column isn’t sequence based.
In the case of the global index – the increase in the number of partitions is only likely to be a performance hit if it means that data that was well clustered in the table is now scattered across twice as many blocks because of the doubling in the number of table partitions. I wouldn’t expect to see any increase in the resources usage due to the index itself.
Just think: what queries will I run; if I make this change how many extra index blocks will I have to visit for a given query; how many extra table blocks will I have to visit for a given query. If the numbers go up, that’s the threat; if the numbers go down, does this also mean reduced contention on some queries and DML.