RE: Partitioned Tables and Local vs. Global Indexes.

Hello Jay,

Of the 4 index partitioning methods you mentioned, you can eliminate "c". Local indexes by definition are equipartitioned with the underlying table so there can be no such thing as a local non-partitioned index on a partitioned table.

For the other three. "a" Local Partitioned Index is preferred whenever the index can include the table's partition key. Just about every maintenance operation is easier.

In the event that an index must be created on a partition table where the partition key is not a column in the index, you will need to create a global index. This is often the case where a sequence is used to create a table's primary key while the partition key is unrelated (i.e. date ranges).

My preference on global indexes is to always use "b" a Global Partitioned Index. The tables where I've used partitioning have tended to grow rapidly. If the table is big enough to require partitioning, chances are that an index on that table will also require partitioning. That's my thinking.

Prior to my arrival at my current site, the person who is now my team lead created several partition tables (no, he's not really a DBA but he does know enough to be dangerous 8-). One of the tables he created required a global index. Since he was automating partition maintenance and did not want to complicate his life with different partition schemes between the table and the primary key index, he decided to use option "d" a Global Non-Partitioned Index. I've discussed changing this arrangement since this PK index is larger than I like, but he outranks me and continues to want to avoid the added maintenance complexity.

So, that pretty much explains my philosophy on selecting each method of index partitioning. I'm sure there are other opinions. I'm even more sure that others have used some of the more complex and/or esoteric partitioning methods that I haven't found a need for yet. Should be interesting to see what they might have to say.

Kevin Kennedy
First Point Energy Corporation

-----Original Message-----
Sent: Tuesday, June 11, 2002 9:19 AM
To: Multiple recipients of list ORACLE-L

> Hi,
>
>
> I have inherited a database that contains about 20 partitioned tables,
> many or them in the 10 to 30 million row range. All of the tables are
> partitioned by range. I am using Oracle 8.1.6.2 on Solaris.
>
>
> I have found a mixture of indexes and I an not sure of the justification
> of each.
>
>
> Why would one choose to use
>
> a. Local Partitioned Index
> b. Global Partitioned Index
> c. Local Non Partitioned Index
> d. Global Non Partitioned Index
>
>
>
>
>
>
> Thanks
>
>
>
>
>
> Jay Earle, BSc(CS) (jay_earle_at_smartforce.com)
> DBA, Operations Group
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jay Earle (DBA)
  INET: Jay_Earle_at_smartforce.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: kkennedy
  INET: kkennedy_at_firstpoint.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).