yangtingkun常数复合索引应用案例

受楼下老大的NULL贴启发，讲两个NULL与索引的小技巧

1.既然NULL是可以进复合索引的，在我们需要对NULL进行索引时，就可以构造一个“伪复合索引”：
CREATE INDEX my_index ON my_table(my_column,0);
后面这个零就是加入的伪列。这样以后在有 my_column IS NULL 的条件就可以利用索引了（当然最终使用与否还得由CBO决定）。
2.不想索引的行，即使不是NULL, 也可用函数把它剔除。
假设有status_id列，里面有0:未处理,1:已处理两种状态，我们关心的仅仅是0的行，处理完就会改成1. 这样表中0的行仅仅是少数，大部分是1的行，数据量多了BTREE索引的维护就有开销。
这时可以建立这样的索引：
CREATE INDEX my_index ON my_table(DECODE(status_id,0,0));
它只对0行数据进行索引。当你要取未处理数据时，SELECT * FROM my_table WHERE DECODE(status_id,0,0)=0 就可以高效利用索引。

另有一个陷阱要小心避免：
SELECT ... FROM t1 WHERE my_column NOT IN (SELECT my_column FROM t2);
假如这个子查询SELECT my_column FROM t2里面有NULL, 那么主查询就一行数据也出不来了! 必须改用:
SELECT ... FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.my_column = t1.my_column);

常数复合索引应用案例
===========================================================
作者: yangtingkun(http://yangtingkun.itpub.net)
发表于: 2011.05.19 23:46
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/518108
---------------------------------------------------------------
从一个客户的真实优化案例引申的问题。
客户的一个数据库需要进行优化，不过由于程序开发方没有介入，因此这次优化无法对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的全表扫描。