Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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 <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 23 Jun 2007 21:03:45 +0100
Message-ID: <fKidnYCPtJ084eDbRVnyjAA@bt.com>

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.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message 
news:Cv%di.20653$C96.20477_at_newssvr23.news.prodigy.net...

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US