Unanswered: Optimal hash-partitions number for Global Partitioned Index
I am testing performance optimisations by using "global partitioned indexes" on "Oracle 10g Release 2"
I experiment with table that implements Many-To-Many join of the type
CREATE TABLE "T1" ("ID1" integer, "ID2" integer)
Table has 2,5M rows and every of the ID columns has about 100K unique values.
I've created two global partitioned indexes:
CREATE UNIQUE INDEX "X_T1_1" on "T1" ("ID1", "ID2")
GLOBAL PARTITION BY HASH ("ID1") PARTITIONS [Num];
CREATE UNIQUE INDEX "X_T1_2" on "T1" ("ID2", "ID1")
GLOBAL PARTITION BY HASH ("ID2") PARTITIONS [Num];
I ran my test package for: [Num] in 8, 16, 32, 64, 128, 256
The best result were for [Num] = 64 !
My question is:
Is there any method for calculation of the optimal [Num] in the general case ?
I suspect that [Num] could depend on:
1) Number of unique values to of the hash column set
2) The size of the index rows (bytes)
3) The size of a single Extent (bytes)
4) The mode of extent management (local/automatic)
As with most things to do with the optimizer there are no calculations available as the optmizer takes so many factors into account (and the number of factors seems to increase with each Oracle version). The best thing to do is to benchmark what works best for your production environment. And of course retest when you change Oracle versions
I hoped that there is a way to find where the benefit from spared B-tree search steps will meet the overhead of hash partitions management but as they say: "no pain no gain"
But you are most probably right because my tests show quite a big deviation. Several runs of one variant after restart of the Oracle service, give different control times.
This only comes to show, that for sure I'm not aware of all factors that reflect partitioned index usage.
Anyway - thanks a lot for the comment. Now I know that there isn't any simple decision that I've missed.