Shulou(Shulou.com)06/01 Report--

This article introduces to you what is the effective scope of oracle hint, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Range of hint

Parameter hints is efficient for the whole sql. Other hints only works on a single query block. Specify hints within the query block.

When subquery and inline view, there will be multiple query blocks. Add hint to each query block to control the query block (hint valid range is controlled within the query block)

SQL > create table T1 (an int,b varchar2 (10))

Table created.

SQL > create table T2 (a2 int,b2 varchar2 (10))

Table created.

SQL >

SQL > insert into T1 values (1)

1 row created.

SQL > insert into T2 values (1)

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)

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)

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)

Global hint uses. Reference objects contained in other query blocks (the referenced query block already has an alias, and the query block alias is not a table alias)

SQL > select * from T1 ts where a = (select / * + full (T2) full (ts.t1) * / a2 from T2 where a2room1)

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 from 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 from as (select * from T1)

3 select/*+full (T2) full (t1_t.t1) * / T1 from t where t2.a2=t1_t.a 2.b2 from T2

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 >

If the subquery does not have an alias, you can make an alias as the subquery (made in qb_name hint). Only 10g can use qb_name.

SQL > with

2 T1 contains as (select / * + qb_name (sq) * / * from T1)

3 select / * + qb_name (sq_t) full (@ sq T1) full (@ sq_t T2) * / T1 where t2.a2=t1_t.a 2.b2 from T2 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 a2room1)

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 a2room1)

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)

Use aliases generated by query optimizer

SQL > explain plan set statement_id='xh' for select * from T1 where a = (select a2 from T2 where a2)

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$2

17 rows selected.

SEL$ is the query block prefix name (sel=select,cri$=create index,del$=delete,ins$=insert,misc$=lock table or multi-class statement, mrg$=merge,set$=union&minus,upd$=update)

The order is to query the location of the block in the parse phase of the sql statement (left-right), so sel$1 represents the query block of select T1, and sel$2 represents the query block of select T2

SQL > select * from T1 where a = (select / * + full (@ sel$2 T2) full (@ sel$1 T1) * / a2 from T2 where a2room1)

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)

In addition, if the table has an alias, the alias should be used in hint.

SQL > select * from T1 ts where a = (select / * + full (@ sel$2 T2) full (@ sel$1 ts) * / a2 from T2 where a2room1)

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)

* in addition, parameter hints is efficient for the entire sql (e.g. hint all_rows,gather_plan_statistics, etc.)

* v$sql_hint in 11g can view the list of available hint

About what is the effective scope of oracle hint to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.