Index selectivity versus clustering factor

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Tue, 23 Apr 2013 15:37:07 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0AF6298E_at_USA7109MB012.na.xerox.net>

The MTL_MATERIAL_TRANSACTION table in the Oracle ERP database has multiple indexes on it. Statistics are gathered on this table once a week. We have noticed that SQL plan for a particular SQL statement is alternating between two indexes, MTL_MATERIAL_TRANSACTIONS_N1 & MTL_MATERIAL_TRANSACTIONS_N3. The Selectivity and CF of these two indexes is shown below:

INDEX_NAME                         NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR


------------------------------ ------------ ------------- -----------------

MTL_MATERIAL_TRANSACTIONS_N1    133,880,400    68,318,310        81,749,000
MTL_MATERIAL_TRANSACTIONS_N3    138,635,500    36,643,500        43,579,800

It seems that index MTL_MATERIAL_TRANSACTIONS_N1 is more selective than MTL_MATERIAL_TRANSACTIONS_N3 but MTL_MATERIAL_TRANSACTIONS_N3 has a better CF. In this scenario, when preparing an execution plan, which statistic takes precedence over the other?

Thank you,
Amir

--

http://www.freelists.org/webpage/oracle-l Received on Tue Apr 23 2013 - 17:37:07 CEST