ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle hint有效范围

oracle hint有效范围

Linux操作系统 作者:羽化残虹 时间:2017-09-27 11:05:58 0 删除 编辑

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_name

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

17 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列表

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

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

注册时间:2013-08-24

  • 博文量
    69
  • 访问量
    260433