通过索引来访问表的成本公式应该包含3个与块相关的组件：按降序遍历的分支层数、遍历 叶块的数目和访问过的表块的数目。
1、入门
SQL> create table t1
2 nologging
3 as
4 select
5 trunc(dbms_random.value(0,25))n1, --n1会产生25个不同的值。
6 rpad('x',40)ind_pad, --只有一个值。
7 trunc(dbms_random.value(0,20))n2, --n2会产生20个不同的值。
8 lpad(rownum,10,'0')small_vc,
9 rpad('x',200)padding
10 from
11 all_objects
12 where
rownum <= 10000
14 ;
表已创建。
SQL> create index t1_i1 on t1(n1, ind_pad, n2)
2 nologging
3 pctfree 91
4 ;
索引已创建。
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,
4 't1',
5 cascade => true,
6 estimate_percent => null,
7 method_opt => 'for all columns size 1'
8 );
9 end;
10 /
PL/SQL 过程已成功完成。SQL> select t.TABLE_NAME, t.NUM_ROWS, t.BLOCKS 2 from user_tables t 3 where table_name = 'T1'; TABLE_NAME NUM_ROWS BLOCKS ------------------------------ ---------- ---------- T1 10000 387 SQL> select s.table_name, 2 s.column_name, 3 s.num_distinct, 4 s.density, 5 s.num_nulls, 6 s.avg_col_len 7 from user_tab_col_statistics s 8 where table_name = 'T1'; TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS AVG_COL_LEN ------------------------------ ------------------------------ ------------ ---------- ---------- ----------- T1 N1 25 .04 0 3 T1 IND_PAD 1 1 0 41 T1 N2 20 .05 0 3 T1 SMALL_VC 10000 .0001 0 11 T1 PADDING 1 1 0 201 SQL> select i.index_name, 2 i.table_name, 3 i.blevel, 4 i.leaf_blocks, 5 i.distinct_keys, 6 i.clustering_factor, 7 i.num_rows 8 from user_indexes i 9 where index_name = 'T1_I1'; INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS ------------------------------ ------------------------------ ---------- ----------- ------------- ----------------- ---------- T1_I1 T1 2 1111 500 9752 10000
可以看见DISTINCT_KEYS等于索引列的NUM_DISTINCT相乘 = 25 * 1 * 20 = 500。一共有500种组合，没种组合对应的行数就是20行（10000 / 500 = 20）。
SQL> select small_vc
2 from t1
3 where n1 = 2
4 and ind_pad = rpad('x', 40)
5 and n2 = 3;
执行计划
----------------------------------------------------------
Plan hash value: 1429545322
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1160 | 25 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 20 | 1160 | 25 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 20 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=2 AND "IND_PAD"='x
' AND "N2"=3)
索引选择率：n1选择率*ind_pad选择率*n2选择率=1/25 * 1 * 1/20 = 1/500 = 1 / DISTINCT_KEYS
返回基数：1 / DISTINCT_KEYS * NUM_ROWS = 1/500 * 10000 = 20。
通过索引查询的成本为25，基数为20。基数估算非常准确，但成本又是如何得出的呐？CBO认为一共读取了20行数据，对访问表的成本为20不应该感到惊奇。索引的成本是5，可以将其中两个归咎于索引的blevel等于2。此处还剩余3个成本——可能是优化器已经估计到为了获取所必须的20个不同的行，必须遍历3个叶块。
effective index selectivity：将独立列的选择率相乘来计算联合选择率的方式是一种通用解决方案。但是此处有一个改进是我们评估索引时必须考虑的。假定有一个查询还包含了额外的谓词small_vc='000001'。如果我们选择通过先有的索引来访问表，那么一直到接触到了表之后，都不能用上最后这个谓词——因此，这个谓词将无法影响我们将要访问的数据的分数，只能影响最终返回的数据的分数。
effective table selectivity：当我们计算使用索引的成本时，有效表选择率应该仅仅是基于那些在接触到表之前就能在索引中进行评估的谓词。在这个示例中，针对表的所有谓词都能够在索引中找到，因此，可以直接认为有效表选择率也是0.04*1*0.05=0.002。
clustering_factor：是一个度量标准，用于索引的有序度和表的混乱度之间的比较。从表上面看，优化器安装索引中的顺序来遍历表，并追踪索引项有多少次是从一个表块跳转到另一个表块，从而计算clustering_factor。没跳转一次，计数器将会增加一次——计算器最终的值就是clustering_factor的值。清楚了clustering_factor的计算过程之后，我们就应该知道clustering_factor的最小值等于表中的块数目，最大值等于表中的行数——前提是我们已经得到了统计信息。
cost = blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)
SQL> select
2 2 +
3 ceil(1111 * (1/25 * 1 * 1/20)) +
4 ceil(9752 * (1/25 * 1 * 1/20))
5 from dual;
2+CEIL(1111*(1/25*1*1/20))+CEIL(9752*(1/25*1*1/20))
---------------------------------------------------
25
我们经常可以通过重建索引来减小索引中leaf_blocks参数的大小；但是，重建索引对于clustering_factor参数没有影响。
2、如何处理基于区间的测试（比如，n2 between 1 and 3）
SQL> select
2 /*+ index(t1) */
3 small_vc
4 from t1
5 where n1 = 2
6 and ind_pad = rpad('x', 40)
7 and n2 between 1 and 3;
执行计划
----------------------------------------------------------
Plan hash value: 1429545322
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 4756 | 93 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 82 | 4756 | 93 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 82 | | 12 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=2 AND "IND_PAD"='x
' AND "N2">=1 AND "N2"<=3)
n2选择率：(high_limit-low_limit)/(high_value-low_value)+2/num_distinct = ((3-1)/(19-0)+2/20)
effective index selectivity：(1/25 * 1 * ((3-1)/(19-0)+2/20))
effective table selectivity：和前面的示例一样，针对表的所有谓词都能够在索引中找到，因此，可以直接认为有效表选择率等同于有效索引选择率。
SQL> select round((1/25 * 1 * ((3-1)/(19-0)+2/20)) * 10000) from dual;
ROUND((1/25*1*((3-1)/(19-0)+2/20))*10000)
-----------------------------------------
82
cost = blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity);
SQL> select
2 2 +
3 ceil(1111 * (1/25 * 1 * ((3-1)/(19-0)+2/20))) +
4 ceil(9752 * (1/25 * 1 * ((3-1)/(19-0)+2/20)))
5 from dual;
2+CEIL(1111*(1/25*1*((3-1)/(19-0)+2/20)))+CEIL(9752*(1/25*1*((3-1)/(19-0)+2/20)))
---------------------------------------------------------------------------------
93
为了简单起见，上面的测试是针对索引的最后一列基于区间测试的。但是，如果对索引前面的列进行基于区间测试的话，又会出什么样的结果呢？
SQL> alter session set "_optimizer_skip_scan_enabled"=false;
会话已更改。
SQL> set autotrace trace exp;
SQL> select
2 /*+ index(t1) */
3 small_vc
4 from t1
5 where n1 between 1 and 3
6 and ind_pad = rpad('x', 40)
7 and n2 = 2;
执行计划
----------------------------------------------------------
Plan hash value: 1429545322
------------------------------------------------------------------------------------
万能数据库查询分析器使用技巧之（八）
马根峰
( 广东联合电子服务股份有限公司, 广州 510300)
作者博客：
CSDN博客：http://blog.csdn.net/magenfeng
新浪博客： http://blog.sina.com.cn/magenfeng
QQ空间： http://user.qzone.qq.com/630414817
0 引言
中国本土程序员马根峰推出的个人作品----万能数据库查询分析器，中文版本《DB 查询分析器》、英文版本《DB Query Analyzer》。
万能数据库查询分析器集哈希技术、链表等多种数据结构于一体，使用先进系统开发技术，经历4年的研究、开发、测试周期后在2006年面世。之后7年来一直在进行不断地完善、升级，到目前为止，最新版本为3.02 。万能数据库查询分析器具有长达7万多行代码的工作量，使得其具有强大的功能、友好的操作界面、良好的操作性、跨越各种数据库平台乃至于EXCEL。
在《程序员》2007第2期的“新产品&工具点评”部分，编辑“特别推荐”了“万能数据库查询分析器”发布。本期只点评了5个工具，分别是“Adobe Acrobat 8 中文版”、“迅雷搜索 1.7 新版上线”、“Google 桌面搜索 5.0 中文发布”、“BEA 发布 WebLogic SIP Server 3.0”和特别推荐“万能数据库查询分析器”发布。前面4个都是国内外大型软件公司的产品，只有“万能数据库查询分析器”是个人创作的软件。截止到目前，在国内最著名的软件下载网站“中关村在线”中下载量近9万次，位居整个数据库类排行谤中前20位。
截止到2011年11月9日，在Google搜索关键字“DB 查询分析器”、“DB Query Analyzer”，搜索结果分别在80万、150万左右；在Baidu搜索关键字“DB 查询分析器”、“DB Query Analyzer”，搜索结果均在40万左右。
下面前台以中文版本《DB 查询分析器》、后台以MS ACESS为数据库、操作系统为WIN7为例，简单介绍一下最新的3.02版本中新增的功能---“文本限定符”。允许用户自已来设定“字符”型字段的“文本限定符”，可以指定双引号、单引号将“字符”型字段的值标识，也可以什么都不用。这一功能，可以帮助用户来快速地生成查询SQL语句。
1 建立MS ACCESS数据库的ODBC数据源
运行《DB 查询分析器》，点击菜单项“工具--->ODBC数据源管理器”，来创建基于MS ACCESS的数据源“four_stations”，如下图所示。
图1 创建示例中的基于MS ACCESS 的ODBC数据源“four_stations”
接下来连接数据源，点击菜单项“文件--->连接”，在“连接数据库”窗口中，选择ODBC数据源“four_stations”，不用输入用户名和口令，即可连接数据库。
2 快速生成三种常见的SQL语句
在《DB 查询分析器》中，有三个常用的SQL语句生成菜单项，分别是“返回所有行”、“返回前100行”、“记录数”。本例中，以“返回前100行”为例，如图2所示，右击表test并选择“返回前100行”，并点击F5或者点击菜单项“查询--->运行”，结果如下。
图2 快速生成“返回前100行”SQL语句
在“万能数据库查询分析器” 中文版本《DB 查询分析器》、英文版本《DB Query Analyzer》中，默认的“文本限制符”是None，即对于文本字段，显示的时候，什么都不加。
3 快速生成查询特定行的SQL语句
如何获得以下流水号的记录？
25060B681B23F204
25060B681B253B01
25060B681B255102
5303355ED6120D00
5303355ED6122501
5303355ED6123802
5303355ED6124D03
5303355ED6126604
5303355ED6127A05
5305355ED618A200
5305355ED618BF01
图3 要查询的目标记录
在《DB 查询分析器》 和《DB Query Analy
create sequence seq_emp
increment by 1 --每次加几个
start with 1 --从1开始计数
maxvalue n --设置最大值
nomaxvalue --不设置最大值
nocycle --不循环累加
cycle --循环累加
cache 10 --缓存大小10
nocache --无缓存
创建好后
seq_emp.currval --sequence 当前值
seq_emp.nextval --sequence 增加值