影响Oracle标量子查询性能的三个因素

来源：这里教程网时间：2026-03-03 16:18:30 作者：

在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

可以看到当主查询关联列的唯一值很低的时候，标量子查询的效率会很高。