[20180912]关于ANSI joins语法.txt

[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提示,这样更加支持我的理由...^_^.