影響Oracle標量子查詢效能的三個因素

在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

可以看到當主查詢關聯列的唯一值很低的時候，標量子查詢的效率會很高。