Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Putting an index on column where table is partitioned
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
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 ...Received on Wed Feb 06 2002 - 01:35:14 CST
>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