由索引引出简单实验几例
***********************************************声明************************************************
原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处（http://blog.csdn.net/huangyanlong）。
表述有错误之处，请您留言，不胜感激。
提醒：点击目录，更有助于您的查看。
*****************************************************************************************************
对之前的小例子重新归纳了一下，希望可以帮助对索引有进一步的理解。
【例1】数据量小不需建索引
//如果表的数据量很少，全表扫描和走索引成本相差很小，使用索引是不是就没有必要了。 实验操作： SQL> SELECT ENAME,JOB,SAL FROM SCOTT.EMP; //先找到一张小表以作实验，查看表中信息，只有14行 ENAME JOB SAL ---------- --------- ------ SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 JONES MANAGER 2975 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950 FORD ANALYST 3000 MILLER CLERK 1300 已选择14行。 SQL> SET AUTOTRACE ON SQL> SET AUTOTRACE TRACEONLY SQL> SELECT * FROM SCOTT.EMP WHERE ENAME='JAMES'; //全表扫描查找JAMES的信息 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- SQL> CREATE INDEX IND_EMP_ENAME ON SCOTT.EMP(ENAME); //为ENAME列建索引 SQL> SELECT * FROM SCOTT.EMP WHERE ENAME='JAMES'; //走列索引查找JAMES的信息 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_EMP_ENAME | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- //全表扫描成本是3%，走索引成本是2% //从以上实验发现，在表的数据量很小的情况下，全表扫描和走索引成本上相差不大。
【例2】全表扫描IO成本低于使用索引情况
**************************************************************************
举一个例子，不恰当的使用索引，比用全表扫描的的IO成本更加高。
**************************************************************************
解答：
思路：创建一组rowid是散落在多个表数据块中的索引，这样由于索引列数据的分布情况和索引中的顺序差异很大，致使通过全表扫表比走索引更能降低IO的使用成本。
操作如下：
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> /
//按照上面的方法以下连续创建，形成多个块，让每个块中都有相同的键值而形成一组实验用的ROWID
SQL> /
SQL> /
SQL> /
SQL> /
SQL> COMMIT;
SQL> CREATE INDEX IND_H1 ON TAB_HYL(OBJECT_ID);
//创建实验表中OBJECT_ID列的索引，之后通过该列值进行查询，来