Re: Indexing partition key

Alex

2010/9/20 Pavel Ermakov <ocp.pauler_at_gmail.com>

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