Re: Index selectivity versus clustering factor

Informal explanation:

| 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

Assume you have enough "column = constant" predicates that Oracle could use either index completely, and whichever it uses it will visit some table blocks and then discard some of the rows thanks to predicates from "the other" index.

num_rows/distinct_keys tells you that the number of rows you get per key from N1 is (about) half the number of rows you get from N3. But clustering_factor tells you that the rows for N1 are scattered (roughly) twice as much as the rows for N3 - i.e. if you visited the same number of rows in each case you'd have to visit twice the number of table blocks for N1.

Half the rows, twice the scatter - the indexed are fairly finely balanced for the amount of work you do whichever you choose to use. So it's not surprising that you see a flip flop taking place.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "Hameed, Amir" <Amir.Hameed_at_xerox.com> To: <oracle-l_at_freelists.org> Sent: Tuesday, April 23, 2013 4:37 PM Subject: Index selectivity versus clustering factor

| 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:
|
| 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?
|

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