欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

oracle标量子查询简介和表连接改写

程序员文章站 2022-06-08 14:47:21
...

之前小鱼就听过了标量子查询,不过对于其中的细节理解还是远远不够,借助一部分资料和自己测试对标量子查询做一点简单的分析和介绍。 Oracle允许在select子句中包含单行子查询,这个也就是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(+);

SELECT a2,
(SELECT SUM (c2)
FROM b, c
WHERE b.a1 = a.a1 AND b.b2 = c.b2),
(SELECT SUM (b2)
FROM b
WHERE b.a1 = a.a1),
(SELECT SUM (c2)
FROM c
WHERE c.a1 = a.a1)
FROM a
表连接:
SELECT a2,
bb.sum1,
cc.sum2,
dd.sum3
FROM ( SELECT SUM (c2) sum1, b.a1
FROM b, c
WHERE b.b2 = c.b2
GROUP BY b.a1) bb,
( SELECT SUM (b2) sum2, a1
FROM b
GROUP BY a1) cc,
( SELECT SUM (c2) sum3, a1
FROM c
GROUP BY a1) dd,
a
WHERE a.a1 = bb.a1(+) AND a.a1 = cc.a1(+) AND a.a1 = dd.a1(+);