“索引”实验小例 - 数据库编程

说明查询的成本 SQL> ANALYZE TABLE TAB_HYL COMPUTE STATISTICS; //分析一下实验表 SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME ='TAB_HYL'; //查看一下此时实验表的行数、块数已经达到实验准备条件，可以开始试验了 NUM_ROWS BLOCKS ---------- ---------- 8960 103 SQL> SET AUTOTRACE ON SQL> SET AUTOTRACE TRACEONLY //设定跟踪 SQL> SELECT * FROM TAB_HYL WHERE OBJECT_ID=70; //通过上面创建了索引的列来查找，得到下面的分析结果，记住cpu的成本为30，并且数据库自动完成的是走全表扫描，说明数据库已经判断出什么方式查询，成本更低了。 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 128 | 10112 | 30 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TAB_HYL | 128 | 10112 | 30 (0)| 00:00:01 | ---------------------------------------------------------------------- //之后我们人为让查询走索引再看一下分析结果。 SQL> SELECT /*+INDEX(TAB_HYL IND_H1)*/ * FROM TAB_HYL WHERE OBJECT_ID=70; //强制查询走索引，输出一下结果，看到成本是102，要远高于全表扫描的成本（全表扫描是30，见上表）。 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 128 | 10112 | 102(0)| 00:00:02 | | 1 | TABLE ACCESS BY INDEX ROWID| TAB_HYL | 128 | 10112 | 102(0)| 00:00:02 | |* 2 | INDEX RANGE SCAN | IND_H1 | 128 | | 1(0)| 00:00:01 | ---------------------------------------------------------------------- 通过以上实验说明，当索引列数据的分布情况和索引中的顺序差异很大这种情况出现时，做索引范围扫描效率偏低。

【例3】构造表时集簇因子数分别为接近块数、接近行数

**************************************************************************
建两张表，各建一个索引。要求A表的索引集簇因子接近表块数，B表的索引集簇因子接近表行数。
**************************************************************************
（一）、创建A表：索引集簇因子接近表块数
操作：
SQL> CREATE TABLE TAB_HYL AS SELECT * FROM DBA_OBJECTS;
//先创建了一个TAB_HYL表以作实验源表，为了通过这个表分析出表中一个块所占的行数
SQL> ANALYZE TABLE TAB_HYL COMPUTE STATISTICS;
//分析这张TAB_HYL实验表
SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME ='TAB_HYL';
//查找出实验表上的行数、块数

  NUM_ROWS     BLOCKS
---------- ----------
     72606       1033

SQL> SELECT 72606/1033 FROM DUAL;
//计算平均每个块中的行数为70行

72606/1033
----------
 70.286544

SQL> DROP TABLE TAB_HYL PURGE;
//删除这张表
SQL> CREATE TABLE TAB_HYL AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM<=70;
//重新创建实验表让它装入70行形成第一个块
SQL> INSERT INTO TAB_HYL SELECT * FROM TAB_HYL;
//复制相同的70行插到实验表中,即实验表中共有140行数据，两个块
SQL> /    
//再次执行相同操作，但此时基准的实验表为140行，因此第三次插入了140行数据，即现在实验表有280行数据
SQL> /    
//按照上面的方法以下连续创建，这是为了构造实验表的集簇因子
SQL> /
SQL> /
SQL> /
SQL> /
SQL> COMMIT;
SQL> CREATE TABLE TAB_A AS SELECT * FROM TAB_HYL ORDER BY OBJECT_ID;
//根据实验表创建出表A，表A是通过OBJECT_ID排序的，因此就得到了键值相同的分布较集中的块
SQL> CREATE INDEX IND_H1 ON TAB_A(OBJECT_ID);
//创建A表中OBJECT_ID列的索引
SQL> ANALYZE TABLE TAB_A COMPUTE STATISTICS;
//分析一下A表
SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME = 'TAB_A';

  NUM_ROWS     BLOCKS
---------- ----------
      8960        102

SQL> SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,CLUSTERING_FACTOR
  2  FROM USER_INDEXES
  3  WHERE INDEX_NAME = 'IND_H1';
//查看A表索引列的b-tree级别、叶的块数、不同的key值、平均每个key所占的叶块的数量、聚集的因子

 BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR
------- ----------- ------------- ----------------------

“索引”实验小例(二)

【例3】构造表时集簇因子数分别为接近块数、接近行数