[20180912]关于ANSI joins语法.txt

--//曾经写过一篇关于ANSI joins的问题,连接:http://blog.itpub.net/267265/viewspace-1988395/
--//提到我个人偏向于使用+语法,链接提到oracle实际上内部转化为+的语法.

--//最近优化中,发现使用qb_name提示,在ANSI joins语法中存在问题.通过例子说明:

1.环境:
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

2.使用ansi joins语法:

SCOTT@test01p> select /*+ qb_name(test) */ ename,dname from dept left join emp on dept.deptno=emp.deptno;
...

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  b5kg9zvurubjy, child number 0
-------------------------------------
select /*+ qb_name(test) */ ename,dname from dept left join emp on
dept.deptno=emp.deptno
Plan hash value: 2251696546
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |       |     5 (100)|          |       |       |          |
|   1 |  MERGE JOIN OUTER            |         |     15 |   330 |     5   (0)| 00:00:01 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 |    52 |     2   (0)| 00:00:01 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      4 |       |     1   (0)| 00:00:01 |       |       |          |
|*  4 |   SORT JOIN                  |         |     14 |   126 |     3   (0)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL         | EMP     |     14 |   126 |     3   (0)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$0A195698
   2 - SEL$0A195698 / DEPT@SEL$1
   3 - SEL$0A195698 / DEPT@SEL$1
   5 - SEL$0A195698 / EMP@SEL$1
--//前面定义的qb_name(test),也就是Query Block Name不起作用.

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

   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO")

3.使用+语法:
SCOTT@test01p> select /*+ qb_name(test) */ ename,dname from dept , emp where dept.deptno=emp.deptno(+);

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a8m9mmj3j7zgg, child number 0
-------------------------------------
select /*+ qb_name(test) */ ename,dname from dept , emp where
dept.deptno=emp.deptno(+)
Plan hash value: 2251696546
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |       |     5 (100)|          |       |       |          |
|   1 |  MERGE JOIN OUTER            |         |     15 |   330 |     5   (0)| 00:00:01 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 |    52 |     2   (0)| 00:00:01 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      4 |       |     1   (0)| 00:00:01 |       |       |          |
|*  4 |   SORT JOIN                  |         |     14 |   126 |     3   (0)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL         | EMP     |     14 |   126 |     3   (0)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - TEST
   2 - TEST / DEPT@TEST
   3 - TEST / DEPT@TEST
   5 - TEST / EMP@TEST
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO")

--//可以发现qb_name定义的提示起作用.

--//可以发现在ansi joins语法中无法很好支持qb_name提示,这样更加支持我的理由...^_^.