sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

案例Oracle 10G HINT是什么如何使用及注意事项及多种使用方法

时间:2016-03-24 21:39   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净

Oracle 10g HINT在新增列方式指定案例

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

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

Table created.

SQL> create index ind_hint on test_hint(owner,object_type);

Index created.

SQL>  exec dbms_stats.gather_table_stats(user, 'TEST_HINT', method_opt => 'for all indexed columns size 100',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set auto trace exp
SQL> select * from test_hint where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 11101196

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 23272 |  2113K|   161   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST_HINT | 23272 |  2113K|   161   (1)| 00:00:02 |
-------------------------------------------------------------------------------

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

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

SQL> select /*+index(a)*/ * from test_hint  a where owner = 'SYS';


Execution Plan
----------------------------------------------------------
Plan hash value: 890897193

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

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

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

SQL> select /*+index(a ind_hint)*/ * from test_hint  a where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 890897193

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

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

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

SQL> select /*+index(a (owner,object_type))*/ * from test_hint  a where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 890897193

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

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

   2 - access("OWNER"='SYS')
--指定和index完全一致的列,走index

SQL> select /*+index(a (owner))*/ * from test_hint  a where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 890897193

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

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

   2 - access("OWNER"='SYS')
--指定列和where条件一致,也可以使用该index

SQL> select /*+index(a (object_id))*/ * from test_hint  a where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 11101196

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 23272 |  2113K|   161   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST_HINT | 23272 |  2113K|   161   (1)| 00:00:02 |
-------------------------------------------------------------------------------

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

   1 - filter("OWNER"='SYS')
--虽然是index中的一个列,但是由于不是where条件中,所以不能被使用

SQL> select /*+index(a (owner))*/ * from test_hint  a where object_type = 'TABLE';

Execution Plan
----------------------------------------------------------
Plan hash value: 1755360976

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |  1752 |   159K|   104   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT |  1752 |   159K|   104   (0)| 00:00:02 |
|*  2 |   INDEX SKIP SCAN           | IND_HINT  |  1752 |       |    25   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"='TABLE')
       filter("OBJECT_TYPE"='TABLE')

总结:

--指定index的第一列,虽然不在where中,但是还是会使用index

--说明:使用/*+ index(table_alias (column_names)) */方式的hint,需要先测试,有可能不能达到预期效果

-----------------温馨提示--------------------
操作有风险,动手需谨慎
Oracle研究中心 www.oracleplus.net
本文由大师惜分飞分享,转载请尽量保留本站网址。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例Oracle 10G HINT是什么如何使用及注意事项及多种使用方法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/17.html

Oracle研究中心

关键词:

HINT案例

HINT 索引

Oracle 10G HINT案例

HINT使用方法