Re: Index choice

From: Stefan Koehler <contact_at_soocs.de>
Date: Mon, 12 Jan 2015 22:08:37 +0100 (CET)
Message-ID: <1058693113.6977.1421096917467.open-xchange_at_app04.ox.hosteurope.de>

Hi Mohamed,
if your issue is really caused by the calculated clustering factor. Have you already cross-checked, that the clustering factor calculation represents the "real world work"?

There is a back-ported enhancement request ("Bug 13262857 Enh: provide some control over DBMS_STATS index clustering factor computation INDEX") for this, which can help in such cases. Richard Foote blogged about this enhancement and how it works: https://richardfoote.wordpress.com/2013/05/08/important-clustering-factor-calculation-improvement-fix-you/

Maybe this is also a valid solution for you (dependent on the CF root cause of course).

Best Regards
Stefan Koehler

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

> Mohamed Houri <mohamed.houri_at_gmail.com> hat am 12. Januar 2015 um 20:33 geschrieben:

> In my opinion they remain two options to make the CBO choosing index_2 instead of index_1
>
>
> * set manually (using dbms_stat) the clustering factor of index_2 so that it will be less than the clustering factor of index_1
> * compress the index_2 so that the number of leaf block will be reduced and hence the cost will also be reduced
>
>
> What do you think?

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

Received on Mon Jan 12 2015 - 22:08:37 CET