Question About Indexes on a Partitioned Table

Platform: Oracle 10.2.0.3.0, Windows 2003 Server, 8GB RAM

I'm working on trying to partition some tables and in the Concepts, Datawarehouse and Performance Tuning manuals mention is made of the different types of indexes (Local and Global).

One table I'm working on is:

HMP_DATES

    person_id                        NUMBER(10)        NOT NULL,
    service_type_code            VARCHAR2(3)    NOT NULL,
    srv_dt_from                    DATE                    NOT NULL,
    data_source                    CHAR(1)                NOT NULL,
    mbr_key                          VARCHAR2(50),
    mbr_sys_key                    VARCHAR2(50),
    sys_date                            DATE

I'm placing a primary key on the person_id, service_type_code, srv_dt_from and data_source columns since those fields uniquely identify a row.

Since we have to keep data for 10 years I'm planning on initially creating 12 partitions (we have some data currently in the database past 10 years and some in the future) using the SRV_DT_FROM as the partition key.

I understand the concept of local indexes and even global but I'm confused a bit concerning global indexes.

From reading the manuals it would appear that a primary key should be a global index. What I haven't been able to figure out is whether a global index is created in one segment or if it's partitioned too. I know even if it's partitioned the same as the underlying table you don't get the benefits as a true local partitioned index.

My question is whether I should 1) use global indexes for primary/unique keys and 2) if I do should I partition it also or leave it as one segment.

Thanks Received on Tue Jun 19 2007 - 21:35:40 CDT