Re: Indexing partition key

Hi

Why estimate_percent => 30?

Best regards, Pavel.


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

> 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: