了解index的compress
对于创建index时，是否选择compress功能概念一直比较模糊，什么情况下适用compress，什么情况下应该避免使用，都不太清楚，于是，做了如下的实验。
搭建测试环境
|
SYS @ prod >create table t as select * from dba_objects;
Table created.
SYS @ prod >insert into t select * from t;
50390 rows created.
SYS @ prod >insert into t select * from t;
100780 rows created.
SYS @ prod >insert into t select * from t;
201560 rows created.
SYS @ prod >insert into t select * from t;
403120 rows created.
SYS @ prod >insert into t select * from t;
806240 rows created.
SYS @ prod >insert into t select * from t;
1612480 rows created. |
对测试表全表扫描，查看 统计数据
|
SYS @ prod >select count(*) from t;
COUNT(*) ---------- 3224960
SYS @ prod >select t.num_distinct from dba_tab_col_statistics t where t.table_name='T' and t.column_name='OBJECT_TYPE';
NUM_DISTINCT ------------ 41 SYS @ prod >set autotrace on; SYS @ prod >select count(*) from t where object_type='INDEX';
COUNT(*) ---------- 114112
Execution Plan ---------------------------------------------------------- Plan hash value: 2966233522
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 9831 (2)| 00:01:58 | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| T | 78658 | 614K| 9831 (2)| 00:01:58 | ---------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter("OBJECT_TYPE"='INDEX')
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 44115 consistent gets 27009 physical reads 0 redo size 413 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
当前由于没有索引，CBO使用Table Full Scan来访问数据，consistent gets非常大。 |
建立普通索引：
|
SYS @ prod > create index T_IDX on t(object_type);
Index created. |
查看该索引的分布情况
|
SYS @ prod >select t.header_file,t.header_block from dba_segments t where t.segment_name='T_IDX';
HEADER_FILE HEADER_BLOCK ----------- ------------ 1 94689
索引在 数据文件 1 的 第 94689个 数据块 |
看看branch的情况
|
SYS @ prod >alter system dump datafile 1 block 94690;
System altered. |
查看BRANCH 的trace文件
|
Branch block dump ================= header address 3084659780=0xb7dc2c44 kdxcolev 2 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 24 kdxcofbo 76=0x4c kdxcofeo 7562=0x1d8a kdxcoavs 7486 kdxbrlmc 4334349=0x42230d kdxbrsno 0 kdxbrbksz 8056 kdxbr2urrc 5 row#0[8029] dba: 4334683=0x42245b col 0; len 15; (15): 49 4e 44 45 58 20 50 41 52 54 49 54 49 4f 4e col 1; len 6; (6): 00 41 fd 7b 00 3e row#1[8007] dba: 4335020=0x4225ac col 0; len 10; (10): 4a 41 56 41 20 43 4c 41 53 53 col 1; len 6; (6): 00 41 82 b8 00 45 |
Leaf情况
|
SYS @ prod >alter system dump datafile 1 block 94691;
System altered. |
查看LEAF 的trace文件
|
Leaf block dump =============== header address 3084659804=0xb7dc2c5c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 377 kdxcofbo 790=0x316 kdxcofeo 1623=0x657 kdxcoavs 833 kdxlespl 0 kdxlende 0 kdxlenxt 4288996=0x4171e4 kdxleprv 0=0x0 kdxledsz 0 kdxlebksz 8032 row#0[8015] flag: ------, lock: 0, len=17 col 0; len 7; (7): 43 4c 55 53 54 45 52 col 1; len 6; (6): 00 41 6d e2 00 04 row#1[7998] flag: ------, lock: 0, len=17 col 0; len 7; (7): 43 4c 55 53 54 45 52 col 1; len 6; (6): 00 41 6d e2 00 14
一切都比较正常，Leaf存储了键值及链接，看看这时的查询情况： |
|
SYS @ prod >select t.blevel,t.leaf_blocks,t.distinct_keys,t.avg_leaf_blocks_per_key,t.avg_data_blocks_per_key,t.clustering_factor from user_indexes t where t.index_name='T_IDX';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR ---------- ----------- ------------- ----------------------- ----------------------- ----------------- 2 8882 41 216 3376 138427
SYS @ prod >select count(*) from t where object_type='INDEX';
COUNT(*) ---------- 114112
Execution Plan ---------------------------------------------------------- Plan hash value: 293504097
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 221 (1)| 00:00:03 | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | INDEX RANGE SCAN| T_IDX | 78658 | 614K| 221 (1)| 00:00:03 | ---------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("OBJECT_TYPE"='INDEX')
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 274 consistent gets 0 physical reads 0 redo size 413 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
索引可以利用上了，consistent gets由全表扫描的44115降低至274，降为0.0169。对于一般的优化而言，效果已经很明显了。
但是，有没有更好的降低consistent gets的方法呢？ |
