15.读书笔记收获不止Oracle之 索引高度
来源:互联网 发布:淘宝换货售后卡怎么写 编辑:程序博客网 时间:2024/04/20 00:09
15.读书笔记收获不止Oracle之 索引高度
我们用实际例子来看下索引的高度是否真的比较低。
构造一系列表T1到T7,记录数从5到500万依次以10倍的差额逐步增大。
SQL>Create table t1 as select rownum as id,rownum+1 as id2from dual connect by level<=5;
Create table t2 as select rownum as id,rownum+1 as id2 from dualconnect by level<=50;
Create table t3 as select rownum as id,rownum+1 as id2 from dualconnect by level<=500;
Create table t4 as select rownum as id,rownum+1 as id2 from dualconnect by level<=5000;
Create table t5 as select rownum as id,rownum+1 as id2 from dualconnect by level<=50000;
Create table t6 as select rownum as id,rownum+1 as id2 from dualconnect by level<=500000;
创建索引:
Create index idx_id_t1 on t1(id);
Create index idx_id_t2 on t2(id);
Create index idx_id_t3 on t3(id);
Create index idx_id_t4 on t4(id);
Create index idx_id_t5 on t5(id);
Create index idx_id_t6 on t6(id);
查看索引大小:
col segment_name format a15;
select segment_name,bytes/1024 from user_segments where segment_namein ('IDX_ID_T1','IDX_ID_T2','IDX_ID_T3','IDX_ID_T4','IDX_ID_T5','IDX_ID_T6');
查看索引高度: BLEVEL=0表示1层
col index_name format a15;
selectindex_name,blevel,leaf_blocks,num_rows,distinct_keys,clustering_factor fromuser_ind_statistics where table_name in ('T1','T2','T3','T4','T5','T6');
INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS
--------------- ---------- --------------------- -------------
CLUSTERING_FACTOR
-----------------
IDX_ID_T6 2 1113 500000 500000
1035
IDX_ID_T5 1 110 50000 50000
101
IDX_ID_T4 1 11 5000 5000
9
INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS
--------------- ---------- --------------------- -------------
CLUSTERING_FACTOR
-----------------
IDX_ID_T3 1 2 500 500
1
IDX_ID_T2 0 1 50 50
1
IDX_ID_T1 0 1 5 5
1
记录相差巨大,但是高度差别却是如此之小。
1. 索引高度较低使用技巧
在这基础上进行测试如下:
Set autotrace traceonly
Set linesize 1000
Set timing on
Select * from t5 where id=10;
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 2977381114
-------------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED|T5 | 1 | 10 | 2 (0)| 00:00:01 |
|* 2| INDEX RANGE SCAN | IDX_ID_T5 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
2- access("ID"=10)
Statistics
----------------------------------------------------------
32 recursive calls
0 dbblock gets
52 consistent gets
4 physical reads
0 redosize
608 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rowsprocessed
在表六中查询:
Select * from t6 where id=10;
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 661597417
-------------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 10 | 4 (0)| 00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED|T6 | 1 | 10 | 4 (0)| 00:00:01 |
|* 2| INDEX RANGE SCAN | IDX_ID_T6 | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
2- access("ID"=10)
Statistics
----------------------------------------------------------
26 recursive calls
0 dbblock gets
52 consistent gets
5 physical reads
0 redosize
608 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rowsprocessed
发现t5和t6的表记录虽然相差了一个数量级,但是通过索引查询的效率却相差不多。主要是因为他们的BLEVEL 差不多。
2. 删除索引测试
drop index IDX_ID_T6;
Select * from t6 where id=10;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 1930642322
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 10 | 292 (2)| 00:00:01 |
|* 1| TABLE ACCESS FULL| T6 | 1 | 10 | 292 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter("ID"=10)
Statistics
----------------------------------------------------------
19 recursive calls
0 dbblock gets
1072 consistent gets
0 physical reads
0 redosize
604 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rowsprocessed
去掉索引后,逻辑读变的非常明显。产生了1072 次逻辑读。
如果索引的高度为3,查询到一条记录大致需要3到4次IO。如果返回100万条记录,就是100万乘以3或4,就是三四百万的IO数据,如不全表扫描。全表扫描还可以进行读取多个块。
- 15.读书笔记收获不止Oracle之 索引高度
- 14.读书笔记收获不止Oracle之 索引
- 12.读书笔记收获不止Oracle之 索引分区表
- 16.读书笔记收获不止Oracle之 分区索引
- 17.读书笔记收获不止Oracle之 索引存储列值
- 21.读书笔记收获不止Oracle之 索引回表效率
- 22.读书笔记收获不止Oracle之 索引特性活用
- 23.读书笔记收获不止Oracle之 组合索引
- 24.读书笔记收获不止Oracle之 索引的危害
- 25.读书笔记收获不止Oracle之 位图索引
- 26.读书笔记收获不止Oracle之 位图索引即席查询
- 27.读书笔记收获不止Oracle之 位图索引更新
- 28.读书笔记收获不止Oracle之 函数索引
- 【读书笔记】【收获,不止Oracle】索引组织表
- 1.读书笔记收获不止Oracle之开篇
- 2.读书笔记收获不止Oracle之 意识
- 13.读书笔记收获不止Oracle之 簇表
- 18.读书笔记收获不止Oracle之 索引SUM和AVG优化
- 关于反码,byte最大值127,最小值-128的由来缘由以及书本上从未讲解过的反码补码功能作用
- USB接口的功能和区分全解析
- 226. Invert Binary Tree Difficult: Easy
- pos1007
- 静态链接库(.lib)和动态链接库(.dll)的区别和使用
- 15.读书笔记收获不止Oracle之 索引高度
- 关系密切的设计模式(一)
- 16.读书笔记收获不止Oracle之 分区索引
- python学习笔记
- (转)金融从业人员的核心竞争力在哪里?
- 17.读书笔记收获不止Oracle之 索引存储列值
- 2017年10月30日 第二十一次总结
- Failed to contact master at [localhost:11311]
- Git小结