Hi Experts,
I have a table "X" and below are its index details. Just want to know why is the clustering factor so high for indexes - IDX1 and IDX2 ?
Is there any way CF can be brought down by say ordering data in the indexes ?
Table has 188K records.
INDEX NAME | DISTINCT KEYS | LEAF BLOCKS | CLUSTERING FACTOR | NUM ROWS | INDEX COLUMNS |
|---|
| T_PK | 188111 | 357 | 3887 | 188111 | ORD_ID |
| T_IDX2 | 188341 | 699 | 187887 | 188341 | EXT_ID, ORD_ID |
| T_IDX1 | 187504 | 762 | 187661 | 188111 | EXT_ID |
Possibly, because of this one of my query is not opting for a index scan, rather FTS.
MERGE INTO gtt_ord t1
USING X t2 ON (t1.global_ext_id = t2.ext_id)
WHEN MATCHED THEN
UPDATE SET t1.ord_id = t2.ord_id;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | 832 (100)| |
| 1 | MERGE | GTT_ORD | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN | | 1156 | 706K| 832 (2)| 00:00:01 |
| 4 | TABLE ACCESS FULL| GTT_ORD | 1152 | 589K| 36 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| X | 188K| 18M| 794 (2)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("t1"."GLOBAL_EXT_ID"="t2"."EXT_ID")
Thanks and Regards,
-Ranit
( on Oracle 11.2.0.4.0 )