[20171002]NESTED LOOPS(PARTITION OUTER).TXT
[20171002]NESTED LOOPS(PARTITION OUTER).TXT
--//昨天看,裡面提到執行計劃NESTED LOOPS(PARTITION OUTER).
--//第36頁:
(5)NESTED LOOPS (PARTITION OUTER)
以左外關聯的左邊資料集(或右外關聯的右邊資料集)為外迴圈，將左外關聯的右邊資料集(或右外關聯的左邊資料集)分組(分割槽)進行外關
聯匹配。
提示：對於分割槽左(右)外關聯，從邏輯上看，左(右)表需要與右(左)表中的資料分組(分割槽)分別做外關聯。如果實際操作也按照
這個邏輯實現，則意味著每次與一組資料進行關聯，都要讀取一次左(右)表資料。而在NESTED LOOPS PARTITION OUTER中，第一次讀取
左(右)表資料後，就被快取在私有記憶體中，從而避免了多次重複讀取共享記憶體資料。
--//注意後面幾句,被快取在私有記憶體中，從而避免了多次重複讀取共享記憶體資料。
--//如果sql語句走nest loops,利用這個特性可以減少邏輯讀,我重複測試書中的例子:
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
create table t_users as select * from dba_users;
create table t_tables as select * from dba_tables;
--//分析略,沒有建立任何索引.
2.測試1:
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SELECT /*+ use_nl(t_tables t4) */ owner,table_name,t4.username,t4.created FROM t_tables PARTITION BY (owner) RIGHT OUTER
JOIN t_users t4 ON t_tables.owner=t4.username;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 31ytmvz0ttfha, child number 0
-------------------------------------
select /*+ use_nl(t_tables t4) */ owner,table_name,t4.username,t4.create
d from t_tables partition by (owner) right outer join t_users t4 on
t_tables.owner=t4.username
Plan hash value: 2189670143
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1281 (100)| | 3704 |00:00:00.08 | 93 | | | |
| 1 | VIEW | | 1 | 2408 | 486K| 1281 (1)| 00:00:01 | 3704 |00:00:00.08 | 93 | | | |
| 2 | NESTED LOOPS PARTITION OUTER| | 1 | 2408 | 101K| 1281 (1)| 00:00:01 | 3704 |00:00:00.08 | 93 | | | |
| 3 | BUFFER SORT | | 28 | | | | | 1324 |00:00:00.01 | 3 | 4096 | 4096 | 4096 (0)|
| 4 | TABLE ACCESS FULL | T_USERS | 1 | 49 | 833 | 3 (0)| 00:00:01 | 49 |00:00:00.01 | 3 | | | |
|* 5 | FILTER | | 1324 | | | | | 2408 |00:00:00.08 | 90 | | | |
| 6 | SORT PARTITION JOIN | | 1324 | 49 | 1274 | 27 (0)| 00:00:01 | 117K|00:00:00.05 | 90 | 133K| 133K| 118K (0)|
| 7 | TABLE ACCESS FULL | T_TABLES | 1 | 49 | 1274 | 26 (4)| 00:00:01 | 2408 |00:00:00.01 | 90 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / from$_subquery$_003@SEL$2
2 - SEL$1
4 - SEL$1 / T4@SEL$1
7 - SEL$1 / T_TABLES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("T_TABLES"."OWNER"="T4"."USERNAME")
SCOTT@test01p> @expand_sql_text2.sql 1sc7wagyhgyay
SELECT "A1"."OWNER_0" "OWNER","A1"."TABLE_NAME_1" "TABLE_NAME","A1"."USERNAME_2" "USERNAME","A1"."CREATED_3" "CREATED"
FROM (SELECT "A2"."OWNER" "OWNER_0","A2"."TABLE_NAME" "TABLE_NAME_1","A3"."USERNAME" "USERNAME_2","A3"."CREATED"
"CREATED_3" FROM "SCOTT"."T_TABLES" "A2" PARTITION BY ( "A2"."OWNER" ) RIGHT OUTER JOIN "SCOTT"."T_USERS" "A3" ON "A2"."OWNER"="A3"."USERNAME") "A1"
PL/SQL procedure successfully completed.
--//注意看buffers=93,你可以發現雖然執行計劃是走NESTED LOOPS.邏輯讀並不是很高.正是利用了"快取在私有記憶體中，從而避免了多次
--//重複讀取共享記憶體資料".當然你看starts列,可以發現執行許多次.這樣會消耗CPU資源,對比下面的A-time就可以發現邏輯讀減少了,
--//A-TIME實際上是增加的.
--//如果刪除partition by (owner)看看:
select /*+ use_nl(t_tables t4) */ owner,table_name,t4.username,t4.created from t_tables right outer join t_users t4 on
t_tables.owner=t4.username;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 02vwt24q957g8, child number 0
-------------------------------------
select /*+ use_nl(t_tables t4) */ owner,table_name,t4.username,t4.create
d from t_tables right outer join t_users t4 on
t_tables.owner=t4.username
Plan hash value: 518654026
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1254 (100)| | 2430 |00:00:00.02 | 4431 |
| 1 | NESTED LOOPS OUTER| | 1 | 2430 | 102K| 1254 (1)| 00:00:01 | 2430 |00:00:00.02 | 4431 |
| 2 | TABLE ACCESS FULL| T_USERS | 1 | 49 | 833 | 3 (0)| 00:00:01 | 49 |00:00:00.01 | 9 |
|* 3 | TABLE ACCESS FULL| T_TABLES | 49 | 49 | 1274 | 26 (4)| 00:00:01 | 2408 |00:00:00.02 | 4422 |
-------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$0E991E55
2 - SEL$0E991E55 / T4@SEL$1
3 - SEL$0E991E55 / T_TABLES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T_TABLES"."OWNER"="T4"."USERNAME")
--//注意看buffers=4431.
--//開始以為2者返回記錄數量一致,仔細看A-rows列,可以發現2者並不等價(指結果集).
--//實際上執行如下:
SELECT /* use_nl(t_tables t4) */ t_tables.owner,t_tables.table_name,t4.username,t4.created
FROM t_tables PARTITION BY (owner) RIGHT OUTER JOIN t_users t4 ON t_tables.owner=t4.username;
--//相當於t_users做驅動表.把t_tables按照owner分割槽,然後個個分割槽再跟t_users每行右連線.輸出太長,我加入一個條件(t4.username='SCOTT');
SELECT /* use_nl(t_tables t4) */
t_tables.owner
,t_tables.table_name
,t4.username
,t4.created
FROM t_tables PARTITION BY (owner)
RIGHT OUTER JOIN t_users t4 ON t_tables.owner = t4.username
WHERE t4.username = 'SCOTT';
OWNER TABLE_NAME USERNAME CREATED
-------------------- --------------- -------- -------------------
APEX_040200 SCOTT 2013-06-28 11:35:40
APPQOSSYS SCOTT 2013-06-28 11:35:40
AUDSYS SCOTT 2013-06-28 11:35:40
CTXSYS SCOTT 2013-06-28 11:35:40
DBSNMP SCOTT 2013-06-28 11:35:40
DVSYS SCOTT 2013-06-28 11:35:40
FLOWS_FILES SCOTT 2013-06-28 11:35:40
GSMADMIN_INTERNAL SCOTT 2013-06-28 11:35:40
HR SCOTT 2013-06-28 11:35:40
IX SCOTT 2013-06-28 11:35:40
LBACSYS SCOTT 2013-06-28 11:35:40
MDSYS SCOTT 2013-06-28 11:35:40
OE SCOTT 2013-06-28 11:35:40
OJVMSYS SCOTT 2013-06-28 11:35:40
OLAPSYS SCOTT 2013-06-28 11:35:40
ORDDATA SCOTT 2013-06-28 11:35:40
ORDSYS SCOTT 2013-06-28 11:35:40
OUTLN SCOTT 2013-06-28 11:35:40
PM SCOTT 2013-06-28 11:35:40
SCOTT DEPTX SCOTT 2013-06-28 11:35:40
SCOTT ASHDUMP SCOTT 2013-06-28 11:35:40
SCOTT T SCOTT 2013-06-28 11:35:40
SCOTT PARTITIONED SCOTT 2013-06-28 11:35:40
SCOTT T_USERS SCOTT 2013-06-28 11:35:40
SCOTT DEMO SCOTT 2013-06-28 11:35:40
SCOTT NON_PARTITIONED SCOTT 2013-06-28 11:35:40
SCOTT T1 SCOTT 2013-06-28 11:35:40
SCOTT T2 SCOTT 2013-06-28 11:35:40
SCOTT DEMO1 SCOTT 2013-06-28 11:35:40
SCOTT EMPX SCOTT 2013-06-28 11:35:40
SCOTT TX SCOTT 2013-06-28 11:35:40
SCOTT PEOPLE SCOTT 2013-06-28 11:35:40
SCOTT CHAINED_ROWS SCOTT 2013-06-28 11:35:40
SCOTT SAMPLE_PAYMENTS SCOTT 2013-06-28 11:35:40
SCOTT SALGRADE SCOTT 2013-06-28 11:35:40
SCOTT BONUS SCOTT 2013-06-28 11:35:40
SCOTT DEPT SCOTT 2013-06-28 11:35:40
SCOTT EMP SCOTT 2013-06-28 11:35:40
SH SCOTT 2013-06-28 11:35:40
SYS SCOTT 2013-06-28 11:35:40
SYSTEM SCOTT 2013-06-28 11:35:40
TEST1 SCOTT 2013-06-28 11:35:40
TEST2 SCOTT 2013-06-28 11:35:40
WMSYS SCOTT 2013-06-28 11:35:40
XDB SCOTT 2013-06-28 11:35:40
45 rows selected.
--//仔細看發現與以前遇到的右連線不同,它不是全部輸出NULL,而輸出t_tables.owner的值,TABLE_NAME為NULL.
--//真不懂這樣業務會在哪裡使用.
--//測試改成left連線看看.測試OK.
SELECT /*+ use_nl(t_tables t4) */
t_tables.owner
,t_tables.table_name
,t4.username
,t4.created
FROM t_users t4 left OUTER JOIN t_tables PARTITION BY (owner)
ON t_tables.owner = t4.username;
--//如果寫成這樣,取消左(右)連線,:
SELECT /*+ use_nl(t_tables t4) */
t_tables.owner
,t_tables.table_name
,t4.username
,t4.created
FROM t_users t4 JOIN t_tables PARTITION BY (owner)
ON t_tables.owner = t4.username;
--//執行計劃並不會看到NESTED LOOPS PARTITION OUTER(因為沒有出現outer).buffers也不會減少.
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8ffk412btk23u, child number 1
-------------------------------------
SELECT /*+ use_nl(t_tables t4) */ t_tables.owner
,t_tables.table_name ,t4.username ,t4.created FROM
t_users t4 JOIN t_tables PARTITION BY (owner) ON t_tables.owner
= t4.username
Plan hash value: 1212610317
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1254 (100)| | 2408 |00:00:00.02 | 4431 |
| 1 | NESTED LOOPS | | 1 | 2408 | 101K| 1254 (1)| 00:00:01 | 2408 |00:00:00.02 | 4431 |
| 2 | TABLE ACCESS FULL| T_USERS | 1 | 49 | 833 | 3 (0)| 00:00:01 | 49 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T_TABLES | 49 | 49 | 1274 | 26 (4)| 00:00:01 | 2408 |00:00:00.02 | 4423 |
-------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$58A6D7F6
2 - SEL$58A6D7F6 / T4@SEL$1
3 - SEL$58A6D7F6 / T_TABLES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T_TABLES"."OWNER"="T4"."USERNAME")
SCOTT@test01p> @expand_sql_text2.sql 8ffk412btk23u
SELECT "A1"."QCSJ_C000000000300004_3" "OWNER",
"A1"."TABLE_NAME_2" "TABLE_NAME","A1"."USERNAME_0" "USERNAME",
"A1"."CREATED_1" "CREATED"
FROM (SELECT "A3"."USERNAME" "USERNAME_0",
"A3"."CREATED" "CREATED_1","A2"."TABLE_NAME" "TABLE_NAME_2",
"A2"."OWNER" "QCSJ_C000000000300004_3" FROM "SCOTT"."T_USERS" "A3",
"SCOTT"."T_TABLES" "A2" WHERE "A2"."OWNER" = "A3"."USERNAME") "A1"
PL/SQL procedure successfully completed.
--//注:變換的sql語句 PARTITION BY (owner)消失.
--//另外對應存在MERGE JOIN PARTITION OUTER,而不存在HASH JOIN PARTITION OUTER.
--//再做一個例子:
create table t1 as select rownum id ,rownum||'t1' t1name from dual connect by level<=3;
create table t2 as select trunc(rownum/2) id,rownum||'t2' t2name from dual connect by level<=10;
--//分析略.
SELECT /*+ use_nl(t1 t2) */
t1.id t1id,
t1.t1name,
t2.id t2id,
t2.t2name
FROM t1 left OUTER JOIN t2 PARTITION BY (id)
ON t1.id = t2.id;
T1ID T1NAME T2ID T2NAME
---- ------- ---- -------
1 1t1 0
2 2t1 0
3 3t1 0
1 1t1 1 2t2
1 1t1 1 3t2
2 2t1 1
3 3t1 1
1 1t1 2
2 2t1 2 4t2
2 2t1 2 5t2
3 3t1 2
1 1t1 3
2 2t1 3
3 3t1 3 6t2
3 3t1 3 7t2
1 1t1 4
2 2t1 4
3 3t1 4
1 1t1 5
2 2t1 5
3 3t1 5
21 rows selected.
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6jkntv1szw7c3, child number 0
-------------------------------------
SELECT /*+ use_nl(t1 t2) */ t1.id t1id, t1.t1name,
t2.id t2id, t2.t2name FROM t1 left OUTER JOIN t2 PARTITION BY
(id) ON t1.id = t2.id
Plan hash value: 1872405082
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 14 (100)| | 21 |00:00:00.01 | 6 | | | |
| 1 | VIEW | | 1 | 18 | 1296 | 14 (0)| 00:00:01 | 21 |00:00:00.01 | 6 | | | |
| 2 | NESTED LOOPS PARTITION OUTER| | 1 | 18 | 252 | 14 (0)| 00:00:01 | 21 |00:00:00.01 | 6 | | | |
| 3 | BUFFER SORT | | 7 | | | | | 19 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS FULL | T1 | 1 | 3 | 21 | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 3 | | | |
|* 5 | FILTER | | 19 | | | | | 6 |00:00:00.01 | 3 | | | |
| 6 | SORT PARTITION JOIN | | 19 | 2 | 14 | 3 (0)| 00:00:01 | 30 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 2 | 14 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / from$_subquery$_003@SEL$2
2 - SEL$1
4 - SEL$1 / T1@SEL$1
7 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("T1"."ID"="T2"."ID")
SELECT /*+ use_nl(t1 t2) */
t1.id t1id,
t1.t1name,
t2.id t2id,
t2.t2name
FROM t1 PARTITION BY (id) left OUTER JOIN t2 PARTITION BY (id)
ON t1.id = t2.id;
*
ERROR at line 1:
ORA-39751: partitioned table on both sides of PARTITIONED OUTER JOIN is not supported
--//指令碼expand_sql_text2.sql
SET LONG 20000
SET SERVEROUTPUT ON
DECLARE
L_sqltext CLOB := NULL;
l_result CLOB := NULL;
BEGIN
SELECT sql_fulltext
INTO l_sqltext
FROM v$sqlarea
WHERE sql_id = '&&1';
$IF DBMS_DB_VERSION.VER_LE_11_2
$THEN
dbms_sql2.expand_sql_text (l_sqltext, l_result);
$ELSIF DBMS_DB_VERSION.VER_LE_12_1
$THEN
DBMS_UTILITY.expand_sql_text (l_sqltext, l_result);
$ELSIF DBMS_DB_VERSION.VER_LE_12_2
$THEN
DBMS_UTILITY.expand_sql_text (l_sqltext, l_result);
$END
DBMS_OUTPUT.put_line (l_result);
END;
/
SET SERVEROUTPUT OFF
--//昨天看,裡面提到執行計劃NESTED LOOPS(PARTITION OUTER).
--//第36頁:
(5)NESTED LOOPS (PARTITION OUTER)
以左外關聯的左邊資料集(或右外關聯的右邊資料集)為外迴圈，將左外關聯的右邊資料集(或右外關聯的左邊資料集)分組(分割槽)進行外關
聯匹配。
提示：對於分割槽左(右)外關聯，從邏輯上看，左(右)表需要與右(左)表中的資料分組(分割槽)分別做外關聯。如果實際操作也按照
這個邏輯實現，則意味著每次與一組資料進行關聯，都要讀取一次左(右)表資料。而在NESTED LOOPS PARTITION OUTER中，第一次讀取
左(右)表資料後，就被快取在私有記憶體中，從而避免了多次重複讀取共享記憶體資料。
--//注意後面幾句,被快取在私有記憶體中，從而避免了多次重複讀取共享記憶體資料。
--//如果sql語句走nest loops,利用這個特性可以減少邏輯讀,我重複測試書中的例子:
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
create table t_users as select * from dba_users;
create table t_tables as select * from dba_tables;
--//分析略,沒有建立任何索引.
2.測試1:
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SELECT /*+ use_nl(t_tables t4) */ owner,table_name,t4.username,t4.created FROM t_tables PARTITION BY (owner) RIGHT OUTER
JOIN t_users t4 ON t_tables.owner=t4.username;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 31ytmvz0ttfha, child number 0
-------------------------------------
select /*+ use_nl(t_tables t4) */ owner,table_name,t4.username,t4.create
d from t_tables partition by (owner) right outer join t_users t4 on
t_tables.owner=t4.username
Plan hash value: 2189670143
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1281 (100)| | 3704 |00:00:00.08 | 93 | | | |
| 1 | VIEW | | 1 | 2408 | 486K| 1281 (1)| 00:00:01 | 3704 |00:00:00.08 | 93 | | | |
| 2 | NESTED LOOPS PARTITION OUTER| | 1 | 2408 | 101K| 1281 (1)| 00:00:01 | 3704 |00:00:00.08 | 93 | | | |
| 3 | BUFFER SORT | | 28 | | | | | 1324 |00:00:00.01 | 3 | 4096 | 4096 | 4096 (0)|
| 4 | TABLE ACCESS FULL | T_USERS | 1 | 49 | 833 | 3 (0)| 00:00:01 | 49 |00:00:00.01 | 3 | | | |
|* 5 | FILTER | | 1324 | | | | | 2408 |00:00:00.08 | 90 | | | |
| 6 | SORT PARTITION JOIN | | 1324 | 49 | 1274 | 27 (0)| 00:00:01 | 117K|00:00:00.05 | 90 | 133K| 133K| 118K (0)|
| 7 | TABLE ACCESS FULL | T_TABLES | 1 | 49 | 1274 | 26 (4)| 00:00:01 | 2408 |00:00:00.01 | 90 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / from$_subquery$_003@SEL$2
2 - SEL$1
4 - SEL$1 / T4@SEL$1
7 - SEL$1 / T_TABLES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("T_TABLES"."OWNER"="T4"."USERNAME")
SCOTT@test01p> @expand_sql_text2.sql 1sc7wagyhgyay
SELECT "A1"."OWNER_0" "OWNER","A1"."TABLE_NAME_1" "TABLE_NAME","A1"."USERNAME_2" "USERNAME","A1"."CREATED_3" "CREATED"
FROM (SELECT "A2"."OWNER" "OWNER_0","A2"."TABLE_NAME" "TABLE_NAME_1","A3"."USERNAME" "USERNAME_2","A3"."CREATED"
"CREATED_3" FROM "SCOTT"."T_TABLES" "A2" PARTITION BY ( "A2"."OWNER" ) RIGHT OUTER JOIN "SCOTT"."T_USERS" "A3" ON "A2"."OWNER"="A3"."USERNAME") "A1"
PL/SQL procedure successfully completed.
--//注意看buffers=93,你可以發現雖然執行計劃是走NESTED LOOPS.邏輯讀並不是很高.正是利用了"快取在私有記憶體中，從而避免了多次
--//重複讀取共享記憶體資料".當然你看starts列,可以發現執行許多次.這樣會消耗CPU資源,對比下面的A-time就可以發現邏輯讀減少了,
--//A-TIME實際上是增加的.
--//如果刪除partition by (owner)看看:
select /*+ use_nl(t_tables t4) */ owner,table_name,t4.username,t4.created from t_tables right outer join t_users t4 on
t_tables.owner=t4.username;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 02vwt24q957g8, child number 0
-------------------------------------
select /*+ use_nl(t_tables t4) */ owner,table_name,t4.username,t4.create
d from t_tables right outer join t_users t4 on
t_tables.owner=t4.username
Plan hash value: 518654026
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1254 (100)| | 2430 |00:00:00.02 | 4431 |
| 1 | NESTED LOOPS OUTER| | 1 | 2430 | 102K| 1254 (1)| 00:00:01 | 2430 |00:00:00.02 | 4431 |
| 2 | TABLE ACCESS FULL| T_USERS | 1 | 49 | 833 | 3 (0)| 00:00:01 | 49 |00:00:00.01 | 9 |
|* 3 | TABLE ACCESS FULL| T_TABLES | 49 | 49 | 1274 | 26 (4)| 00:00:01 | 2408 |00:00:00.02 | 4422 |
-------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$0E991E55
2 - SEL$0E991E55 / T4@SEL$1
3 - SEL$0E991E55 / T_TABLES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T_TABLES"."OWNER"="T4"."USERNAME")
--//注意看buffers=4431.
--//開始以為2者返回記錄數量一致,仔細看A-rows列,可以發現2者並不等價(指結果集).
--//實際上執行如下:
SELECT /* use_nl(t_tables t4) */ t_tables.owner,t_tables.table_name,t4.username,t4.created
FROM t_tables PARTITION BY (owner) RIGHT OUTER JOIN t_users t4 ON t_tables.owner=t4.username;
--//相當於t_users做驅動表.把t_tables按照owner分割槽,然後個個分割槽再跟t_users每行右連線.輸出太長,我加入一個條件(t4.username='SCOTT');
SELECT /* use_nl(t_tables t4) */
t_tables.owner
,t_tables.table_name
,t4.username
,t4.created
FROM t_tables PARTITION BY (owner)
RIGHT OUTER JOIN t_users t4 ON t_tables.owner = t4.username
WHERE t4.username = 'SCOTT';
OWNER TABLE_NAME USERNAME CREATED
-------------------- --------------- -------- -------------------
APEX_040200 SCOTT 2013-06-28 11:35:40
APPQOSSYS SCOTT 2013-06-28 11:35:40
AUDSYS SCOTT 2013-06-28 11:35:40
CTXSYS SCOTT 2013-06-28 11:35:40
DBSNMP SCOTT 2013-06-28 11:35:40
DVSYS SCOTT 2013-06-28 11:35:40
FLOWS_FILES SCOTT 2013-06-28 11:35:40
GSMADMIN_INTERNAL SCOTT 2013-06-28 11:35:40
HR SCOTT 2013-06-28 11:35:40
IX SCOTT 2013-06-28 11:35:40
LBACSYS SCOTT 2013-06-28 11:35:40
MDSYS SCOTT 2013-06-28 11:35:40
OE SCOTT 2013-06-28 11:35:40
OJVMSYS SCOTT 2013-06-28 11:35:40
OLAPSYS SCOTT 2013-06-28 11:35:40
ORDDATA SCOTT 2013-06-28 11:35:40
ORDSYS SCOTT 2013-06-28 11:35:40
OUTLN SCOTT 2013-06-28 11:35:40
PM SCOTT 2013-06-28 11:35:40
SCOTT DEPTX SCOTT 2013-06-28 11:35:40
SCOTT ASHDUMP SCOTT 2013-06-28 11:35:40
SCOTT T SCOTT 2013-06-28 11:35:40
SCOTT PARTITIONED SCOTT 2013-06-28 11:35:40
SCOTT T_USERS SCOTT 2013-06-28 11:35:40
SCOTT DEMO SCOTT 2013-06-28 11:35:40
SCOTT NON_PARTITIONED SCOTT 2013-06-28 11:35:40
SCOTT T1 SCOTT 2013-06-28 11:35:40
SCOTT T2 SCOTT 2013-06-28 11:35:40
SCOTT DEMO1 SCOTT 2013-06-28 11:35:40
SCOTT EMPX SCOTT 2013-06-28 11:35:40
SCOTT TX SCOTT 2013-06-28 11:35:40
SCOTT PEOPLE SCOTT 2013-06-28 11:35:40
SCOTT CHAINED_ROWS SCOTT 2013-06-28 11:35:40
SCOTT SAMPLE_PAYMENTS SCOTT 2013-06-28 11:35:40
SCOTT SALGRADE SCOTT 2013-06-28 11:35:40
SCOTT BONUS SCOTT 2013-06-28 11:35:40
SCOTT DEPT SCOTT 2013-06-28 11:35:40
SCOTT EMP SCOTT 2013-06-28 11:35:40
SH SCOTT 2013-06-28 11:35:40
SYS SCOTT 2013-06-28 11:35:40
SYSTEM SCOTT 2013-06-28 11:35:40
TEST1 SCOTT 2013-06-28 11:35:40
TEST2 SCOTT 2013-06-28 11:35:40
WMSYS SCOTT 2013-06-28 11:35:40
XDB SCOTT 2013-06-28 11:35:40
45 rows selected.
--//仔細看發現與以前遇到的右連線不同,它不是全部輸出NULL,而輸出t_tables.owner的值,TABLE_NAME為NULL.
--//真不懂這樣業務會在哪裡使用.
--//測試改成left連線看看.測試OK.
SELECT /*+ use_nl(t_tables t4) */
t_tables.owner
,t_tables.table_name
,t4.username
,t4.created
FROM t_users t4 left OUTER JOIN t_tables PARTITION BY (owner)
ON t_tables.owner = t4.username;
--//如果寫成這樣,取消左(右)連線,:
SELECT /*+ use_nl(t_tables t4) */
t_tables.owner
,t_tables.table_name
,t4.username
,t4.created
FROM t_users t4 JOIN t_tables PARTITION BY (owner)
ON t_tables.owner = t4.username;
--//執行計劃並不會看到NESTED LOOPS PARTITION OUTER(因為沒有出現outer).buffers也不會減少.
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8ffk412btk23u, child number 1
-------------------------------------
SELECT /*+ use_nl(t_tables t4) */ t_tables.owner
,t_tables.table_name ,t4.username ,t4.created FROM
t_users t4 JOIN t_tables PARTITION BY (owner) ON t_tables.owner
= t4.username
Plan hash value: 1212610317
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1254 (100)| | 2408 |00:00:00.02 | 4431 |
| 1 | NESTED LOOPS | | 1 | 2408 | 101K| 1254 (1)| 00:00:01 | 2408 |00:00:00.02 | 4431 |
| 2 | TABLE ACCESS FULL| T_USERS | 1 | 49 | 833 | 3 (0)| 00:00:01 | 49 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T_TABLES | 49 | 49 | 1274 | 26 (4)| 00:00:01 | 2408 |00:00:00.02 | 4423 |
-------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$58A6D7F6
2 - SEL$58A6D7F6 / T4@SEL$1
3 - SEL$58A6D7F6 / T_TABLES@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T_TABLES"."OWNER"="T4"."USERNAME")
SCOTT@test01p> @expand_sql_text2.sql 8ffk412btk23u
SELECT "A1"."QCSJ_C000000000300004_3" "OWNER",
"A1"."TABLE_NAME_2" "TABLE_NAME","A1"."USERNAME_0" "USERNAME",
"A1"."CREATED_1" "CREATED"
FROM (SELECT "A3"."USERNAME" "USERNAME_0",
"A3"."CREATED" "CREATED_1","A2"."TABLE_NAME" "TABLE_NAME_2",
"A2"."OWNER" "QCSJ_C000000000300004_3" FROM "SCOTT"."T_USERS" "A3",
"SCOTT"."T_TABLES" "A2" WHERE "A2"."OWNER" = "A3"."USERNAME") "A1"
PL/SQL procedure successfully completed.
--//注:變換的sql語句 PARTITION BY (owner)消失.
--//另外對應存在MERGE JOIN PARTITION OUTER,而不存在HASH JOIN PARTITION OUTER.
--//再做一個例子:
create table t1 as select rownum id ,rownum||'t1' t1name from dual connect by level<=3;
create table t2 as select trunc(rownum/2) id,rownum||'t2' t2name from dual connect by level<=10;
--//分析略.
SELECT /*+ use_nl(t1 t2) */
t1.id t1id,
t1.t1name,
t2.id t2id,
t2.t2name
FROM t1 left OUTER JOIN t2 PARTITION BY (id)
ON t1.id = t2.id;
T1ID T1NAME T2ID T2NAME
---- ------- ---- -------
1 1t1 0
2 2t1 0
3 3t1 0
1 1t1 1 2t2
1 1t1 1 3t2
2 2t1 1
3 3t1 1
1 1t1 2
2 2t1 2 4t2
2 2t1 2 5t2
3 3t1 2
1 1t1 3
2 2t1 3
3 3t1 3 6t2
3 3t1 3 7t2
1 1t1 4
2 2t1 4
3 3t1 4
1 1t1 5
2 2t1 5
3 3t1 5
21 rows selected.
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6jkntv1szw7c3, child number 0
-------------------------------------
SELECT /*+ use_nl(t1 t2) */ t1.id t1id, t1.t1name,
t2.id t2id, t2.t2name FROM t1 left OUTER JOIN t2 PARTITION BY
(id) ON t1.id = t2.id
Plan hash value: 1872405082
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 14 (100)| | 21 |00:00:00.01 | 6 | | | |
| 1 | VIEW | | 1 | 18 | 1296 | 14 (0)| 00:00:01 | 21 |00:00:00.01 | 6 | | | |
| 2 | NESTED LOOPS PARTITION OUTER| | 1 | 18 | 252 | 14 (0)| 00:00:01 | 21 |00:00:00.01 | 6 | | | |
| 3 | BUFFER SORT | | 7 | | | | | 19 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS FULL | T1 | 1 | 3 | 21 | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 3 | | | |
|* 5 | FILTER | | 19 | | | | | 6 |00:00:00.01 | 3 | | | |
| 6 | SORT PARTITION JOIN | | 19 | 2 | 14 | 3 (0)| 00:00:01 | 30 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 2 | 14 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / from$_subquery$_003@SEL$2
2 - SEL$1
4 - SEL$1 / T1@SEL$1
7 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("T1"."ID"="T2"."ID")
SELECT /*+ use_nl(t1 t2) */
t1.id t1id,
t1.t1name,
t2.id t2id,
t2.t2name
FROM t1 PARTITION BY (id) left OUTER JOIN t2 PARTITION BY (id)
ON t1.id = t2.id;
*
ERROR at line 1:
ORA-39751: partitioned table on both sides of PARTITIONED OUTER JOIN is not supported
--//指令碼expand_sql_text2.sql
SET LONG 20000
SET SERVEROUTPUT ON
DECLARE
L_sqltext CLOB := NULL;
l_result CLOB := NULL;
BEGIN
SELECT sql_fulltext
INTO l_sqltext
FROM v$sqlarea
WHERE sql_id = '&&1';
$IF DBMS_DB_VERSION.VER_LE_11_2
$THEN
dbms_sql2.expand_sql_text (l_sqltext, l_result);
$ELSIF DBMS_DB_VERSION.VER_LE_12_1
$THEN
DBMS_UTILITY.expand_sql_text (l_sqltext, l_result);
$ELSIF DBMS_DB_VERSION.VER_LE_12_2
$THEN
DBMS_UTILITY.expand_sql_text (l_sqltext, l_result);
$END
DBMS_OUTPUT.put_line (l_result);
END;
/
SET SERVEROUTPUT OFF
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2145710/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【sql調優之執行計劃】nested loops join and nested loop join outerSQLOOP
- NESTED LOOPS 成本計算OOP
- nested loops 和hash join的一點測試OOP
- 關於迴圈巢狀nested loops的一點分析巢狀OOP
- 統計資訊過期導致SQL進行NESTED LOOPS查詢緩慢SQLOOP
- Nested loops、Hash join、Sort merge join（三種連線型別原理、使用要點）OOP型別
- 【TUNE_ORACLE】列出NL（NESTED LOOPS）被驅動表走了全表掃描的SQL參考OracleOOPSQL
- 【TUNE_ORACLE】列出返回行數較多的巢狀迴圈（NESTED LOOPS）SQL的SQL參考Oracle巢狀OOPSQL
- 微課sql最佳化(14)、表的連線方法(3)-關於Nested Loops Join(巢狀迴圈)SQLOOP巢狀
- 使用Oracle 10g引入的Partition Outer Joins進行統計報表的實現Oracle 10g
- React Loops 嚐鮮ReactOOP
- HDU 3853 LOOPS(概率dp)OOP
- partition table and partition indexIndex
- PARTITION partition01
- PARTITION partition02
- PARTITION partition04
- nested loop心得OOP
- [20130513]Interval Partition的一些問題.txt
- Pruning、Reference Partition、Exchange Partition
- partition timestamp(0) not use partition
- 外連線(outer join)示例
- [20191203]enq: ZA - add std audit table partition.txtENQ
- jQuery之height()、innerHeight()、outerjQuery
- 理解full outer jion，union，union all
- outer join新舊語法分析語法分析
- ElasticSearch多層nested查詢、nested過濾排除非結果內容Elasticsearch
- HDU 3853 LOOPS：期望dp【網格型】OOP
- JS閉包文章--(翻譯)Callbacks in LoopsJSOOP
- PARTITION SPILT
- hive partitionHive
- over (partition by)
- exchange partition
- oracle partitionOracle
- split partition
- Clique Partition
- [20131125]Partition, compress and drop column (ORA-39726).txt
- Partition Pruning和Partition-Wise Joins
- 資料庫表--nested table資料庫