ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10g新增列方式指定HINT

10g新增列方式指定HINT

原创 Linux操作系统 作者:wei-xh 时间:2011-06-07 10:32:34 0 删除 编辑

懒的写了,直接引用下:

http://yangtingkun.itpub.net/post/468/510277

看优化文档的时候发现,指定索引的HINT还可以通过列方式。


9i和以前的版本,索引提示的格式为/*+ index(table_alias) *//*+ index(table_alias index_name) */,但是在10g中不仅可以通过索引名称来确定HINT的索引,还可以通过指定列名的方式。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> create table t
2 as select *
3 from dba_objects;

Table created.

SQL> create index ind_t_owner_type
2 on t (owner, object_type);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all indexed columns size 100')

PL/SQL procedure successfully completed.

SQL> set autot trace exp
SQL> select *
2 from t
3 where wner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23299 | 2138K| 180 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 23299 | 2138K| 180 (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='SYS')

SQL> select /*+ index(t ind_t_owner_type) */ *
2 from t
3 where wner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 393469706

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23299 |2138K| 721 (1)| 00:00:11 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 23299 |2138K| 721 (1)| 00:00:11 |
|* 2 | INDEX RANGE SCAN | IND_T_OWNER_TYPE | 23299 | | 44 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SYS')

除了常规的索引名称方式,还可以通过ownerownerobject_type列的方式指定:

SQL> select /*+ index(t (t.owner)) */ *
2 from t
3 where wner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 393469706

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23299 |2138K| 721 (1)| 00:00:11 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 23299 |2138K| 721 (1)| 00:00:11 |
|* 2 | INDEX RANGE SCAN | IND_T_OWNER_TYPE | 23299 | | 44 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SYS')

SQL> select /*+ index(t (owner, object_type)) */ *
2 from t
3 where wner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 393469706

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23299 |2138K| 721 (1)| 00:00:11 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 23299 |2138K| 721 (1)| 00:00:11 |
|* 2 | INDEX RANGE SCAN | IND_T_OWNER_TYPE | 23299 | | 44 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SYS')

可以通过索引的所有列来指定,也可以通过前缀列的方式,索引列前面可以添加表的别名,如果省略则默认是前面指定别名的表。

但是,不能通过非前缀列指定索引,甚至指定列的次序与索引列顺序不一致都会导致HINT失效:

SQL> select /*+ index(t (object_type)) */ *
2 from t
3 where wner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23299 | 2138K| 180 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 23299 | 2138K| 180 (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='SYS')

SQL> select /*+ index(t (object_type, owner)) */ *
2 from t
3 where wner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23299 | 2138K| 180 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 23299 | 2138K| 180 (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='SYS')

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

下一篇: Linux Hugepages
请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2341146