Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Question About Indexes on a Partitioned Table

Re: Question About Indexes on a Partitioned Table

From: Jonathan Lewis <>
Date: Sat, 23 Jun 2007 21:03:45 +0100
Message-ID: <>

A primary key can be enforced by a local index provided the key includes the partitioning column(s). The same is true of any unique index or index enforcing a unique constraint.

Once that requirement is out of the way, the choice of local vs. global for ANY indexes on a partitioned table should largely be dictated by one pair of conflicting issues:

Local indexes allow complete isolation of partitions, which means you can drop or exchange partitions virtually free of charge - but global indexes usually have to go through expensive maintenance.

Global indexes allow maximum efficiency of access along the index path - but local indexes introduce access overheads for all queries that do NOT include the partitioning key, as every partition of the index will have to be probed.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

"Dereck L. Dietz" <> wrote in message 

> Platform: Oracle, 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:
> 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 Sat Jun 23 2007 - 15:03:45 CDT

Original text of this message