RE: Indexing partition key
- From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
- To: <ax.mount@xxxxxxxxx>, "'Oracle-L Group'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 10 Sep 2010 13:08:24 -0400
I had the same issue with a similar sized table and index. I dropped it with
good results.
Ken
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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
Other related posts:
