Re: Question About Indexes on a Partitioned Table

On Jun 19, 10:35 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> 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

Have you read Tom Kytes latest book "Expert Oracle Database Architecture"?

Chapter 13 is on partitioning and includes relevant considerations thru 10g. Received on Wed Jun 20 2007 - 06:44:58 CDT