SQL优化之不走索引_加hint也不走索引

案例1：隐式类型转换

SQL> create table t(id varchar2(10));
表已创建。

SQL> insert into t select empno from scott.emp;
已创建14行。

SQL> commit;
提交完成。

SQL> select * from t;

ID
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

已选择14行。

SQL> desc t;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 VARCHAR2(10)

SQL> create index t_idx on t(id);
索引已创建。

SQL> analyze table t compute statistics;
表已分析。

SQL> set autot trace
SQL> set timing on

SQL> select * from t where id=7369;

已用时间:  00: 00: 00.00

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

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

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

   1 - filter(TO_NUMBER("ID")=7369)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        408  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

数据类型隐式转换，导致未走索引

SQL> select /*+ index(t) */ * from t where id=7369;

已用时间:  00: 00: 00.00

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

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

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

   1 - filter(TO_NUMBER("ID")=7369)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        408  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

即使加了hint，依然无效

SQL> select * from t where id='7369';

已用时间:  00: 00: 00.00

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

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_IDX |     1 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - access("ID"='7369')

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        408  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

解决方案：1、避免隐式转换 2、创建函数索引

案例2：索引失效

SQL> select a.index_name,a.table_owner,a.table_name from user_indexes a where status = 'UNUSABLE';

SQL> alter index index_name rebuild online compute statistics;

索引已更改。

案例3：索引的访问路径oracle并没有考虑

主要讨论index fast full scan

index fast full scan类似全表扫描，只是把索引当作表来处理，支持并行和多块读
index fast full scan的前提是，索引必须满足这个查询，同时必须保证非空
(通过非空字段或者是在where条件中声明非空),最为典型的就是count(*)

SQL> create table test as select * from dba_objects;
表已创建。

SQL> create index i_test_1 on test(object_id);
索引已创建。

SQL> analyze table test compute statistics;
表已分析。

SQL> set autot trace
SQL> select count(*) from test;

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

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   206   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 52933 |   206   (1)| 00:00:03 |
-------------------------------------------------------------------

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        730  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

这里虽有索引，但oracle并不知道索引中是否有null值，所以无法走索引

SQL> select count(*) from test where object_id is not null;

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

----------------------------------------------------------------------------------

| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          |     1 |     4 |    34   (3)| 00:00:01 |

|   1 |  SORT AGGREGATE       |          |     1 |     4 |            | |

|*  2 |   INDEX FAST FULL SCAN| I_TEST_1 | 52933 |   206K|    34   (3)| 00:00:01 |

----------------------------------------------------------------------------------


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

   2 - filter("OBJECT_ID" IS NOT NULL)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        124  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

select count(distinct object_id) from test;
select count(*) from (select object_id from test group by object_id);
这是itpub上的一个例子,严格来说这2个语句并不是等价的，第一个语句会忽略null，因此只要object_id上有索引
，同时索引比表小的情况就会用索引扫描代替全表扫描，而第二个语句则不会.
第一个语句和select count(*) from (select object_id from test where object_id is not null group by object_id);
是等价的.