RE: Index selectivity versus clustering factor
Date: Tue, 23 Apr 2013 11:27:32 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8879D82B434_at_NADCWPMSGCMS10.hca.corpad.net>
Amir,
Also keep in mind how the optimizer calculate costs (where do cost figures come from?). DBMS_STATS.GATHER_SYSTEM_STATS.
Have you run DBMS_STATS.GATHER_SYSTEM_STATS to populate the IO and CPU information for the optimizer? And if so, did you run a NOWORKLOAD gather during an idle (or mostly idle) window as well. NOWORKLOAD populates a few of the fields that a normal interval gather does not populate.
Chris
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark
Sent: Tuesday, April 23, 2013 11:10 AM
To: Amir.Hameed_at_xerox.com; 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
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 23 2013 - 18:27:32 CEST