Re: partitions cardinality

From: Stefan Koehler <contact_at_soocs.de>
Date: Tue, 21 Apr 2015 16:56:58 +0200 (CEST)
Message-ID: <921098672.164863.1429628218554.JavaMail.open-xchange_at_app01.ox.hosteurope.de>

Hi Mauro,
thank you for the explanation and re-writing the filter predicate again.

For unknown reasons i setup my test case with "i_date >= to_date('20150402', 'yyyymmdd') and i_date =< to_date('20150515', 'yyyymmdd')", so my mentioned "between CBO calculation" was not accurate anyway.

>> The main reason for the drop I think is because part of the requested interval is out-of-range

Totally true. I did not notice this with the first argument - great to have an eagle eye like you :-)) .. also tested it with an in-range predicate and works out as expected in both cases. Thanks.

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK  

Best Regards
Stefan Koehler

> Mauro Pagano <mauro.pagano_at_gmail.com> hat am 21. April 2015 um 16:17 geschrieben:
>
> I think the partition adjustments isn't the main factor here, if you recreate the table as non partitioned you get the same estimation (more or
> less).
>
> The main reason for the drop I think is because part of the requested interval is out-of-range
> - Filter is i_date >= to_date('20150402', 'yyyymmdd') and i_date < to_date('20150515', 'yyyymmdd')
> - Depending when you create the TC you have a low value around 2015/04/18 or 2015/04/21 (not so great to work with a TC that changes overnight
> though :-()
>
> So the overlapping interval would roughly be 20150421->20150515 (upper bound excluded so it should be +1*1/NDV) that translates to a selectivity of
> aprox 0.405 that leads to around 81k
> There are probably some more small adjustments here and there to change the numbers a little more.
>
> Corrections are super-welcome, as usual :-)

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 21 2015 - 16:56:58 CEST