Re: Indexing partition key

why not...?

It works well in my experience

2010/9/15 Pavel Ermakov <ocp.pauler@xxxxxxxxx>

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