在oracle中返回单行单列的子查询称之为标量子查询，标量子查询大多数情况出现在select后面，
而标量子查询的效率跟以下下几个因素密切相关，
1、主查询返回数据量的大小
2、子查询在关联列是否有高效的索引
3、主查询关联列的唯一值高低
下面测试这些因素对标量子查询性能的影响
创建测试表
SQL> create table test1 as select * from dba_objects;
Table created.
SQL> create table test3 as select * from dba_objects;
Table created.
首先测试标量子查询的性能和主查询返回数据量大小有关
SQL> select count(1) from test1 where owner='SYS';
COUNT(1)
----------
30811
SQL> select a.object_id,(select OBJECT_NAME from test3 b where a.object_id =b.object_id) object_name from test1 a where a.owner='SYS';
30851 rows selected.
Elapsed: 00:02:01.01
Execution Plan
----------------------------------------------------------
Plan hash value: 305196822
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 27489 | 289 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST3 | 1 | 30 | 289 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| TEST1 | 2499 | 27489 | 289 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."OBJECT_ID"=:B1)
2 - filter("A"."OWNER"='SYS')
Statistics
----------------------------------------------------------
196 recursive calls
0 db block gets
31995626 consistent gets
0 physical reads
0 redo size
1315596 bytes sent via SQL*Net to client
23140 bytes received via SQL*Net from client
2058 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
30851 rows processed
接下来更改owner减少主查询返回的数据量
SQL> select owner,count(object_id) from test3 group by owner;
OWNER COUNT(OBJECT_ID)
------------------------------ ----------------
OWBSYS_AUDIT 12
MDSYS 1509
PUBLIC 27702
OUTLN 9
CTXSYS 366
OLAPSYS 719
FLOWS_FILES 12
OWBSYS 2
HR 34
SYSTEM 529
ORACLE_OCM 8
EXFSYS 310
APEX_030200 2406
SCOTT 8
PM 27
OE 127
DBSNMP 57
ORDSYS 2532
ORDPLUGINS 10
SYSMAN 3491
SH 306
IX 55
APPQOSSYS 3
XDB 844
ORDDATA 248
BI 8
SYS 30811
WMSYS 316
SI_INFORMTN_SCHEMA 8
29 rows selected.
SQL> select a.object_id,(select OBJECT_NAME from test3 b where a.object_id =b.object_id) object_name from test1 a where a.owner='SCOTT';
22 rows selected.
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 305196822
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 27489 | 289 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST3 | 1 | 30 | 289 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| TEST1 | 2499 | 27489 | 289 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."OBJECT_ID"=:B1)
2 - filter("A"."OWNER"='SCOTT')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
23901 consistent gets
0 physical reads
0 redo size
1092 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22 rows processed
从执行计划来看差别十分明显，当主查询的返回数据量大的时候严重影响性能。
测试标量子查询的性能和子查询是否有高效索引有关
查看表test3上是否有高效的索引
select index_name,column_name from dba_ind_columns where table_name='TEST3';
SQL> select index_name,column_name from dba_ind_columns where table_name='TEST3';
INDEX_NAME COLUMN_NAME
------------------------------
IND_TEST3_NAME OBJECT_NAME
从上面的查询结果来看并没有合适的索引，现在创建索引再执行第一条SQL查看执行效率；
创建索引，
create index ind_text3_id on test3(object_id);
SQL> create index ind_text3_id on test3(object_id);
Index created.
select a.object_id,(select OBJECT_NAME from test3 b where a.object_id =b.object_id) object_name from test1 a where a.owner='SYS';
SQL> select a.object_id,(select OBJECT_NAME from test3 b where a.object_id =b.object_id) object_name from test1 a where a.owner='SYS';
30851 rows selected.
Elapsed: 00:00:00.68
Execution Plan
----------------------------------------------------------
Plan hash value: 569210033
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 27489 | 289 (1)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST3 | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEXT3_ID | 1 | | 1 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | TEST1 | 2499 | 27489 | 289 (1)| 00:00:04 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."OBJECT_ID"=:B1)
3 - filter("A"."OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14058 consistent gets
156 physical reads
0 redo size
1315596 bytes sent via SQL*Net to client
23140 bytes received via SQL*Net from client
2058 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30851 rows processed
从执行信息上看，在子查询表上创建了索引之后SQL性能得到了大幅度提高，
接下来测试主查询关联列唯一值对标量子查询性能的影响。
先删除掉在子查询表上创建的索引。
drop index ind_text3_id;
SQL> drop index ind_text3_id;
Index dropped.
--把SYS用户下的object_id 都更新为同一个值。
update test1 set object_id =11 where owner='SYS';
SQL> update test1 set object_id =11 where owner='SYS';
30851 rows updated.
SQL> commit;
Commit complete.
然后执行第一条SQL查看执行效率。
SQL> select a.object_id,(select OBJECT_NAME from test3 b where a.object_id =b.object_id) object_name from test1 a where a.owner='SYS';
30851 rows selected.
Elapsed: 00:00:00.40
Execution Plan
----------------------------------------------------------
Plan hash value: 305196822
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2499 | 27489 | 289 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST3 | 1 | 30 | 289 (1)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| TEST1 | 2499 | 27489 | 289 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."OBJECT_ID"=:B1)
2 - filter("A"."OWNER"='SYS')
Statistics
----------------------------------------------------------
196 recursive calls
0 db block gets
4176 consistent gets
0 physical reads
0 redo size
531163 bytes sent via SQL*Net to client
23140 bytes received via SQL*Net from client
2058 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
30851 rows processed
可以看到当主查询关联列的唯一值很低的时候，标量子查询的效率会很高。
最后修改时间：2020-12-23 19:49:04
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
