[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