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

Thread: Primary key using a local partitioned index

  1. #11
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    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
    That's true, but direct joins between partitioned tables in a DW are pretty rare -- usually you see such things in OLTP systems.

    For the sake of flexibility I'd go for non-prefixed indexes, especially if they are bitmap-type.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  2. #12
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by marist89
    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.
    I don't think that there is any particular difference between prefixed and non-prefixed local indexes, with regard to what kind of partition operations require a partition rebuild, is there?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #13
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327

    Re: Primary key using a local partitioned index

    Originally posted by mike9
    Hi,

    I have a fact table which is range partitioned over the monthes.

    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).
    You may create an index with compress 1 in order of (time, project, customer) since you have a single time value in each partition. That provide good performance for project-based search and also enable index probing.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

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