0. 创建测试环境

-- Create table
drop table alex_t02 purge;
create table alex_t02 as select rownum id, a.* from dba_objects a;

-- Create/Recreate primary, unique and foreign key constraints 
alter table alex_t02 add constraint pk_alex_t02 primary key (id) using index;
create index idx_alex_t02_objid on alex_t02 (object_id);
create index idx_alex_t02_objn on alex_t02 (object_name);
create index idx_alex_t02_typown on alex_t02 (object_type,owner);

--Gather Statistics
exec dbms_stats.gather_table_stats('dbmgr','alex_t02')
exec dbms_stats.gather_index_stats('dbmgr','pk_alex_t02')
exec dbms_stats.gather_index_stats('dbmgr','idx_alex_t02_objid')
exec dbms_stats.gather_index_stats('dbmgr','idx_alex_t02_objn')
exec dbms_stats.gather_index_stats('dbmgr','idx_alex_t02_typown')

1. 索引被无视:列与列的对比

SQL> select * from alex_t02 where id=object_id;
----------------------------------------------------------
Plan hash value: 1157084622
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    99 |   137   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| ALEX_T02 |     1 |    99 |   137   (3)| 00:00:02 |
------------------------------------------------------------------------------

2. 索引被无视:存在NULL条件

SQL> select * from alex_t02 where object_id is not null;
----------------------------------------------------------
Plan hash value: 1157084622
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 40729 |  3937K|   138   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| ALEX_T02 | 40729 |  3937K|   138   (3)| 00:00:02 |
------------------------------------------------------------------------------

3. 索引被无视:not条件

SQL> select * from alex_t02 where object_id<>500;
----------------------------------------------------------
Plan hash value: 1157084622
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 40728 |  3937K|   138   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| ALEX_T02 | 40728 |  3937K|   138   (3)| 00:00:02 |
------------------------------------------------------------------------------

SQL> select * from alex_t02 where object_id not in (100,200,300,400);
----------------------------------------------------------
Plan hash value: 1157084622
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 40725 |  3937K|   139   (4)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| ALEX_T02 | 40725 |  3937K|   139   (4)| 00:00:02 |
------------------------------------------------------------------------------

SQL> select * from alex_t02 where not exists (select 1 from alex_t01 where alex_t02.id=alex_t01.id);
----------------------------------------------------------
Plan hash value: 1822376894
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    23 |  2392 |   150  (11)| 00:00:02 |
|   1 |  NESTED LOOPS ANTI |             |    23 |  2392 |   150  (11)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| ALEX_T02    | 40729 |  3937K|   137   (3)| 00:00:02 |
|*  3 |   INDEX UNIQUE SCAN| PK_ALEX_T01 | 99944 |   488K|     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------

4. 索引被无视:LIKE前置通配符

SQL> select * from alex_t02 where object_name like 'ALL%';
----------------------------------------------------------
Plan hash value: 3495118679
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     2 |   198 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T02          |     2 |   198 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T02_OBJN |     2 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

SQL> select * from alex_t02 where object_name like '%ALL';
----------------------------------------------------------
Plan hash value: 1157084622
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  2036 |   196K|   137   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| ALEX_T02 |  2036 |   196K|   137   (3)| 00:00:02 |
------------------------------------------------------------------------------

5. 索引被无视:条件列上使用函数

SQL> select * from alex_t02 where object_name=upper('ALL_RULE_SET_RULES');
----------------------------------------------------------
Plan hash value: 3495118679
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     2 |   198 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T02          |     2 |   198 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T02_OBJN |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

SQL> select * from alex_t02 where upper(object_name)='ALL_RULE_SET_RULES';
----------------------------------------------------------
Plan hash value: 1157084622
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   407 | 40293 |   137   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| ALEX_T02 |   407 | 40293 |   137   (3)| 00:00:02 |
------------------------------------------------------------------------------

6. 索引被无视:高区分度前导列的复合索引无法用于后置列查询

SQL> select * from alex_t02 where owner='DBMGR';
----------------------------------------------------------
Plan hash value: 1157084622
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  3133 |   302K|   136   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| ALEX_T02 |  3133 |   302K|   136   (2)| 00:00:02 |
------------------------------------------------------------------------------
Trackback

no comment untill now

Add your comment now

切换到手机版