案例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);
是等价的.