ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL没有hint不是好系统

SQL没有hint不是好系统

原创 Linux操作系统 作者:syzxlyx_cu 时间:2009-09-28 11:14:47 0 删除 编辑

SQL没有hint不是好系统

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

如果一个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可以避免以后执行计划的改变,特别是在日期类型的字段上且伴随着范围查询。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20542911/viewspace-615724/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-09-28

  • 博文量
    125
  • 访问量
    124064