Primary key using a local partitioned index
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Primary key using a local partitioned index

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Posts
    534

    Primary key using a local partitioned index

    Hi,

    I'm working in a DWH project with Oracle 9.2

    The question is about the importance of the order of the keys of an local partitionned unique index used by the PK.

    I have a fact table which is range partitioned over the monthes.
    There are 3 dimensions attached to this fact table: time (month), customer and project. The primary key uses the dimension foreign keyes and is so based on the columns time, customer and project.

    For each monthly partition I have about 1'000'000 entries related to 1'000'000 different projects, 500'000 customers and 1 time value (1st day of the month).

    My question is: doest it have any bad impact for the optimizer if I create an index for the primary key with the partition range key at the last position of the index.

    So it would be an index like:
    create unique index pk_ft on fact_table(project_id, customer_id, time_id) local...

    instead of:
    create unique index pk_ft on fact_table(time_id, project_id, customer_id) local...


    Thanks for any feedback
    Mike

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    this is not gonna help you but I think in DWH for easier management you should create non-unique index then add the PK constraint to enforce uniqueness

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Pando :

    Hez conserned about order of the COLS he shud index.

    Well I think Mike, put the high Card Col in first place, it will suerly better response time.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Mar 2002
    Posts
    534
    Abhay,

    Your right my point was the order of the columns realted to their cardinality. I'm also convinced that if that unique index is used the response time should be better. What I'm worried about is that the index becomes an non-prefixed index because the range key is not the left most key of the index (select index_name, alignment from user_part_indexes).

    So what I'm realy wondering is the impact of having a non-prefixed index. Until now we only used prefixed PK indexes where the partition range key was the first key of the index.
    Last edited by mike9; 08-15-2003 at 05:44 AM.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    advantage of non-prefixed index:
    Useful when fast access needed when query column not based on table partition key
    Good for historical databases

    disadvantage:
    More expensive to probe than prefixed index
    Nonunique nonprefixed must scan all partitions to get the row

    Also do you know the way you create your PK, on the day you have to disable it for any reason you would lose the whole index and you have to build the index for your whole fact table...?

  6. #6
    Join Date
    Feb 2003
    Posts
    85

    Thumbs up

    Originally posted by pando
    advantage of non-prefixed index:
    Useful when fast access needed when query column not based on table partition key
    Good for historical databases

    disadvantage:
    More expensive to probe than prefixed index
    Nonunique nonprefixed must scan all partitions to get the row

    Also do you know the way you create your PK, on the day you have to disable it for any reason you would lose the whole index and you have to build the index for your whole fact table...?

    You are good, learning new things everyday

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    Originally posted by pando
    More expensive to probe than prefixed index
    Pando, What do you exactly mean by "probe".

    The index would be local so range partitionned the same way as the table and it would be unique. It would aslo containt the partition key but not in the first but last position of the index.


    Also would you rather recommand to create 2 indexes:
    a local prefixed unique (PK) index for ( time, project, customer)
    and a local non-prefixed index for ( customer)

    than only one index:
    a local non-prefixed unique (PK) Index on ( customer, project, time )
    Last edited by mike9; 08-15-2003 at 08:15 AM.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Any predicate placed on the range partitioning key is automaticaly going to allow partition elimination in the query, and this applies to the indexes as well as the table. So don't get hung up on the time column having to be the left-most column of a composite index.

    What you might like to do is adopt a common practice in DW fact tables of not having a real PK at all. You can declare a primary key (which aids the optimizer in making good decisions), but not enforce it, like ...
    Code:
    Alter Table
       my_fact
    Add Constraint
       xpkmy_fact
    Primary Key
       (
       time,
       customer,
       project
       )
    Disable Novalidate;
    
    Alter Table
       my_fact
    Modify Constraint
       xpkmy_fact
    Rely;
    Then, create local btree indexes on the customer and the project columns respectively.

    You might also place a bitmap index on the time column, which would aid some types of queries.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    A local prefixed index would give better performance than non-prefixed local index, if the 2 (or more) partitioned tables are joined on the partition key columns.

    You will see different execution plans when 2 tables are joined on the partition key columns.

    Tamil

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by mike9
    Pando, What do you exactly mean by "probe".
    I can read pando's mind right now... "I'll show you probing..."


    The index would be local so range partitionned the same way as the table and it would be unique. It would aslo containt the partition key but not in the first but last position of the index.


    Also would you rather recommand to create 2 indexes:
    a local prefixed unique (PK) index for ( time, project, customer)
    and a local non-prefixed index for ( customer)

    than only one index:
    a local non-prefixed unique (PK) Index on ( customer, project, time )
    This is also a trade-off between performance and administrative effort. The non-prefixed index would need to be rebuilt (in one form or another) almost every time you needed to do partition maintenance on the table.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

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



Click Here to Expand Forum to Full Width