Partitioned or non-partitioned indexes

From: Kevin Hale Boyes <kcboyes_at_gmail.com>
Date: Mon, 25 Apr 2011 13:28:34 -0600
Message-ID: <BANLkTi=RD20bP+MVs9mcC3_VTVgOOCFzwA_at_mail.gmail.com>



I'm designing a new set of master-detail tables.

The master has about 300,000 rows and the detail table has about 7 rows for each master row.
There are thousands of insertions and deletions per day and the master slowly accumulates rows over time (I hope). There are actually several detail tables but didn't think it was important for my question.

The tables are HASH-partitioned by a numeric foreign-key column
(bto_id) that has about 50 distinct values.
Most of the access to these tables includes this partitioning key. This column is different than the primary key column.

The table definition looks like this:

    CREATE TABLE WM (

        id     NUMBER NOT NULL,
        bto_id NUMBER NOT NULL,
        some other columns
        CONSTRAINT R_WM1 FOREIGN KEY (bto_id) REFERENCES OTHER_TAB (oth_id),
    )
     PARTITION BY HASH (bto_id);

    CREATE TABLE WD (
        id     NUMBER NOT NULL,
        wmid   NUMBER NOT NULL,
        bto_id NUMBER NOT NULL,
        some other columns
        CONSTRAINT R_WD1 FOREIGN KEY (wmid) REFERENCES WM (id),
        CONSTRAINT R_WD2 FOREIGN KEY (bto_id) REFERENCES OTHER_TAB (oth_id),
    )
     PARTITION BY HASH (bto_id);



I'd like some help designing two indexes for these tables - the primary key indexes and the foreign-key index from WD to WM.

The partitioning guide from Oracle talks about global hash partitioned indexes and how they improve performance for monotonically growing indexes.
This description fits my tables which have the primary keys coming from sequences.
It also fits my usage where I'm more concerned about performance versus manageability.

So, this leads to something like the following for the primary key:

    ALTER TABLE WM ADD CONSTRAINT XGPKWM PRIMARY KEY (id)

       USING INDEX (CREATE UNIQUE INDEX XGPKWM ON WM (id)
       GLOBAL PARTITION BY HASH (id)
         (PARTITION XGPKWM_P1, PARTITION XGPKWM_P2, PARTITION
XGPKWM_P3, PARTITION XGPKWM_P4,
          PARTITION XGPKWM_P5, PARTITION XGPKWM_P6, PARTITION
XGPKWM_P7, PARTITION XGPKWM_P8)
       TABLESPACE INDX);


I guess the foreign key indexes would look similar.

I don't know why this would be better than a simple non-partitioned PK or FK index.
(i.e., ALTER TABLE WM ADD CONSTRAINT XGPKWM PRIMARY KEY (id))

I don't have a specific question but I am looking for feedback on this index design and pointers to things that I may not have considered.

Thanks,
Kevin.

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Apr 25 2011 - 14:28:34 CDT

Original text of this message