Re: Index choice

From: Stefan Koehler <>
Date: Mon, 12 Jan 2015 22:08:37 +0100 (CET)
Message-ID: <>

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:

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

> Mohamed Houri <> 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?

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

Original text of this message