Re: Putting an index on column where table is partitioned

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

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:1012944552.3873.0.nnrp-14.9e984b29_at_news.demon.co.uk...
>
> 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 ).
> >
> >
> >
> >
> >
>
>
Received on Wed Feb 06 2002 - 00:48:55 CST