常数复合索引应用案例

 

从一个客户的真实优化案例引申的问题。

 

 

客户的一个数据库需要进行优化，不过由于程序开发方没有介入，因此这次优化无法对SQL进行修改。

仅对数据库级的调整一般来说收效不大，不过发现客户数据库中个别的SQL存在性能问题，且这个性能问题已经影响到整个数据库。如果可以将这个SQL优化，那么可以解决目前数据库的性能问题。幸运的是，这个问题可以通过添加索引来进行优化。

模拟问题SQL如下：

SQL> select * from v$version;

BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

PL/SQL Release 9.2.0.4.0 - Production

CORE   9.2.0.3.0      Production

TNS for Linux: Version 9.2.0.4.0 - Production

NLSRTL Version 9.2.0.4.0 – Production

SQL> create table t (id number not null, created date, other char(200));

Table created.

SQL> insert into t select rownum, created, 'a' from all_objects;

31126 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'T')

PL/SQL procedure successfully completed.

SQL> var v_id number

SQL> var v_date varchar2(14)

SQL> explain plan for

 2 select count(*)

 3 from t

 4 where nvl(created, sysdate) > to_date(:v_date, 'yyyymmddhh24miss') - 3

 5 and id = :v_id;

Explained.

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

PLAN_TABLE_OUTPUT

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

 

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

| Id | Operation           | Name      | Rows | Bytes | Cost |

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

|  0 | SELECT STATEMENT    |            |    1 |   13 |   92 |

|  1 | SORT AGGREGATE     |            |    1 |   13 |      |

|* 2 |  TABLE ACCESS FULL | T          |    1 |   13 |   92 |

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

Predicate Information (identified by operation id):

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

  2 - filter(NVL("T"."CREATED",SYSDATE@!)>TO_DATE(:Z,'yyyymmddhh24miss')-3 AND "T"."ID"=TO_NUMBER(:Z))

Note: cpu costing is off

16 rows selected.

对于这个SQL，通过索引方式优化很简单，只需要建立ID和CREATED上的复合索引，就可以避免全表扫描：

SQL> create index ind_t_id_created on t (id, created);

Index created.

SQL> explain plan for

 2 select count(*)

 3 from t

 4 where nvl(created, sysdate) > to_date(:v_date, 'yyyymmddhh24miss') - 3

 5 and id = :v_id;

Explained.

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

PLAN_TABLE_OUTPUT

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

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

| Id | Operation           | Name            | Rows | Bytes | Cost |

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

|  0 | SELECT STATEMENT    |                  |    1 |   13 |    2 |

|  1 | SORT AGGREGATE     |                  |    1 |   13 |      |

|* 2 |  INDEX RANGE SCAN  | IND_T_ID_CREATED |    1 |   13 |    2 |

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

Predicate Information (identified by operation id):

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

  2 - access("T"."ID"=TO_NUMBER(:Z))

      filter(NVL("T"."CREATED",SYSDATE@!)>TO_DATE(:Z,'yyyymmddhh24miss')-3)

Note: cpu costing is off

17 rows selected.

Oracle之所以可以选择索引扫描，是由于复合索引中CREATED列为空的记录也会被保存。由于ID列为非空，而索引不会保存所有列全为空的情况，因此CREATED为空的记录同样可以在索引中找到。

事实上，即使ID不为空，由于另一个查询条件指定了ID = :V_ID，这使得访问的记录并不包括ID为空的记录，这使得复合索引仍然可以包括这个SQL需要访问的所有数据。

不过新的疑问来了，如果查询的SQL不包含ID列的限制条件，则目前的索引不在可用：

SQL> alter table t modify id null;

Table altered.

SQL> explain plan for

 2 select count(*)

 3 from t

 4 where nvl(created, sysdate) > to_date(:v_date, 'yyyymmddhh24miss') - 3;

Explained.

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

PLAN_TABLE_OUTPUT

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

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

| Id | Operation           | Name      | Rows | Bytes | Cost |

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

|  0 | SELECT STATEMENT    |            |    1 |    8 |   92 |

|  1 | SORT AGGREGATE     |            |    1 |    8 |      |

|* 2 |  TABLE ACCESS FULL | T          | 1556 | 12448 |   92 |

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

Predicate Information (identified by operation id):

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

  2 - filter(NVL("T"."CREATED",SYSDATE@!)>TO_DATE(:Z,'yyyymmddhh24miss')-3)

Note: cpu costing is off

16 rows selected.

针对这种情况，一种方法是找一个不为空的字段做联合索引，而更省空间的方法是建立复合常数索引：

SQL> create index ind_t_created0 on t(created, 0);

Index created.

SQL> explain plan for

 2 select count(*)

 3 from t

 4 where nvl(created, sysdate) > to_date(:v_date, 'yyyymmddhh24miss') - 3;

Explained.

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

PLAN_TABLE_OUTPUT

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

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

| Id | Operation            | Name          | Rows | Bytes | Cost |

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

|  0 | SELECT STATEMENT     |                |    1 |    8 |    4 |

|  1 | SORT AGGREGATE      |                |    1 |    8 |      |

|* 2 |  INDEX FAST FULL SCAN| IND_T_CREATED0 | 1556 | 12448 |    4 |

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

 

Predicate Information (identified by operation id):

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

  2 - filter(NVL("T"."CREATED",SYSDATE@!)>TO_DATE(:Z,'yyyymmddhh24miss')-3)

Note: cpu costing is off

16 rows selected.

最终通过建立一个包含常数的复合索引，从而避免了这个SQL的全表扫描。

 

 

（以上内容摘于网络，如有侵权，请告之，将第一时间删除）