[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

---------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------------------------

| 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