oracle標量子查詢簡介和表連接改寫

Oracle允許在select子句中包含單行子查詢，這個也就是oracle的標量子查詢，標量子查詢有點類似于外連接，當使用到外連接時我們可以靈活的將其轉化為標量子查詢。

SQL> create table t1 as select * from all_users;

Table created.

SQL> create table t2 as select * from all_objects;

Table created.

SQL> select a.object_id,(select b.username from t1 b where a.owner=b.username) f
rom t2 a;

49812 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1364172329

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53276 | 1560K| 152 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 17 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 53276 | 1560K| 152 (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("B"."USERNAME"=:B1)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
4282 consistent gets
0 physical reads
0 redo size
1176699 bytes sent via SQL*Net to client
37012 bytes received via SQL*Net from client
3322 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49812 rows processed

標量子查詢其實還是一個子查詢，那么它究竟是如何查詢的：首先走的是外部的查詢，比如上一個sql語句執行計劃，先全表掃描的T2 a，然后取T2 a表的每一行數據就去和T1 b去過濾，過濾條件是a.owner=b.username，如果符合則返回子查詢的值，如果不符合則用null補充。當然這個時候還有個類似的filter去重的運算，對于t2 a中重復的數據行不用再去和t1 b去過濾。

而上面這個標量子查詢的sql語句其實是等價于下面外連接sql語句的：
SQL> select a.object_id,b.username from t2 a,t1 b
2 where a.owner=b.username(+) ;

49812 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 535089106

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53276 | 2445K| 155 (2)| 00:00:02 |
|* 1 | HASH JOIN RIGHT OUTER| | 53276 | 2445K| 155 (2)| 00:00:02 |
| 2 | TABLE ACCESS FULL | T1 | 23 | 391 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 53276 | 1560K| 152 (1)| 00:00:02 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."OWNER"="B"."USERNAME"(+))

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
4043 consistent gets
0 physical reads
0 redo size
1176659 bytes sent via SQL*Net to client
37012 bytes received via SQL*Net from client
3322 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
49812 rows processed

而如果標量子查詢中如果主查詢的一行對應子查詢返回有多個值，這個是不允許的
SQL> select a.username,b.object_id from t1 a,t2 b
2 where a.username=b.owner(+);

29742 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1823443478

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53276 | 2445K| 155 (2)| 00:00:02 |
|* 1 | HASH JOIN OUTER | | 53276 | 2445K| 155 (2)| 00:00:02 |
| 2 | TABLE ACCESS FULL| T1 | 23 | 391 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 53276 | 1560K| 152 (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."USERNAME"="B"."OWNER"(+))

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
2726 consistent gets
0 physical reads
0 redo size
654542 bytes sent via SQL*Net to client
22294 bytes received via SQL*Net from client
1984 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
29742 rows processed

SQL> select a.username,(select b.object_id from t2 b where a.username=b.owner) f
rom t1 a;
select a.username,(select b.object_id from t2 b where a.username=b.owner) from t
1 a
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

這里由于a.username=b.owner，其中b.owner有多個相同的值，所以這里返回的b.object_id可能有多個值，這里就出現上述的ora-01427錯誤。

標量子查詢中也可以有聚合函數的出現：
SQL> set autotrace traceonly;
SQL> select a.username,max(b.object_id) from t1 a,t2 b
2 where a.username=b.owner(+)
3 group by a.username;

23 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 577572187

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53276 | 2445K| 159 (4)| 00:00:02 |
| 1 | HASH GROUP BY | | 53276 | 2445K| 159 (4)| 00:00:02 |
|* 2 | HASH JOIN OUTER | | 53276 | 2445K| 155 (2)| 00:00:02 |
| 3 | TABLE ACCESS FULL| T1 | 23 | 391 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 53276 | 1560K| 152 (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"."USERNAME"="B"."OWNER"(+))

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
92 recursive calls
0 db block gets
777 consistent gets
685 physical reads
0 redo size
1169 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
23 rows processed

SQL> select a.username,(select max(b.object_id) from t2 b where b.owner=a.userna
me) from t1 a;

23 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 367820

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 391 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| T2 | 533 | 15990 | 152 (1)| 00:00:02 |
| 3 | TABLE ACCESS FULL | T1 | 23 | 391 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("B"."OWNER"=:B1)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
15929 consistent gets
0 physical reads
0 redo size
1206 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23 rows processed

但是我們注意到上述標量子查詢卻存在一個問題，就是無法將子查詢展開為表連接，換句話說無法采用靈活的hash join outer的關聯方式。

關于標量子查詢和表關聯的性能簡介：
如果主查詢返回的數據較多，而子查詢中又沒有高效的索引，關聯列對應的主查詢表又沒有較多的重復值，那么這個標量子查詢的執行成本是很大的,如上面的標量子查詢和外連接的sql語句中可以看出外連接IO成本要明顯小于標量子查詢。

但是標量子查詢oracle內部確是有優化的，優化器cache了中間的結果,如果結果集不大，子查詢中又有高效的索引，那么這個標量子查詢可能會比常規的表關聯更加高效。

小魚列出幾種常會涉及到的標量子查詢和表連接的sql改寫：
1 最簡單的標量子查詢
table :a(a1,a2),b(a1,b2)
select a2,(select b2 from b where b.a1=a.a1) from a

表連接：
select a2,b2 from a,b where a.a1=b.a1(+);

2 子查詢中限制返回一行數據
table :a(a1,a2),b(a1,b2)
select a2,(select b2 from b where b.a1=a.a1 and rownum=1) from a

表連接：
SELECT a2, c.b2
FROM a,
(SELECT b2, ROW_NUMBER () OVER (PARTITION BY a1 ORDER BY a1) cn
FROM b
WHERE cn = 1) c
WHERE a.a1 = c.b2(+);

3 子查詢帶有聚合函數
table :a(a1,a2),b(a1,b2)
select a2,(select sum(b2) from b where b.a1 = a.a1) from a

表連接1：
SELECT a2, bb.sum_value
FROM a,
( SELECT SUM (b2) sum_value, a1
FROM b
GROUP BY a1) bb
WHERE a.a1 = b.a1(+);
表連接2：
SELECT a2, SUM (b2)
FROM a, b
WHERE a.a1 = b.a1(+)
GROUP BY a2;

4 查詢中包括好幾個標量子查詢
table :a(a1,a2),b(a1,b2),c(a1,b2,c2)
SELECT a.a2,
(SELECT c2
FROM b, c
WHERE b.a1 = a.a1 AND b.b2 = c.b2 AND ROWNUM = 1),
(SELECT b2
FROM b
WHERE b.a1 = a.a1 AND ROWNUM = 1),
(SELECT c2
FROM c
WHERE c.a1 = a.a1 AND ROWNUM = 1)
FROM a

表連接：
SELECT a.a2,
bb.c2,
cc.b2,
dd.c2
FROM (SELECT c2,
b.a1,
ROW_NUMBER () OVER (PARTITION BY b.a1 ORDER BY b.a1) cnt
FROM b, c
WHERE b.b2 = c.b2 AND cnt = 1) bb,
(SELECT b2, ROW_NUMBER () OVER (PARTITION BY a1 ORDER BY a1) cnt
FROM b
WHERE cnt = 1) cc,
(SELECT c2, ROW_NUMBER () OVER (PARTITION BY a1 ORDER BY a1) cnt
FROM c
WHERE cnt = 1) dd,
a
WHERE a.a1 = bb.a1(+) AND a.a1 = cc.a1(+) AND a.a1 = dd.a1(+);