[ORALCE]SQL 优化案例之 组合索引与排序

组合索引与排序

步骤一 构造测试表

drop table TX1 purge;
create table TX1 as select * from dba_objects;
create index idx_object_id on TX1(owner,object_type);
alter table TX1 modify owner not NULL;

exec dbms_stats.gather_table_stats('SYS','TX1',cascade=>true);

测试一,索引无排序

SQL> set autotrace traceonly
SQL> select /*+index(TX1,idx_object_id)*/ * from TX1 order by owner desc ,object_type asc;

73396 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 509348553

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             | 73396 |    9461K|         |    5932   (1)| 00:00:01 |
|   1 |  SORT ORDER BY                 |             | 73396 |    9461K|      13M|    5932   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TX1         | 73396 |    9461K|         |    3627   (1)| 00:00:01 |
|   3 |    INDEX FULL SCAN             | IDX_OBJECT_ID | 73396 |         |         |     259   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
     60  recursive calls
      0  db block gets
       3726  consistent gets
      0  physical reads
      0  redo size
    4637967  bytes sent via SQL*Net to client
      54269  bytes received via SQL*Net from client
       4895  SQL*Net roundtrips to/from client
      5  sorts (memory)
      0  sorts (disk)
      73396  rows processed

发现有许多内存排序

测试二,索引无有排序

drop index idx_object_id;
create index idx_object_id on TX1(owner desc ,object_type asc);
select /*+index(TX1,idx_object_id)*/ * from TX1 order by owner desc ,object_type asc;
Execution Plan
----------------------------------------------------------
Plan hash value: 3824983714

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            | 73396 |  9461K|  3638   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TX1        | 73396 |  9461K|  3638   (1)| 00:00:01 |
|   2 |   INDEX FULL SCAN        | IDX_OBJECT_ID | 73396 |        |    270   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      13190  consistent gets
    268  physical reads
      0  redo size
    4610085  bytes sent via SQL*Net to client
      54269  bytes received via SQL*Net from client
       4895  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      73396  rows processed

测试结果,发现内存排序没有了,cost降低了不少

测试三,索引排序反转

select /*+index(TX1,idx_object_id)*/ * from TX1 order by owner asc ,object_type desc;

SQL> select /*+index(TX1,idx_object_id)*/ * from TX1 order by owner asc ,object_type desc;

73396 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 509348553

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             | 73396 |    9461K|         |    5943   (1)| 00:00:01 |
|   1 |  SORT ORDER BY                 |             | 73396 |    9461K|      13M|    5943   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TX1         | 73396 |    9461K|         |    3638   (1)| 00:00:01 |
|   3 |    INDEX FULL SCAN             | IDX_OBJECT_ID | 73396 |         |         |     270   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
       3634  consistent gets
      0  physical reads
      0  redo size
    4637361  bytes sent via SQL*Net to client
      54269  bytes received via SQL*Net from client
       4895  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      73396  rows processed

发现有内存排序,cost 还是很高

测试四,加hit 索引反转

SQL> select /*+index_desc(TX1,idx_object_id)*/ * from TX1 order by owner asc ,object_type desc;

73396 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1887026249

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            | 73396 |  9461K|  3638   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TX1        | 73396 |  9461K|  3638   (1)| 00:00:01 |
|   2 |   INDEX FULL SCAN DESCENDING| IDX_OBJECT_ID | 73396 |        |    270   (1)| 00:00:01 |
---------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      13182  consistent gets
      0  physical reads
      0  redo size
    4610122  bytes sent via SQL*Net to client
      54274  bytes received via SQL*Net from client
       4895  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      73396  rows processed

测试结果,发现没有内存排序,COST提高不少

总结，索引排序提高效率，可以在应用中使用