Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Varna
    Posts
    20

    Question Unanswered: Optimal hash-partitions number for Global Partitioned Index

    Hi,
    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)

    Any opinion would be greatly appretiated !
    Prob Solver

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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

    Alan

  3. #3
    Join Date
    Dec 2003
    Location
    Varna
    Posts
    20

    Thumbs up

    Hi, Alan

    I was afraid so !

    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.

    All the best !
    Prob Solver

  4. #4
    Join Date
    Dec 2003
    Location
    Varna
    Posts
    20
    I've browsed AskTom's http://asktom.oracle.com/pls/ask/ nice comments but only thing i found was that number of hash partitions [Num] must be a power of 2 (2, 4, 8, 16, ...)
    Prob Solver

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •