Re: Indexing partition key

  • From: Pavel Ermakov <ocp.pauler@xxxxxxxxx>
  • To: amonte <ax.mount@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 14 Sep 2010 12:55:54 +0400

Hi

Could you please post how are you gather statistics?

Best regards, Pavel.

2010/9/14 amonte <ax.mount@xxxxxxxxx>

> yes the statistics are up to date
>
> the execution is very simple: parallel index scan (with artition pruning)
> and then table access
>
> Thanks
> 2010/9/13 Pavel Ermakov <ocp.pauler@xxxxxxxxx>
>
> Hi!
>>
>> Are you statistics up-to-date? How are you gather statistics?Could you
>> post explain plan?
>>
>> Best regards, Pavel.
>>
>> 2010/9/10 amonte <ax.mount@xxxxxxxxx>
>>
>> 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: