Re: Putting an index on column where table is partitioned

You don't state the version, and partition elimination effects are highly variable between versions.

Reviewing your sample query, though, using 8.1.7 in principle, (a) you do not need the index for Oracle to do partition elimination, and (b) having the index, as a local index, should not cause Oracle to access every partition.

Have you used a full and complete EXPLAIN PLAN to produce the execution path (including the partition start and stop) or have you used autotrace ?

--
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
<28K78.36840$Ni2.210592_at_news-server.bigpond.net.au>...


>

>I have a partitioned table that is partitioned on a DATE column, one

>partition for each year.

>

>If I dont put an index on that DATE column, and I have a query like "WHERE

>datecolumn = dateVar", Oracle will automatically know which partition to

>select from.

>

>If I put an index on that DATE column, wouldn't it make the query slower?

>.... since it would then have to access the parititioned table by its ROWID

>as suggested by the ... and therefore would make Oracle scan all paritions.

>

>Is this right? At least this is what I can see from EXPLAIN PLAN ( unless I

>have misinterpreted it ).

>

>

>

>

>