oracle hint有效范围是什么

2023-10-17 1415阅读

这篇文章给大家介绍oracle hint有效范围是什么，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。hint的范围

parameter hints对整个sql有效率，其它hints只对单个查询块起作用，要在查询块内指定hints

当子查询，内联view时候会有多个查询块，要为每个查询块加hint控制该查询块（hint有效范围控制在查询块内）

SQL> create table t1 (a int,b varchar2(10));Table created.SQL> create table t2 (a2 int,b2 varchar2(10));Table created.SQL>SQL> insert into t1 values(1,’a’);1 row created.SQL> insert into t2 values(1,’b’);1 row created.SQL> commit;Commit complete.SQL> create index t1_id on t1(a);Index created.

SQL> create index t2开发云主机域名_id on t2(a2);Index created.

SQL> execute dbms_stats.gather_table_stats(‘XH’,’T1′);PL/SQL procedure successfully completed.SQL> execute dbms_stats.gather_table_stats(‘XH’,’T2′);PL/SQL procedure successfully completed.

SQL> set autotrace trace exp

SQL> select * from t1 where a=(select a2 from t2 where a2=1 );Execution Plan

———————————————————-

Plan hash value: 3305116341————————————————————————————-

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

————————————————————————————-

| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T1_ID | 1 | | 1 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | T2_ID | 1 | 3 | 1 (0)| 00:00:01 |

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

————————————————— 2 – access(“A”= (SELECT “A2” FROM “T2” “T2” WHERE “A2″=1))

3 – access(“A2″=1)SQL> select /*+full(t1)*/ * from t1 where a=(select a2 from t2 where a2=1 );Execution Plan

———————————————————-

Plan hash value: 1681039550—————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

—————————————————————————

| 0 | SELECT STATEMENT | | 1 | 5 | 4 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T1 | 1 | 5 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN| T2_ID | 1 | 3 | 1 (0)| 00:00:01 |

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

————————————————— 1 – filter(“A”= (SELECT “A2” FROM “T2” “T2” WHERE “A2″=1))

2 – access(“A2″=1)

全局hint使用.引用包含在其他查询块中的对象(引用的查询块已经有别名才行,查询块别名不是表别名)

SQL> select * from t1 ts where a=(select /*+full(t2) full(ts.t1)*/a2 from t2 where a2=1 );Execution Plan

———————————————————-

Plan hash value: 2910250514————————————————————————————-

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

————————————————————————————-

| 0 | SELECT STATEMENT | | 1 | 5 | 5 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T1_ID | 1 | | 1 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL | T2 | 1 | 3 | 3 (0)| 00:00:01 |

—————————————————————————–开发云主机域名——–Predicate Information (identified by operation id):

————————————————— 2 – access(“A”= (SELECT /*+ FULL (“T2”) */ “A2” FROM “T2” “T2” WHERE

“A2″=1))

3 – filter(“A2″=1)SQL> with

2 t1_t as (select * from t1)

3 select t1_t.a,t2.b2 from t2,t1_t where t2.a2=t1_t.a

4 /Execution Plan

———————————————————-

Plan hash value: 978323357————————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

————————————————————————————–

| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | | | | |

| 2 | NESTED LOOPS | | 1 | 8 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | T1_ID | 1 | 3 | 1 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | T2_ID | 1 | | 0 (0)| 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 5 | 1 (0)| 00:00:01 |

————————————————————————————–Predicate Information (identified by operation id):

————————————————— 4 – access(“T2″.”A2″=”T1″.”A”)

SQL> with

2 t1_t as (select * from t1)

3 select/*+full(t2) full(t1_t.t1)*/ t1_t.a,t2.b2 from t2,t1_t where t2.a2=t1_t.a

4 /Execution Plan

———————————————————-

Plan hash value: 2959412835—————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

—————————————————————————

| 0 | SELECT STATEMENT | | 1 | 8 | 7 (15)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 8 | 7 (15)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T1 | 1 | 3 | 3 (0)| 00:00:01 |

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

————————————————— 1 – access(“T2″.”A2″=”T1″.”A”)SQL>假如子查询没有别名，可以制作一个别名为子查询(用qb_name hint制作),10g才能用qb_nameSQL> with

2 t1_t as (select /*+qb_name(sq)*/ * from t1)

3 select /*+qb_name(sq_t) full(@sq t1) full(@sq_t t2)*/ t1_t.a,t2.b2 from t2,t1_t where t2.a2=t1_t.a

4 /Execution Plan

———————————————————-

Plan hash value: 2959412835—————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

—————————————————————————

| 0 | SELECT STATEMENT | | 1 | 8 | 7 (15)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 8 | 7 (15)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T1 | 1 | 3 | 3 (0)| 00:00:01 |

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

————————————————— 1 – access(“T2″.”A2″=”T1″.”A”)SQL> SQL> select /*+qb_name(sq) full(@sq t1) full(@sq_t t2)*/* from t1 where a=(select /*+qb_name(sq_t)*/ a2 from t2 where a2=1);Execution Plan

———————————————————-Plan hash value: 1484901111—————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

—————————————————————————

| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL | T1 | 1 | 5 | 3 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 (0)| 00:00:01 |

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

————————————————— 1 – filter(“A”= (SELECT /*+ QB_NAME (“SQ_T”) FULL (“T2”) */ “A2”

FROM “T2” “T2” WHERE “A2″=1))

2 – filter(“A2″=1)

SQL> SQL> select /*+qb_name(sq)*/* from t1 where a=(select /*+qb_name(sq_t) full(@sq t1) full(@sq_t t2)*/ a2 from t2 where a2=1);

Execution Plan

———————————————————-

Plan hash value: 1484901111—————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

—————————————————————————

| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL | T1 | 1 | 5 | 3 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 (0)| 00:00:01 |

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

————————————————— 1 – filter(“A”= (SELECT /*+ QB_NAME (“SQ_T”) FULL (“T2”) */ “A2”

FROM “T2” “T2” WHERE “A2″=1))

2 – filter(“A2″=1)

使用query optimizer生成的别名SQL> explain plan set statement_id=’xh’ for select * from t1 where a=(select a2 from t2 where a2=1);Explained.SQL> select * from table(dbms_xplan.display(null,’xh’,’basic +alias’));PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

Plan hash value: 3305116341———————————————

| Id | Operation | Name |

———————————————

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 |

| 2 | INDEX RANGE SCAN | T1_ID |

| 3 | INDEX RANGE SCAN | T2_ID |

———————————————

PLAN_TABLE_OUTPUT

—————————————————————————————————————————————————————————————————————————————————————————————-开发云主机域名——————–

Query Block Name / Object Alias (identified by operation id):

————————————————————- 1 – SEL$1 / T1@SEL$1

2 – SEL$1 / T1@SEL$1

3 – SEL$2 / T2@SEL$217 rows selected.SEL$为查询块前缀名（sel=select,cri$=create index,del$=delete,ins$=insert,misc$=lock table或多类语句,mrg$=merge,set$=union&minus,upd$=update）

顺序是按sql语句parse阶段查询块出现位置(左-右)，所以sel$1表示 select t1那个查询块，sel$2 表示select t2那个查询块

SQL> select * from t1 where a=(select /*+full(@sel$2 t2) full(@sel$1 t1)*/a2 from t2 where a2=1 );Execution Plan

———————————————————-

Plan hash value: 1484901111—————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

—————————————————————————

| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL | T1 | 1 | 5 | 3 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 (0)| 00:00:01 |

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

————————————————— 1 – filter(“A”= (SELECT /*+ FULL (“T2”) */ “A2” FROM “T2” “T2” WHERE

“A2″=1))

2 – filter(“A2″=1)另外表有别名的话hint中要用别名

SQL> select * from t1 ts where a=(select /*+full(@sel$2 t2) full(@sel$1 ts)*/a2 from t2 where a2=1 );Execution Plan

———————————————————-

Plan hash value: 1484901111—————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

—————————————————————————

| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL | T1 | 1 | 5 | 3 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 (0)| 00:00:01 |

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

————————————————— 1 – filter(“A”= (SELECT /*+ FULL (“T2”) */ “A2” FROM “T2” “T2” WHERE

“A2″=1))

2 – filter(“A2″=1)*另外parameter hints对整个sql有效率(例如hint all_rows,gather_plan_statistics之类)

*11g中v$sql_hint可以查看可以使用的hint列表

关于oracle hint有效范围是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。