Re: Indexing partition key

Ok. Are you try to use estimate_percent null or 10 or 100 or auto_sample_size?

Best regards, Pavel.

2010/9/17 amonte <ax.mount_at_gmail.com>

> why not...?
>
> It works well in my experience
>
> 2010/9/15 Pavel Ermakov <ocp.pauler_at_gmail.com>
>
> Hi
>>
>> Why estimate_percent => 30?
>>
>>
>> Best regards, Pavel.
>>
>>
>> 2010/9/14 amonte <ax.mount_at_gmail.com>
>>
>>> 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_at_gmail.com>
>>>
>>>> Hi
>>>>
>>>> Could you please post how are you gather statistics?
>>>>
>>>> Best regards, Pavel.
>>>>
>>>> 2010/9/14 amonte <ax.mount_at_gmail.com>
>>>>
>>>> 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_at_gmail.com>
>>>>>
>>>>> 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_at_gmail.com>
>>>>>>
>>>>>> 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