UPDATE ATAB SET ACOL =
( SELECT TO_DATE(MAX(BCOL), 'YYYY-MM-DD') + 2 FROM BTAB WHERE BCOL2 = 'XX' ) WHERE BCOL2 = 'XX'
最好的办法是B表上 BCOL2,BCOL 建index,但是这个一次性的SQL没太必要。
那么就可以考虑并行。但是ATAB 非常小,改动的记录也就5行
这是如果PDML ,就会A表启动并行(实际session 也没见到),B表并行取消了。
如果没有PDML,A表不并行,B表并行,速度非常快。
并行看着开了,实际没有作用!!!! 也没看到并行进程
UPDATE /*+ ENABLE_PARALLEL_DML PARALLEL(8)*/ ATAB SET ACOL =
( SELECT /*+ parallel (16)*/ TO_DATE(MAX(BCOL), 'YYYY-MM-DD') + 2 FROM BTAB WHERE BCOL2 = 'XX' ) WHERE BCOL2 = 'XX'
Plan Hash Value : 256384155
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 5 | 55 | 1423031 | 00:00:56 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 5 | 55 | 38 | 00:00:01 |
| 3 | INDEX MAINTENANCE | ATAB | | | | |
| 4 | PX RECEIVE | | 5 | 55 | 38 | 00:00:01 |
| 5 | PX SEND RANGE | :TQ10000 | 5 | 55 | 38 | 00:00:01 |
| 6 | UPDATE | ATAB | | | | |
| 7 | PX BLOCK ITERATOR | | 5 | 55 | 38 | 00:00:01 |
| 8 | TABLE ACCESS FULL | ATAB | 5 | 55 | 38 | 00:00:01 |
| 9 | SORT AGGREGATE | | 1 | 14 | | |
| 10 | PARTITION LIST SINGLE | | 1224937726 | 17149128164 | 1422992 | 00:00:56 |
| 11 | TABLE ACCESS FULL | BTAB | 1224937726 | 17149128164 | 1422992 | 00:00:56 |
----------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
-------------------------------------------------------------------------------
0 - STATEMENT
U - parallel (16) / hint overridden by another in parent query block
Notes
-----
- dop_reason = hint
- dop = 8
- px_in_memory_imc = no
- px_in_memory = no
左上角不会显示并行情况,只有sql monitor中才显示
UPDATE /*x+ ENABLE_PARALLEL_DML PARALLEL(8)*/ ATAB SET ACOL =
( SELECT /*+ parallel (16)*/ TO_DATE(MAX(BCOL), 'YYYY-MM-DD') + 2 FROM BTAB WHERE BCOL2 = 'XX' ) WHERE BCOL2 = 'XX'
Plan Hash Value : 604123922
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 5 | 55 | 711516 | 00:00:28 |
| 1 | UPDATE | ATAB | | | | |
| 2 | PX COORDINATOR | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ20000 | 5 | 55 | 19 | 00:00:01 |
| 4 | PX BLOCK ITERATOR | | 5 | 55 | 19 | 00:00:01 |
| 5 | TABLE ACCESS FULL | ATAB | 5 | 55 | 19 | 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 14 | | |
| 7 | PX COORDINATOR | | | | | |
| 8 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 14 | | |
| 9 | SORT AGGREGATE | | 1 | 14 | | |
| 10 | PX BLOCK ITERATOR | | 1224937726 | 17149128164 | 711496 | 00:00:28 |
| 11 | TABLE ACCESS FULL | BTAB | 1224937726 | 17149128164 | 711496 | 00:00:28 |
----------------------------------------------------------------------------------------------------
Notes
-----
- dop_reason = hint
- dop = 16
- px_in_memory_imc = no
- px_in_memory = no
- pdml_reason = PDML is disabled in current session