玉面飞龙的BLOG

一个数据库系统，如果它的表只采用一种物理存储格式，则不是一个好系统。

如果一个Oracle数据库应用，如果一个SQL Hint都没有使用，则不是一个好的应用。

CBO的诞生是因为RBO的不够聪明和过度死板; Hint的诞生是因为CBO的依赖性。要依赖于精确和实时的统计信息才能产生最优的执行计划。而有时候，Oracle软件无法比人们更能够理解人们的数据。

因此，有些统特例，如统计信息相对于数据变化和时间流逝变得不准确，CBO就需要Hint的帮助，选择正确的执行计划。

如日期类型的字段creation_date,存储着每条记录插入数据库的时间，当分析表的时候，会纪录该日期字段的最小值min_value()和最大值max_value()。但一旦分析完毕，新纪录再次插入的时候，其统计信息就不准确了，max_value()会小于刚插入的字段日期值。当作类似creation_date>sysdate这样的查询时候，CBO就认为没有记录满足该条件，因为sysdate > 过去分析表时候得到的max_value()。由此，可能产生不正确的执行计划。

看下面这个由日期范围查询导致的执行计划不稳定的例子。

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> create table t as select * from dba_objects;
Table created.

SQL> select count(*) from t;
COUNT(*)
———-
12307

SQL> update t set object_id=rownum,created=sysdate-rownum;
12307 rows updated.
SQL> commit;
Commit complete.

SQL> create unique index tidx on t(object_id);
Index created.

SQL> create index tidx on t (object_id);
Index created.

SQL> analyze table t compute statistics;
Table analyzed.

SQL>@strucutre_of_table T

OWNER U PAR Index Name Column Name
——- - — ——————————-
ORACLE N NO TIDX OBJECT_ID
ORACLE N NO TIDX2_CREATED CREATED

OWNER TABLE_NAME PAR NUM_ROWS BLOCKS
——- ————— ——— ——-
ORACLE T NO 12307 158

OWNER INDEX_NAME PAR LEAF_BLOCKS BLEVEL DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR SAMPLE
——- ————– ————— ——- ————— ——– —————– ——-
ORACLE TIDX2_CREATED NO 92 1 12307 12307 158 99
ORACLE TIDX NO 27 1 12307 12307 158 99

SQL> alter session set optimizer_mode=all_rows;
Session altered.

当前查询，Oracle选择在object_id列上的索引。

SQL> explain plan for select * from t where object_id=:1 and created> sysdate+10;
Explained.

SQL> @?/rdbms/admin/utlxpls
——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
——————————————————————–
| 0 | SELECT STATEMENT | | 1 | 80 | 2 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 80 | 2 |
|* 2 | INDEX RANGE SCAN | TIDX | 1 | | 1 |
——————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 - filter(”CREATED”>SYSDATE@!+10)
2 - access(”OBJECT_ID”=TO_NUMBER(:1))

使用Hint强制created字段上的索引，成本为3，高于上面的执行计划

SQL> explain plan for select /*+ index(t,tidx2_created) */ * from t where object_id=:1 and created> sysdate+10;
Explained.

SQL> @?/rdbms/admin/utlxpls
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 80 | 3 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 80 | 3 |
|* 2 | INDEX RANGE SCAN | TIDX2_CREATED | 1 | | 2 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 - filter(”OBJECT_ID”=TO_NUMBER(:1))
2 - access(”CREATED”>SYSDATE@!+10)

我们更改数据量

SQL> insert into t select * from t;
12307 rows created.

SQL> /
24614 rows created.

SQL> /
49228 rows created.

SQL> /
98456 rows created.

SQL> update t set created=sysdate-rownum/60;
196912 rows updated.

SQL> commit;
Commit complete.

重建索引是为了增加索引的高度blevel

SQL> drop index tidx;
Index dropped.

SQL> create index tidx on t(object_id) pct_free 50;
Index created.

SQL> analyze table t compute statistics;
Table analyzed.

SQL> @strucutre_of_table T

OWNER U PAR Index Name Column Name
——– — —————— ——————
ORACLE N NO TIDX OBJECT_ID
ORACLE N NO TIDX2_CREATED CREATED

OWNER TABLE_NAME TS_NAME PAR NUM_ROWS BLOCKS
————————— — ——– ——-
ORACLE T SYSTEM NO 196912 2516

OWNER INDEX_NAME PAR LEAF_BLOCKS BLEVEL DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR SAMPLE
———————– — ————— —— ————— ——– —————– ——
ORACLE TIDX NO 766 2 12307 196912 196912 99
ORACLE TIDX2_CREATED NO 1513 2 196912 196912 2514 99

索引高度变为2，这时候同样的SQL， CBO将选择使用created字段上的索引。

SQL> explain plan for select * from t where object_id=:1 and created> sysdate+10;
Explained.

SQL> @?/rdbms/admin/utlxpls
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 80 | 4 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 80 | 4 |
|* 2 | INDEX RANGE SCAN | TIDX2_CREATED | 1 | | 3 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 - filter(”OBJECT_ID”=TO_NUMBER(:1))
2 - access(”CREATED”>SYSDATE@!+100)

将created换成last_modified_date，做object_id=:1 and last_modified_date > “some date literal value” 可能会发生什么情况？

看似简单的SQL，当查询条件涉及的字段有多个索引可选项的时候，尤其要注意，或许使用Hint可以避免以后执行计划的改变，特别是在日期类型的字段上且伴随着范围查询。