Re: Indexing partition key

Was your system OLTP or DWH?

Alex

2010/9/10 Kenneth Naim <kennethnaim_at_gmail.com>

> I had the same issue with a similar sized table and index. I dropped it
> with good results.
>
>
>
> Ken
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *amonte
> *Sent:* Friday, September 10, 2010 12:53 PM
> *To:* Oracle-L Group
> *Subject:* Indexing partition key
>
>
>
> Hi all
>
> I have a data warehouse running in 11.1.0.7.
>
> There is a fact tables with 3000 million of rows range partitioned at
> entered_date per hour, entered_date is also indexed (btree index). This is
> giving some performance headaches because many queries are so simple as
>
> select .....
> from fact_table1
> where entered_date >= to_date(......)
> and entered_date > to_date(......)
>
> The range can go from 1 hour to 7 days. The problem is that instead of
> scanning partitions (full scans) it uses index range scan and to check 9
> hour data instead of taking around 20 seconds (using no_index hint) it takes
> 2 hours.
>
> This is not the first time I have seen this issue, I am almost certian that
> index on the partition key does not help in this case. Shall I drop the
> index?
>
>
> TIA
>
> Alex
>

--
http://www.freelists.org/webpage/oracle-l