SQL优化（2）——有顺序的索引数据

在索引中的数据，已经预先排好顺序了。所以当SQL中指定按照索引列排序时，在执行计划中不需要再排序了。

 

1. 创建测试环境

 

SQL> create table test2 as select * from dba_objects;

 

表已创建。

 

SQL> create index ind_test2 on test2(object_id);

 

索引已创建。

 

2. 查询结果按照索引列排序。查看其执行计划中，并没有排序操作：

 

SQL> explain plan for

  2  select * from test2 where object_id > 100 order by object_id;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 4170160382

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

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

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

|   0 | SELECT STATEMENT            |           | 55999 |  9679K|   847   (1)| 00:00:11 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2     | 55999 |  9679K|   847   (1)| 00:00:11 |

|*  2 |   INDEX RANGE SCAN          | IND_TEST2 | 55999 |       |   124   (2)| 00:00:02 |

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

 

Predicate Information (identified by operation id):

 

PLAN_TABLE_OUTPUT

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

 

   2 - access("OBJECT_ID">100)

       filter("OBJECT_ID">100)

 

Note

-----

   - dynamic sampling used for this statement

 

已选择19行。

 

3. 查询结果按照非索引列排序，查看其执行计划中，产生了排序操作：

 

SQL> explain plan for

  2  select * from test2 where object_id > 100 order by object_name;

 

已解释。

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 2452037388

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

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

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

|   0 | SELECT STATEMENT   |       | 55999 |  9679K|       |  2345   (1)| 00:00:29 |

|   1 |  SORT ORDER BY     |       | 55999 |  9679K|    22M|  2345   (1)| 00:00:29 |

|*  2 |   TABLE ACCESS FULL| TEST2 | 55999 |  9679K|       |   161   (4)| 00:00:02 |

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

 

Predicate Information (identified by operation id):

 

PLAN_TABLE_OUTPUT

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

 

   2 - filter("OBJECT_ID">100)

 

Note

-----

   - dynamic sampling used for this statement

 

已选择18行。

 

4. 下面看一个实际的例子

 

-- 1. 创建测试表

SQL> create table test3(a number, b varchar2(20));

 

表已创建。

 

SQL> create index ind_test3 on test3(a);

 

索引已创建。

 

SQL> insert into test3 values(4, 'yct');

 

已创建 1 行。

 

SQL> insert into test3 values(2, 'yxt');

 

已创建 1 行。

 

SQL> insert into test3 values(5, 'jlt');

 

已创建 1 行。

 

SQL> insert into test3 values(1, 'orange');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

-- 2. 此时查询test3，列a显示为乱序

SQL> select * from test3;

 

         A B

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

         4 yct

         2 yxt

         5 jlt

         1 orange

 

-- 3. 当使用索引时，列a自动排序

SQL> select * from test3 where a > 1;

 

         A B

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

         2 yxt

         4 yct

         5 jlt

 

-- 4. 执行效果就像是加了一个order by a

SQL> select * from test3 where a > 1 order by a;

         A B

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

         2 yxt

         4 yct

         5 jlt

 

-- 5. 而如果没有索引，是不会产生这种排序效果的

SQL> drop index ind_test3;

 

索引已删除。

 

SQL> select * from test3 where a > 1;

 

         A B

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

         4 yct

         2 yxt

         5 jlt