回答你這個問題, 這是最快方式:
SELECT DATA_ITEM,
VDEF2,
VDEF3,
VDEF5,
VDEF6
FROM (SELECT A1.VDEF1 AS DATA_ITEM,
A1.VDEF2,
A1.VDEF3,
A2.VDEF5,
A2.VDEF6
FROM A1, A2
WHERE A1.VDEF1 = A2.VDEF4(+)
AND A1.VDEF2 = A2.VDEF5(+)
AND A1.VDEF3 = A2.VDEF6(+)
UNION
SELECT A2.VDEF4 AS DATA_ITEM,
A1.VDEF2,
A1.VDEF3,
A2.VDEF5,
A2.VDEF6
FROM A1, A2
WHERE A1.VDEF1(+) = A2.VDEF4
AND A1.VDEF2(+) = A2.VDEF5
AND A1.VDEF3(+) = A2.VDEF6)
ORDER BY DATA_ITEM, NVL (VDEF2, VDEF5), NVL (VDEF3, VDEF6);
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | SORT ORDER BY | | 14 | 756 | 9 (34)| 00:00:01 |
| 2 | VIEW | | 14 | 756 | 8 (25)| 00:00:01 |
| 3 | SORT UNIQUE | | 14 | 784 | 8 (63)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | NESTED LOOPS OUTER| | 6 | 336 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| A1 | 6 | 168 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | A2_I01 | 1 | 28 | 0 (0)| |
| 8 | NESTED LOOPS OUTER| | 8 | 448 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL| A2 | 8 | 224 | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | A1_I01 | 1 | 28 | 0 (0)| |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("A1"."VDEF1"="A2"."VDEF4" AND "A1"."VDEF2"="A2"."VDEF5"
AND "A1"."VDEF3"="A2"."VDEF6")
10 - access("A1"."VDEF1"="A2"."VDEF4" AND "A1"."VDEF2"="A2"."VDEF5"
AND "A1"."VDEF3"="A2"."VDEF6")
Note
-----
- dynamic sampling used for this statement (level=2)
AA_20141120_C.jpg (13.73 KB, 下载次数: 1)
2014-11-20 02:36 上传