RE: Index selectivity versus clustering factor

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Tue, 23 Apr 2013 16:41:53 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0AF62A6F_at_USA7109MB012.na.xerox.net>

Thanks Mark.

All of the columns in the statement are not part of any one index, only a few are and therefore, the statement does read data from the table after scanning the index and I do see "TABLE ACCESS BY INDEX ROWID" in the plan. Based on this information, CF should be influencing the plan and not the selectivity. But why would the statement alternate between these two indexes when the CF of one if is almost twice the other?

This statement uses binds and I am thinking that bind-peaking could be a possibility of the change in the execution plan.

Thanks,
Amir
-----Original Message-----

From: Bobak, Mark [mailto:Mark.Bobak_at_proquest.com] Sent: Tuesday, April 23, 2013 12:10 PM
To: Hameed, Amir; oracle-l_at_freelists.org Subject: RE: Index selectivity versus clustering factor

Hi Amir,

It depends on the execution plan.

Consider that if your index access is *not* followed by a "TABLE ACCESS BY ROWID", then clustering factor is irrelevant.

Also, the more selective an index is (the fewer rows will be identified through index access), the less important the clustering factor is.

The clustering factor is a relative measure of how much work Oracle will have to do when doing TABLE ACCESS BY ROWID lookups, based on the index range scan. The larger the range of rows (less selective) the index returns based on your predicates, the more important clustering factor will be in the cost calculations.

Hope that helps,

-Mark

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Hameed, Amir [Amir.Hameed_at_xerox.com] Sent: Tuesday, April 23, 2013 11:37
To: oracle-l_at_freelists.org
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:

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Apr 23 2013 - 18:41:53 CEST