RE: Index selectivity versus clustering factor
- From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
- To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 23 Apr 2013 16:41:53 +0000
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@xxxxxxxxxxxx]
Sent: Tuesday, April 23, 2013 12:10 PM
To: Hameed, Amir; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Hameed, Amir [Amir.Hameed@xxxxxxxxx]
Sent: Tuesday, April 23, 2013 11:37
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
