Partitioned or non-partitioned indexes
- From: Kevin Hale Boyes <kcboyes@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 25 Apr 2011 13:28:34 -0600
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
