Re: Putting an index on column where table is partitioned

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 6 Feb 2002 07:35:14 -0000
Message-ID: <1012980887.16397.0.nnrp-14.9e984b29@news.demon.co.uk>

I suggest you read up on partitioning in the concepts manual and the administration guide. There are also a couple of short articles on my website.

The concept you are particularly looking for is the LOCAL index.

TOAD, by the way, is not giving you a complete execution plan for your queries - when using partitioned tables you also need to see (at least) three other columns from the plan_table, viz:

    partition_id - a reference linking other rows in the plan
    partition_start
    partition_stop

The start and stop tell you more about the actual partitions examined by Oracle.

For your particular query, you might like to try recreating the index with the key word LOCAL (see create index in the SQL reference). This will partition the index so that each partition of the table has a corresponding partition in the index.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

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

Author of:
Practical Oracle 8i: Building Efficient Databases


Jesus M. Salvo Jr. wrote in message ...


>Using 8.1.7.x ( but different patchlevels on some boxes )

>

>Below is what I got with explain plain ( using Toad ). The table was

>analyzed before EXPLAIN was issued and the created index was analyzed

>

>

>Without an index on the HOUR column:

>select * from aggregate

>where hour = varDate

>

>SELECT STATEMENT Optimizer=CHOOSE (Cost=71 Card=324 Bytes=14256)

>    PARTITION RANGE* (SINGLE)

>        TABLE ACCESS* (FULL) OF PLAYER_GAMETYPE_AGGREGATE (Cost=71 Card=324

>Bytes=14256)

>

>

>Without an index on the HOUR column:

>select * from aggregate

>where hour between fromDate and toDate

>

>SELECT STATEMENT Optimizer=CHOOSE (Cost=703 Card=1691 Bytes=74404)

>    PARTITION RANGE* (ITERATOR)

>        TABLE ACCESS* (FULL) OF PLAYER_GAMETYPE_AGGREGATE (Cost=703

>Card=1691 Bytes=74404)

>

>

>

>After creating an index on the HOUR column:

>select * from aggregate

>where hour = varDate

>

>SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=324 Bytes=14256)

>    TABLE ACCESS (BY GLOBAL INDEX ROWID) OF PLAYER_GAMETYPE_AGGREGATE

>(Cost=8 Card=324 Bytes=14256)

>        INDEX (RANGE SCAN) OF PLAYER_GAMETYPE_AGGREGATE_HOUR (NON-UNIQUE)

>(Cost=4 Card=324)

>

>

>As you can see from the above, after the index was created, it is now

>accessing the table via ROWID. Although it appears to be faster without an

>index .... whats the point of the partition then if I create index on the

>column where the table is parititioned?

>

>Is there anyway of creating an index for each table partition ( if there is

>such a thing ), so that you can tell Oracle via a hint to use PARTITION

>RANGE first ... and then use the index for that partition?

>

>

>John

Received on Wed Feb 06 2002 - 01:35:14 CST