Oralce index clustering factor

Oralce index clustering factor

SQL code -- test preparation FOR aggregation factor: -- CREATE two orders and unordered tables, create table T_COLOCATED (id number, COL2 VARCHAR2 (100); begin for I IN 1 .. 100000 loop insert into T_COLOCATED (ID, COL2) VALUES (I, RPAD (DBMS_RANDOM.RANDOM, 95, '*'); END LOOP; END; /alter table T_COLOCATED add constraint PK_T_COLOCATED primary key (ID); create table T_DISORGANIZED as select id, COL2 FROM T_COLOCATED order by COL2; alter table T_DISORGANIZED add constraint PK_T_DISORG primary key (ID ); -- analyze the layers of the two aggregate factors: SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS, NUM_ROWS, DISTINCT_KEYS, CLUSTERING_FACTOR FROM USER_IND_STATISTICS WHERE TABLE_NAME IN ('t_ COLOCATED ', 't_ DISORGANIZED '); INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR ---------- ---------------- --------------- limit 1 208 100000 100000 1469 PK_T_DISORG 1 208 100000 100000 -- first observe the query performance of the ordered table and compare the performance difference select /* + index (t) */* from t_colocated t where id> = 20000 and id <= 40000; Execution Plan -------------------------------------------------------- Plan hash value: 4204525375 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 21104 | 1339K | 389 (1) | 00:00:05 | 1 | table access by index rowid | T_COLOCATED | 21104 | 1339K | 389 (1) | 00:00:05 | * 2 | index range scan | PK_T_COLOCATED | 21104 | 53 (2) | 00:00:01 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 2-access ("ID"> = 20000 AND "ID" <= 40000) note ------dynamic sampling used for this statement statistical information defaults 0 recursive cballs 0 db block gets 2986 consistent gets 0 physical reads 0 redo size 2293678 bytes sent via SQL * Net to client 15048 bytes encoded ed via SQL * Net from client 1335 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20001 rows processed then observe the query performance of the unordered table select/* + index (t) */* from t_disorganized t where id> = 20000 and id <= 40000; used time: 00: 00: 09.75 execution Plan -------------------------------------------------------- Plan hash value: 4204525375 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 21104 | 1339K | 389 (1) | 00:00:05 | 1 | table access by index rowid | T_COLOCATED | 21104 | 1339K | 389 (1) | 00:00:05 | * 2 | index range scan | PK_T_COLOCATED | 21104 | 53 (2) | 00:00:01 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 2-access ("ID"> = 20000 AND "ID" <= 40000) note ------dynamic sampling used for this statement statistical information defaults 0 recursive cballs 0 db block gets 2986 consistent gets 0 physical reads 0 redo size 2293678 bytes sent via SQL * Net to client 15048 bytes encoded ed via SQL * Net from client 1335 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20001 rows processed -- Interpretation of clustering factors in Oracle

 

Indicates the amount of order of the rows in the table based on the values of the index. if the value is near the number of blocks, then the table is very well ordered. in this case, the index entries in a single leaf block tend to point to rows in the same data blocks. if the value is near the number of rows, then the table is very randomly ordered. in this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.