Re: Indexing partition key
- From: amonte <ax.mount@xxxxxxxxx>
- To: Pavel Ermakov <ocp.pauler@xxxxxxxxx>
- Date: Tue, 14 Sep 2010 19:33:00 +0200
Hi
dbms_stats.gather_table_stats(owner, table_name, estimate_percent => 30,
cascade => TRUE, granularity = >ALL)
TIA
alex
2010/9/14 Pavel Ermakov <ocp.pauler@xxxxxxxxx>
> 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:
