NATIVE_FULL_OUTER_JOIN
来源：互联网 发布：九阴真经安卓版免网络 编辑：程序博客网 时间：2024/05/16 07:20
一网友问一sql的优化，语句就不贴了，噼里啪啦一大堆，主要结构是select * from (里面一大堆) a full join ()。看plan就可以了
--------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |--------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 30M| 26G| | 8761K (2)| 03:28:58 | | || 1 | VIEW | | 30M| 26G| | 8761K (2)| 03:28:58 | | || 2 | UNION-ALL | | | | | | | | ||* 3 | HASH JOIN OUTER | | 109K| 106M| 52M| 4491K (2)| 01:47:08 | | || 4 | MERGE JOIN | | 109K| 50M| | 285K (2)| 00:06:50 | | || 5 | SORT JOIN | | 4084K| 1546M| | 285K (2)| 00:06:50 | | || 6 | VIEW | | 4084K| 1546M| | 285K (2)| 00:06:50 | | || 7 | HASH GROUP BY | | 4084K| 494M| 602M| 285K (2)| 00:06:50 | | ||* 8 | HASH JOIN | | 4084K| 494M| | 168K (2)| 00:04:01 | | ||* 9 | TABLE ACCESS FULL | T_xxxx_OUT | 9815 | 335K| | 226 (2)| 00:00:01 | | ||* 10 | TABLE ACCESS FULL | T_xxxx_OUT_LINE | 4084K| 358M| | 167K (2)| 00:04:01 | | ||* 11 | SORT JOIN | | 10 | 920 | | 8 (13)| 00:00:01 | | || 12 | VIEW | | 10 | 920 | | 7 (0)| 00:00:01 | | ||* 13 | FILTER | | | | | | | | ||* 14 | CONNECT BY WITH FILTERING | | | | | | | | ||* 15 | TABLE ACCESS FULL | T_*****_ORGA | 36 | 972 | | 8 (0)| 00:00:01 | | || 16 | NESTED LOOPS | | | | | | | | || 17 | CONNECT BY PUMP | | | | | | | | || 18 | TABLE ACCESS BY INDEX ROWID | T_*****_ORGA | 10 | 180 | | 7 (0)| 00:00:01 | | ||* 19 | INDEX RANGE SCAN | IDX_*****_ORGA_PARENT_ID | 10 | | | 1 (0)| 00:00:01 | | || 20 | VIEW | | 30M| 14G| | 3425K (2)| 01:21:43 | | || 21 | HASH GROUP BY | | 30M| 5094M| 5452M| 3425K (2)| 01:21:43 | | ||* 22 | HASH JOIN | | 30M| 5094M| | 2242K (2)| 00:53:29 | | || 23 | TABLE ACCESS FULL | T_-------- | 37788 | 332K| | 860 (3)| 00:00:02 | | ||* 24 | HASH JOIN | | 29M| 4787M| 306M| 2240K (2)| 00:53:27 | | ||* 25 | TABLE ACCESS FULL | T_TERMINAL_*****_ORDER | 8230K| 211M| | 117K (2)| 00:02:48 | | || 26 | PARTITION RANGE ALL | | 30M| 4173M| | 1880K (2)| 00:44:52 | 1 | 58 ||* 27 | TABLE ACCESS FULL | T_---_LINE | 30M| 4173M| | 1880K (2)| 00:44:52 | 1 | 58 || 28 | HASH GROUP BY | | 29M| 6463M| 6891M| 4269K (2)| 01:41:50 | | ||* 29 | HASH JOIN | | 29M| 6463M| | 2785K (2)| 01:06:26 | | || 30 | TABLE ACCESS FULL | T_-------- | 37788 | 332K| | 860 (3)| 00:00:02 | | ||* 31 | HASH JOIN RIGHT ANTI | | 29M| 6143M| 6400K| 2783K (2)| 01:06:24 | | || 32 | VIEW | VW_SQ_1 | 109K| 5119K| | 285K (2)| 00:06:50 | | || 33 | MERGE JOIN | | 109K| 9172K| | 285K (2)| 00:06:50 | | || 34 | SORT JOIN | | 4084K| 284M| | 285K (2)| 00:06:50 | | || 35 | VIEW | | 4084K| 284M| | 285K (2)| 00:06:50 | | || 36 | HASH GROUP BY | | 4084K| 494M| 602M| 285K (2)| 00:06:50 | | ||* 37 | HASH JOIN | | 4084K| 494M| | 168K (2)| 00:04:01 | | ||* 38 | TABLE ACCESS FULL | T_xxxx_OUT | 9815 | 335K| | 226 (2)| 00:00:01 | | ||* 39 | TABLE ACCESS FULL | T_xxxx_OUT_LINE | 4084K| 358M| | 167K (2)| 00:04:01 | | ||* 40 | SORT JOIN | | 10 | 130 | | 8 (13)| 00:00:01 | | || 41 | VIEW | | 10 | 130 | | 7 (0)| 00:00:01 | | ||* 42 | FILTER | | | | | | | | ||* 43 | CONNECT BY WITH FILTERING | | | | | | | | ||* 44 | TABLE ACCESS FULL | T_*****_ORGA | 36 | 972 | | 8 (0)| 00:00:01 | | || 45 | NESTED LOOPS | | | | | | | | || 46 | CONNECT BY PUMP | | | | | | | | || 47 | TABLE ACCESS BY INDEX ROWID| T_*****_ORGA | 10 | 180 | | 7 (0)| 00:00:01 | | ||* 48 | INDEX RANGE SCAN | IDX_*****_ORGA_PARENT_ID | 10 | | | 1 (0)| 00:00:01 | | ||* 49 | HASH JOIN | | 29M| 4787M| 306M| 2240K (2)| 00:53:27 | | ||* 50 | TABLE ACCESS FULL | T_TERMINAL_*****_ORDER | 8230K| 211M| | 117K (2)| 00:02:48 | | || 51 | PARTITION RANGE ALL | | 30M| 4173M| | 1880K (2)| 00:44:52 | 1 | 58 ||* 52 | TABLE ACCESS FULL | T_---_LINE | 30M| 4173M| | 1880K (2)| 00:44:52 | 1 | 58 |--------------------------------------------------------------------------------------------------------------------------------------------
cbo把full join 转为了union all。30M 的行数，两次，坑啊。哥找了好大一会儿，找到了hint NATIVE_FULL_OUTER_JOIN，报着试试看的心情让网友加上 /*+ NATIVE_FULL_OUTER_JOIN */
新的plan如下:
-----------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 30M| 26G| | 4397K (2)| 01:44:54 | | || 1 | VIEW | VW_FOJ_0 | 30M| 26G| | 4397K (2)| 01:44:54 | | ||* 2 | HASH JOIN FULL OUTER | | 30M| 26G| 50M| 4397K (2)| 01:44:54 | | || 3 | VIEW | | 109K| 49M| | 285K (2)| 00:06:50 | | || 4 | MERGE JOIN | | 109K| 50M| | 285K (2)| 00:06:50 | | || 5 | SORT JOIN | | 4084K| 1546M| | 285K (2)| 00:06:50 | | || 6 | VIEW | | 4084K| 1546M| | 285K (2)| 00:06:50 | | || 7 | HASH GROUP BY | | 4084K| 494M| 602M| 285K (2)| 00:06:50 | | ||* 8 | HASH JOIN | | 4084K| 494M| | 168K (2)| 00:04:01 | | ||* 9 | TABLE ACCESS FULL | T_xxxx_OUT | 9815 | 335K| | 226 (2)| 00:00:01 | | ||* 10 | TABLE ACCESS FULL | T_xxxx_OUT_LINE | 4084K| 358M| | 167K (2)| 00:04:01 | | ||* 11 | SORT JOIN | | 10 | 920 | | 8 (13)| 00:00:01 | | || 12 | VIEW | | 10 | 920 | | 7 (0)| 00:00:01 | | ||* 13 | FILTER | | | | | | | | ||* 14 | CONNECT BY WITH FILTERING | | | | | | | | ||* 15 | TABLE ACCESS FULL | T_*****_ORGA | 36 | 972 | | 8 (0)| 00:00:01 | | || 16 | NESTED LOOPS | | | | | | | | || 17 | CONNECT BY PUMP | | | | | | | | || 18 | TABLE ACCESS BY INDEX ROWID| T_*****_ORGA | 10 | 180 | | 7 (0)| 00:00:01 | | ||* 19 | INDEX RANGE SCAN | IDX_*****_ORGA_PARENT_ID | 10 | | | 1 (0)| 00:00:01 | | || 20 | VIEW | | 30M| 13G| | 3425K (2)| 01:21:43 | | || 21 | HASH GROUP BY | | 30M| 5094M| 5452M| 3425K (2)| 01:21:43 | | ||* 22 | HASH JOIN | | 30M| 5094M| | 2242K (2)| 00:53:29 | | || 23 | TABLE ACCESS FULL | T_-------- | 37788 | 332K| | 860 (3)| 00:00:02 | | ||* 24 | HASH JOIN | | 29M| 4787M| 306M| 2240K (2)| 00:53:27 | | ||* 25 | TABLE ACCESS FULL | T_TERMINAL_*****_ORDER | 8230K| 211M| | 117K (2)| 00:02:48 | | || 26 | PARTITION RANGE ALL | | 30M| 4173M| | 1880K (2)| 00:44:52 | 1 | 58 ||* 27 | TABLE ACCESS FULL | T_---_LINE | 30M| 4173M| | 1880K (2)| 00:44:52 | 1 | 58 |-----------------------------------------------------------------------------------------------------------------------------------------
ok，让网友试运行了下，时间从25分钟降到了15分钟。
在群里提起时，棉花糖大师动作那叫一个快，马上给出一段
When you ask Oracle to use native full join implementation either with parameter _optimizer_native_full_outer_join equals to force (which is mentioned here as an enhancement of 10.2.0.4) or a hint NATIVE_FULL_OUTER_JOIN,
后面再优化，应该就是调pga了，那是后话不提
