SQL语句（8）--- 索引

1. 相关命令

非唯一索引：SQL> create index  INDEXNAME on TABLE (COLUMN); 

唯一索引：SQL> create UNIQUE index  INDEXNAME on TABLE (COLUMN); 

复合索引：SQL> create index  INDEXNAME on TABLE (COLUMN1,COLUMN2);

删除索引：SQL> drop index INDEXNAME;

查询索引：SQL> select * from user_indexes;   

   　　　　SQL> select * from user_ind_columns;

2. 优化器相关命令

SQL> set autotrace on/off:

SQL> set autotrace traceonly explain;------只查看扫描结果

SQL> set autotrace traceonly statistic;------只查看分析结果

SQL> analyze table TABLENAME compute statistics;------在添加大量数据后，使用该命令可以对表数据进行分析 

3. 索引扫描方式

索引唯一扫描：针对某列创建了唯一索引，查询该列信息时，走索引唯一扫描，如果单列索引使用了RANGE操作符，则走全表扫描或者范围扫描

索引范围扫描：唯一索引使用了RANGE操作符，复合唯一索引，使用了前导列

索引全扫描：

索引快速扫描：主要通过参数 db_file_multiblock决定，SQL> show parameter db_file_multiblock;

手工干预强制走索引全扫描：SQL> select  /*+ index(TABLE)*/ COLUMN from TABLE;

手工干预强子和走全表扫描：SQL> select /*+ full(TABLE)*/ COLUMN from TABLE;

SQL> conn scott/tiger@erp
Connected.
SQL> set autotrace on;
SQL> select ename,empno from temp where deptno=10;

ENAME              EMPNO
-------------------- ----------
CLARK               7782
KING               7839
MILLER               7934


Execution Plan
----------------------------------------------------------
Plan hash value: 1896031711

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     5 |    65 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEMP |     5 |    65 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=10)


Statistics
----------------------------------------------------------
     63  recursive calls
     19  db block gets
     90  consistent gets
      3  physical reads
       3036  redo size
    689  bytes sent via SQL*Net to client
    607  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      7  sorts (memory)
      0  sorts (disk)
      3  rows processed

SQL> set autotrace traceonly explain
SQL> select ename,empno from temp where deptno=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1896031711

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     5 |    65 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEMP |     5 |    65 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=10)

SQL> set autotrace traceonly statistic
SQL> select ename,empno from temp where deptno=10;


Statistics
----------------------------------------------------------
      0  recursive calls
      2  db block gets
      8  consistent gets
      0  physical reads
      0  redo size
    689  bytes sent via SQL*Net to client
    607  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      3  rows processed

SQL>