oracle sql 索引优化一例

来源：互联网发布：linux xz 编辑：程序博客网时间：2024/06/16 09:28

需要sql语句：

 SELECT *     FROM cux.CUX_SETT_XLA_LINES V    WHERE V.SOURCE_CODE = 'STR'    AND v.created_by = 0    AND v.accounting_date =DATE '2017-10-25'      ORDER BY V.CREATION_DATE DESC;

Plan hash value: 2341381659-------------------------------------------------------------------------------------------------| Id  | Operation          | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |-------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |                    |  4826 |   975K|       |  1053K  (2)| 03:30:46 ||   1 |  SORT ORDER BY     |                    |  4826 |   975K|  1624K|  1053K  (2)| 03:30:46 ||*  2 |   TABLE ACCESS FULL| CUX_SETT_XLA_LINES |  4826 |   975K|       |  1053K  (2)| 03:30:43 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("V"."ACCOUNTING_DATE"=TO_DATE(' 2017-10-25 00:00:00', 'syyyy-mm-dd               hh24:mi:ss') AND "V"."CREATED_BY"=0 AND "V"."SOURCE_CODE"='STR')

看下每个列的重复值情况

select count(distinct SOURCE_CODE) from  cux.CUX_SETT_XLA_LINES -- 9select count(distinct created_by) from  cux.CUX_SETT_XLA_LINES -- 13

这两列是数据是明显缺少位图索引的列。且这张表主要用于查询

那么位图索引是显然的选择了

SQL> create bitmap index i_x_sc on CUX_SETT_XLA_LINES(SOURCE_CODE)  2  /Index created.SQL> analyze index i_x_sc  2  validate  structure  3  /Index analyzed.SQL> show userUSER is "CUX"SQL> create bitmap index inx_create on CUX_SETT_XLA_LINES(created_by)  2  /Index created.

上述位图索引建好后，大小在30M以下

普通列建B树索引

SQL> create index inx_accdate on CUX_SETT_XLA_LINES (accounting_date);Index created.

该索引建好后大小为：
2.36 GB
B树索引还是占空间的

优化后的sql执行计划

Plan hash value: 1948297352----------------------------------------------------------------------------------------------------------------| Id  | Operation                         | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                  |                    |  4826 |   975K|       |  3536   (1)| 00:00:43 ||   1 |  SORT ORDER BY                    |                    |  4826 |   975K|  1624K|  3536   (1)| 00:00:43 ||   2 |   TABLE ACCESS BY INDEX ROWID     | CUX_SETT_XLA_LINES |  4826 |   975K|       |  3315   (1)| 00:00:40 ||   3 |    BITMAP CONVERSION TO ROWIDS    |                    |       |       |       |            |          ||   4 |     BITMAP AND                    |                    |       |       |       |            |          ||*  5 |      BITMAP INDEX SINGLE VALUE    | INX_CREATE         |       |       |       |            |          ||*  6 |      BITMAP INDEX SINGLE VALUE    | I_X_SC             |       |       |       |            |          ||   7 |      BITMAP CONVERSION FROM ROWIDS|                    |       |       |       |            |          ||*  8 |       INDEX RANGE SCAN            | INX_ACCDATE        |   579K|       |       |  1555   (1)| 00:00:19 |----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   5 - access("V"."CREATED_BY"=0)   6 - access("V"."SOURCE_CODE"='STR')   8 - access("V"."ACCOUNTING_DATE"=TO_DATE(' 2017-10-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

实际观察一下，将一条一个小时跑不出来的sql优化到300ms，提示何止1000倍呢。我们用很小的空间代价，换来极大的性能提升。

不过这只是oracle sql优化一个很简单的例子

阅读全文

1 0