RE: Hint cardinality for predicate
- From: "Flora Deng" <hdeng@xxxxxxxxxxxxxxxx>
- To: <iliakan@xxxxxxxxx>, "'Marcin Przepiorowski'" <pioro1@xxxxxxxxx>
- Date: Wed, 12 May 2010 15:17:28 -0500
Hi,
Did you try the multicolumn statistics since it's 11G new feature?
e.g.
select
dbms_stats.create_extended_stats('schema_name','table_name','(a,b,c)') from
dual;
exec dbms_stats.gather_table_stats('schema_name','table_name', -
method_opt=>'for all column size 1 for columns (a,b,c) size 3');
Flora
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Ilia Kantor
Sent: Wednesday, May 12, 2010 3:08 PM
To: Marcin Przepiorowski
Cc: Oracle-L@xxxxxxxxxxxxx
Subject: Re: Hint cardinality for predicate
Hello,
Database version is 11g2.
I tried histograms. But the problem here again is that columns are
kind-of intercorrelated.
For example, I have 1 column named "country", then other columns have
different stats for each country.
So in fact I need count(distinct country) statistic sets for proper
estimation.
I tried partitioning by country, but it has drawbacks in my case which
slow down at other places.
Best regards
Ilya
2010/5/12 Marcin Przepiorowski <pioro1@xxxxxxxxx>:
> On Wed, May 12, 2010 at 8:53 PM, Ilia Kantor <iliakan@xxxxxxxxx> wrote:
>> Hello,
>>
>> I have a multi-predicate query like:
>>
>> SELECT * FROM table WHERE
>> a BETWEEN 5 and 10
>> and
>> b in (3,4,5)
>> and c in (9)
>> ...
>>
>> Unfortunately, optimizer is not very good at estimating cardinalities.
>> Columns are somewhat intercorrelated and "multiple column stats"
>> feature does not fix that.
>>
>> Although, I gather my own stats which give me exact idea for
>> selectivity of each predicate.
>> How can I pass this information to optimizer?
>>
>
> Hi,
>
> What database version ?
> Did you try change gathering statistic from estimating to calculating
> and use histograms ?
>
>
> --
> Marcin Przepiorowski
> http://oracleprof.blogspot.com
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
