[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提示,這樣更加支援我的理由...^_^.