sql exists oracle,ORACLE 关于带有exists子句的SQL，HINT的写法

先看一条语句。

apollo@CRMG>select * from wxh_tbd1 a where exists ( select 1 from wxh_tbd2 b where a.table_name=b.table_name and b.read_only='YES');

-------------------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------------

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

|   1 |  NESTED LOOPS                  |          |       |       |            |          |

|   2 |   NESTED LOOPS                 |          |     1 |   244 |     5  (20)| 00:00:01 |

|   3 |    SORT UNIQUE                 |          |     1 |    22 |     2   (0)| 00:00:01 |

|   4 |     TABLE ACCESS BY INDEX ROWID| WXH_TBD2 |     1 |    22 |     2   (0)| 00:00:01 |

|*  5 |      INDEX RANGE SCAN          | SS       |     1 |       |     1   (0)| 00:00:01 |

|*  6 |    INDEX RANGE SCAN            | T        |     1 |       |     1   (0)| 00:00:01 |

|   7 |   TABLE ACCESS BY INDEX ROWID  | WXH_TBD1 |     1 |   222 |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

这个语句的外层查询(exists ( select 1 from wxh_tbd2 b where a.table_name=b.table_name and b.read_only='YES'))返回的数据量非常少，因为read_only是yes的几乎没有数据，顶多就一两条，因此驱动表选择wxh_tbd2，，再与wxh_tbd1做 nest loop是一个非常优秀的执行计划。

我们的这个语句逻辑很简单，ORACLE得出了正确的执行计划。可是SQL语句如果非常复杂，或者统计信息不够准，或者是CBO本身缺陷，等等一些原因，ORACLE可能会采用如下的一些执行计划：

-----------------------------------------

| Id  | Operation            | Name     |

-----------------------------------------

|   0 | SELECT STATEMENT     |          |

|*  1 |  HASH JOIN SEMI      |          |

|*  2 |   TABLE ACCESS FULL  | WXH_TBD1 |

|   3 |   TABLE ACCESS FULL  | WXH_TBD2 |

-----------------------------------------

--------------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |                  |  3160 |   752K|   144   (2)| 00:00:02 |

|*  1 |  HASH JOIN RIGHT SEMI   |                  |  3160 |   752K|   144   (2)| 00:00:02 |

|*  2 |   VIEW                  | index$_join$_002 | 12640 |   271K|   111   (1)| 00:00:02 |

|*  3 |    HASH JOIN            |                  |       |       |            |          |

|*  4 |     INDEX RANGE SCAN    | SS               | 12640 |   271K|    39   (0)| 00:00:01 |

|   5 |     INDEX FAST FULL SCAN| S                | 12640 |   271K|    71   (0)| 00:00:01 |

|   6 |   TABLE ACCESS FULL     | WXH_TBD1         |  3160 |   685K|    32   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

这些都是比较糟糕的执行计划，特别是第一个。    那么对于这种exists语句，我们如何通过hint来得到我们期望的执行计划呢？

hint的作用域只能在自己所属的query block里起作用。这里不介绍什么是query block，可以去看下性能诊断艺术，写的非常的经典。

说明：测试涉及的两个表wxh_tbd1,wxh_tbd2都是根据dba_tables创建，两个表的table_name字段上都有索引，表wxh_tbd2的read_only字段有索引ss。

select * from wxh_tbd1 a where exists( select 1 from wxh_tbd2 b where a.table_name=b.table_name and b.read_only='YES');

上面的SQL里，红色字体部分是一个query block,蓝色部分是一个query block。对于一个这么的查询，我们希望得到的执行计划是wxh_tbd2作为驱动，然后再与wxh_tbd1做nest loop。即hint就是leading(wxh_tbd2) use_nl(wxh_tbd1)，那这个hint具体该怎么写呢？

我们先来看看，系统默认的为这个查询生成的query block名称：

apollo@CRMG>select * from table(dbms_xplan.display_cursor('dgcy0vp6abd2q',null,'all'));------------无用输出信息略

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

-------------------------------------------------------------

1 - SEL$5DA710D3

4 - SEL$5DA710D3 / B@SEL$2

5 - SEL$5DA710D3 / B@SEL$2

6 - SEL$5DA710D3 / A@SEL$1

7 - SEL$5DA710D3 / A@SEL$1

@SEL$1这个query block其实就是上面SQL语句红色部分代表的query block，SEL$2代表的是上面SQL蓝色字体部分的query block.那为什么还多出了一个SEL$5DA710D3，这是因为ORACLE在逻辑优化(逻辑优化处于SQL语句解析阶段)的时候会把exists后面的查询unnest到内层查询里，因此就生成了一个全新的SQL 语句，这个SQL没有任何的嵌套与查询子句，这个SQL语句的query block就是SEL$5DA710D3。

这个语句添加HINT，如下的方法都是无效的：

Select /*+ leading(@SEL$2 B@SEL$2) use_nl(A) */ * from wxh_tbd1 a where exists ( select 1 from wxh_tbd2 b where a.table_name=b.table_name and b.read_only='YES');

Select /*+ leading(@SEL$2 B) use_nl(A) */ * from wxh_tbd1 a where exists ( select 1 from wxh_tbd2 b where a.table_name=b.table_name and b.read_only='YES');

Select /*+ leading(@SEL$5DA710D3 B) use_nl(A) */ * from wxh_tbd1 a where exists ( select 1 from wxh_tbd2 b where a.table_name=b.table_name and b.read_only='YES');

如下两种写法是正确的，leading的内容，必须写明是在SEL$5DA710D3这个query block内：

Select /*+ leading(@SEL$5DA710D3 B@SEL$2) use_nl(A) */ * from wxh_tbd1 a where exists ( select 1 from wxh_tbd2 b where a.table_name=b.table_name and b.read_only='YES');

Select /*+ leading(@SEL$5DA710D3 B@SEL$2) use_nl(@SEL$5DA710D3 A@SEL$1) */ * from wxh_tbd1 a where exists ( select 1 from wxh_tbd2 b where a.table_name=b.table_name and b.read_only='YES');

后记：

晕死了，知识还得灵活运用啊。其实我们可以通过outline学习下该如何写hint，我们知道outline之所以能提供稳定的执行计划，其实就是对SQL加了一堆的hint集。我们可以通过outline来看看这种hint该如何写。MD,前天花了我大半天去研究，其实借鉴outline的hint输出，很容易就知道hint该怎么写了。

我们为这个语句(正确执行计划的语句)创建outline，查看HINT：

apollo@CRMG>select hint from user_outline_hints;

NLJ_BATCHING(@"SEL$5DA710D3" "A"@"SEL$1")

USE_NL(@"SEL$5DA710D3" "A"@"SEL$1")

LEADING(@"SEL$5DA710D3" "B"@"SEL$2" "A"@"SEL$1")

INDEX(@"SEL$5DA710D3" "A"@"SEL$1" ("WXH_TBD1"."TABLE_NAME"))

FULL(@"SEL$5DA710D3" "B"@"SEL$2")

OUTLINE(@"SEL$2")

OUTLINE(@"SEL$1")

UNNEST(@"SEL$2")

OUTLINE_LEAF(@"SEL$5DA710D3")

ALL_ROWS

DB_VERSION('11.1.0.7')

OPTIMIZER_FEATURES_ENABLE('11.1.0.7')

IGNORE_OPTIM_EMBEDDED_HINTS

OUTLINE看来又多了一个作用，可以教我们如何写HINT。