OUTLINE_DATA的组合HINT修改有效改变SQL计划

前言：很多时候一个执行计划或者是访问方式（索引列不对），或者是连接方式不对（本来HASH结果确是NL），
又或者是连接的顺序不对；总而言之就是“基本面是好，但还需要调整”；
如下：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


SQL> create table t1 as select * from dba_objects;

Table created.

SQL> insert into t1 select * from t1;

75216 rows created.

SQL> /

150432 rows created.

SQL> /

300864 rows created.

SQL> /

601728 rows created.

SQL> /

1203456 rows created.

SQL> commit;

Commit complete.
create table t2 as select * from dba_objects where rownum<11;
Table created.

create table t3 as select * from dba_objects;
Table created.

select t1.status,t2.owner,t3.object_name
from t1,t2,t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t2.object_id=54;

Plan hash value: 98820498

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |   617 (100)|          |
|*  1 |  HASH JOIN          |      |  2625K|   317M|   617   (3)| 00:00:08 |
|*  2 |   HASH JOIN         |      |  2930 |   311K|   304   (1)| 00:00:04 |
|*  3 |    TABLE ACCESS FULL| T2   |     3 |    90 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T3   |   896 | 70784 |   300   (1)| 00:00:04 |
|*  5 |   TABLE ACCESS FULL | T1   |   896 | 16128 |   300   (1)| 00:00:04 |
----------------------------------------------------------------------------

--假定我们要让id=2和ID=5的两个结果集实行NL连接，那么采用OUTLINE_DATA只要在原来的基本上修改几个字母即可；
select 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      FULL(@"SEL$1" "T3"@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "T3"@"SEL$1" "T1"@"SEL$1")
      USE_HASH(@"SEL$1" "T3"@"SEL$1")
      USE_NL(@"SEL$1" "T1"@"SEL$1")    --把原来的USE_HASH单词换成USE_NL即可
      END_OUTLINE_DATA
  */
  t1.status,t2.owner,t3.object_name
from t1,t2,t3
where t1.object_id=t2.object_id
and t2.object_id=t3.object_id
and t2.object_id=54;
  

Plan hash value: 38608240

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |   875K(100)|          |
|   1 |  NESTED LOOPS       |      |  2625K|   317M|   875K  (1)| 02:55:03 |
|*  2 |   HASH JOIN         |      |  2930 |   311K|   304   (1)| 00:00:04 |
|*  3 |    TABLE ACCESS FULL| T2   |     3 |    90 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T3   |   896 | 70784 |   300   (1)| 00:00:04 |
|*  5 |   TABLE ACCESS FULL | T1   |   896 | 16128 |   299   (1)| 00:00:04 |
----------------------------------------------------------------------------

--可以看到ID=1由原来HASH变成NL；
这种修改在实行SQL的计划绑定时特别有效，复杂SQL经过转换，比如多出了临时视图，进行了SQL的转换，
但在局部计划不合理时，如果你直接从SQL上加HINT有时很难调整浪费时间，而在OUTLINE_DATE基础上却
可以轻松搞定，当然前提是你得首先确定SQL哪里不合理怎么修改才是重要的；