union all 以及标量子查询执行计划

SELECT 1, (SELECT ID1 FROM TE WHERE ID=A.ID2)
FROM .TA A
WHERE COLA= 'X' UNION ALL

SELECT 1, (SELECT ID2 FROM TD WHERE ID=A.ID1)
FROM .TB A
WHERE COLA= 'X' UNION ALL

SELECT 1,COL2 AS PARENT_UUID FROM .TC a
WHERE COLA= 'X'

三个union all 看着像是5个table join，其实有两个是子查询，要反着看 2 3 4 5列的执行计划。

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 174 | 11 | 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | TE | 1 | 34 | 3 | 00:00:01 |
| * 3 | INDEX RANGE SCAN | TE_PK | 1 | | 2 | 00:00:01 |
| 4 | PARTITION LIST SINGLE | | 1 | 29 | 2 | 00:00:01 |
| 5 | TABLE ACCESS FULL | TA | 1 | 29 | 2 | 00:00:01 |
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | TD | 1 | 34 | 2 | 00:00:01 |
| * 7 | INDEX RANGE SCAN | TD_PK | 1 | | 1 | 00:00:01 |
| 8 | PARTITION LIST SINGLE | | 1 | 29 | 2 | 00:00:01 |
| 9 | TABLE ACCESS FULL | TB | 1 | 29 | 2 | 00:00:01 |
| 10 | PARTITION LIST SINGLE | | 1 | 29 | 2 | 00:00:01 |
| 11 | TABLE ACCESS FULL | TC | 1 | 29 | 2 | 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("ID"=:B1)
* 7 - access("ID"=:B1)