Index Use
Set autotrace traceonly;
Select * From t1 Where object_id =2;        --走索引
/*
执行计划
----------------------------------------------------------
Plan hash value: 50753647


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     8 |   672 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     8 |   672 |     9   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1 |     8 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
*/
Select * From t1 Where object_id <= 1000;   --不走索引
/*
已用时间:  00: 00: 00.20
执行计划
----------------------------------------------------------
Plan hash value: 3617692013


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  7635 |   626K|   218   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T1   |  7635 |   626K|   218   (1)| 00:00:03 |
--------------------------------------------------------------------------


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


   1 - filter("OBJECT_ID"<=1000)




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1494  consistent gets
          0  physical reads
          0  redo size
     325345  bytes sent via SQL*Net to client
       5973  bytes received via SQL*Net from client
        510  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       7632  rows processed
*/
Select /*+ index(t1 idx_t1) */* From t1 Where object_id <= 1000;   --强制走索引
/*
执行计划
----------------------------------------------------------
Plan hash value: 50753647


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |  7635 |   626K|  7656   (1)| 00:01:32 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |  7635 |   626K|  7656   (1)| 00:01:32 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1 |  7635 |       |    18   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


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


   2 - access("OBJECT_ID"<=1000)




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       8157  consistent gets
          0  physical reads
          0  redo size
     725524  bytes sent via SQL*Net to client
       5973  bytes received via SQL*Net from client
        510  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       7632  rows processed
*/
比较发现强制使用索引用到的逻辑组更多


Select sql_text,cpu_time,elapsed_time,a.* From v$sql a Where sql_text Like '%* from t1 where object%';  --比较执行时间


Create Table t2 As Select * From t1 Order By object_id;  --用t1按object_id排序后的数据创建t2表
Create Index idx_t2 On t2(object_id);  --创建索引
exec dbms_stats.gather_table_stats('SYSTEM','T2'); --收集优化器统计信息
Select * From dba_tables Where table_name In('T1','T2')  
Select * From dba_indexes Where table_name In('T1','T2')  --查看index的优化器统计信息，相关列，BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,CLUSTERING_FACTOR聚集因子，越小聚集性越好,AVG_DATA_BLOCK_PER_KEY平均相同值所占的块数
Select * From t2 Where object_id <1000;
/*
已选择7632行。




执行计划
----------------------------------------------------------
Plan hash value: 2008370210


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        | 10272 |  1775K|   130   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     | 10272 |  1775K|   130   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2 | 10272 |       |    20   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


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


   2 - access("OBJECT_ID"<=1000)


Note
-----
   - dynamic sampling used for this statement




统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
       1208  consistent gets
         14  physical reads
          0  redo size
     725524  bytes sent via SQL*Net to client
       5973  bytes received via SQL*Net from client
        510  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       7632  rows processed
*/
Select * From t2 Where object_id+0 <100;
/*
已选择7632行。




执行计划
----------------------------------------------------------
Plan hash value: 1513984157


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10272 |  1775K|   219   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T2   | 10272 |  1775K|   219   (1)| 00:00:03 |
--------------------------------------------------------------------------


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


   1 - filter("OBJECT_ID"+0<=1000)


Note
-----
   - dynamic sampling used for this statement




统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       1576  consistent gets
          0  physical reads
          0  redo size
     138029  bytes sent via SQL*Net to client
       5973  bytes received via SQL*Net from client
        510  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       7632  rows processed
*/
排序后的的t2表，系统默认走索引，使用索引用到的逻辑组少


Select object_id,Rowid,dbms_rowid.rowid_block_number(Rowid) bno From t1 Where object_id <=100 Order By bno;  
Select count(Distinct bno) From (Select object_id,Rowid,dbms_rowid.rowid_block_number(Rowid) bno From t1 Where object_id <=100 Order By bno  );--t1表object_id<100的数据分布在16个块，块都是分散的
Select object_id,Rowid,dbms_rowid.rowid_block_number(Rowid) bno From t2 Where object_id <=100 Order By bno;
Select count(Distinct bno) From (Select object_id,Rowid,dbms_rowid.rowid_block_number(Rowid) bno From t2 Where object_id <=100 Order By bno  );--t2表object_id<100的数据分布在9个块,并且块都是连续的
说明t2表中的数据更加整齐
查询的效率高低，和表中的数据量有关系，还和表中数据的分布有关系
--select * from t1 where object_id +0 < 100;     不走索引
表达式不要写在有索引的列，表达式可以写在等号右侧


--带函数的索引
Create Index idx_t1_type On t1(object_type) Tablespace users;
Select * From t1 Where lower(object_type) = 'job'   --语句不走索引，如果使用频繁，可以在创建索引时把函数加上
Drop Index idx_t1_type
Create Index idx_t1_type On t1(lower(object_type)) Tablespace users;
select * from t1 where lower(object_type)='job';   --走索引
/*
已选择32行。




执行计划
----------------------------------------------------------
Plan hash value: 1222077033


-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |  2825 |   240K|   202   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |  2825 |   240K|   202   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1_TYPE |  2825 |       |     8   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


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


   2 - access(LOWER("OBJECT_TYPE")='job')




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
       3376  bytes sent via SQL*Net to client
        407  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         32  rows processed
*/
Select * From dba_ind_columns Where table_name In('T1','T2') --COLUMN_NAME是oracle给命的名SYS_NC00014$


--符合索引
Drop Index idx_t1
Select * From t1 Where object_id = 2 And owner = 'SYS'   --如果经常使用object_id和owner组合，可以创建组合索引
Create Index idx_t1 On t1(owner,object_id) Tablespace users;--owner在前
Select * From t1 Where object_id = 2 And owner = 'SYS'  --走索引
select * from t1 where owner='TSMSYS'  --自动走索引，逻辑组只用28   不走索引，逻辑组1020
Select * From t1 Where object_id = 2   --走索引跳跃扫描
/*执行计划
----------------------------------------------------------
Plan hash value: 2416351759


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     8 |   704 |    16   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     8 |   704 |    16   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IDX_T1 |     8 |       |     8   (0)| 00:00:01 |  --跳跃走索引
--------------------------------------------------------------------------------------


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


   2 - access("OBJECT_ID"=2)
       filter("OBJECT_ID"=2)




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
       1788  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
*/
Create Index idx_t1 On t1(object_id,owner) Tablespace users;--object_id在前
Select * From t1 Where object_id = 2 And owner = 'SYS'  --走索引
Select * From t1 Where object_id = 2 --走索引
select * from t1 where owner='TSMSYS'  --不走索引，全表扫描 逻辑组1070 ,oracle判断的问题，没有走索引跳跃扫描
select /*+ index(t1 idx_t1) */ * from t1 where owner='TSMSYS';  --强制走索引  全索引扫描 247逻辑组
/*执行计划
----------------------------------------------------------
Plan hash value: 3547076549


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    42 |  3696 |   265   (1)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |    42 |  3696 |   265   (1)| 00:00:04 |
|*  2 |   INDEX FULL SCAN           | IDX_T1 |    42 |       |   222   (1)| 00:00:03 |
--------------------------------------------------------------------------------------


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


   2 - access("OWNER"='TSMSYS')
       filter("OWNER"='TSMSYS')




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        247  consistent gets
        216  physical reads
          0  redo size
       3374  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         24  rows processed*/
         
select /*+ index_ss(t1 idx_t1) */ * from t1 where owner='TSMSYS';  --强制跳跃索引 247逻辑组
/*执行计划
----------------------------------------------------------
Plan hash value: 3547076549


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    42 |  3696 |   265   (1)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |    42 |  3696 |   265   (1)| 00:00:04 |
|*  2 |   INDEX FULL SCAN           | IDX_T1 |    42 |       |   222   (1)| 00:00:03 |
--------------------------------------------------------------------------------------


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


   2 - access("OWNER"='TSMSYS')
       filter("OWNER"='TSMSYS')




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        247  consistent gets
        216  physical reads
          0  redo size
       3374  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         24  rows processed*/
--所以owner和object_id顺序不同，产生的效果不同，oracle优化器可能出现错误，所以oracle提供hint提示功能
--创建组合索引时，将不同值少的列放在前面，即owner在前
Select owner,Count(*) From t1 Group By owner;