> 本文測試用語句來自下面大佬文章《ORACLE CBO 的 SQL 自動轉換（Cost Based Transformations）之五》
本文主要介紹由於11.2.0.4.190115中由於bug導致子查詢不展開的情況。

[https://blog.csdn.net/weixin_50513167/article/details/115694814](https://blog.csdn.net/weixin_50513167/article/details/115694814)

## 1. 基礎知識之子查詢展開

子查詢展開（Subquery Unnesting）是最佳化器處理帶子查詢的目標SQL的一種最佳化手段，它是指最佳化器不再將目標SQL中的子查詢當作一個獨立的處理單元來單獨執行，而是將該子查詢轉換為它自身和外部查詢之間等價的表連線。

### 1.1 子查詢展開兩種情形

- 子查詢拆開（即將該子查詢中的表、檢視從子查詢中拿出來，然後和外部查詢中的表、檢視做表連線）
- 不拆開但是會把該子查詢轉換為一個內嵌檢視（Inline View），然後再和外部查詢中的表、檢視做表連線，這種情況下，相當於最佳化器多了一條路徑，CBO會考慮cost高低來決定是否將內嵌檢視用來與外部查詢表做關聯，還是用外部表的資料來驅動內嵌檢視走filter型別執行計劃。

### 1.2 子查詢是否展開判斷

- 子查詢如果展開，則會看到執行計劃中會將子查詢中基表拿出來與主查詢中基表做關聯或將子查詢轉換為內部檢視與主查詢中基表做關聯。
- 子查詢如果未展開，一般會在執行計劃中最晚執行filter型別的子查詢過濾。

### 1.3 _optimizer_unnest_corr_set_subq

該引數控制最佳化器在SQL語句包含子查詢時，是否在滿足子查詢展開條件時，是否可以將子查詢作為一個檢視與主查詢關聯查詢。

## 2. Oracle 11.2.0.4.190115 PSU下測試

> Oracle版本：Oracle 11.2.0.4.190115 for Linux x86_64
>
> Oracle引數:   最佳化器相關引數均為預設值，即相關_optimizer_unnest_corr_set_subq等引數預設均為true。

### 2.1 構建測試環境

> 測試用例均來自於https://blog.csdn.net/weixin_50513167/article/details/115694814

```
drop table t1 purge;
drop table t2 purge;
drop table drv purge;

create table t1(key, pad) as
select to_char(rownum*2-1), lpad(' ',50) from dual connect by level <= 100;

create table t2(key, pad) as
select to_char(rownum*2), lpad(' ',50) from dual connect by level <= 100;

create table drv(key, pad) as
select rownum, lpad(' ',500) from dual connect by level <= 5
union all
select -rownum, lpad(' ',500) from dual connect by level <= 1000;

exec dbms_stats.gather_table_stats(user, 'T1');
exec dbms_stats.gather_table_stats(user, 'T2');
exec dbms_stats.gather_table_stats(user, 'DRV');
```

### 2.2 執行測試SQL

```
# 下面引數預設即為true，此處設定為true是為了體現該引數確實為true
SQL > alter session set "_optimizer_unnest_corr_set_subq" = TRUE;

Session altered.

SQL > set autot on
SQL > select key from drv
  2  where exists (
  3      select key
  4      from t1
  5      where drv.key = to_number(t1.key)
  6      union all
  7      select key
  8      from t2
  9      where drv.key = to_number(t2.key)
 10    );

KEY
----------------------------------------
                                       1
                                       2
                                       3
                                       4
                                       5

Execution Plan
----------------------------------------------------------
Plan hash value: 1881039188

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     5 |  3040   (1)| 00:00:37 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | DRV  |  1005 |  5025 |    23   (0)| 00:00:01 |
|   3 |   UNION-ALL         |      |       |       |            |          |
|*  4 |    TABLE ACCESS FULL| T1   |     1 |     4 |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| T2   |     1 |     4 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

1 - filter( EXISTS ( (SELECT "KEY" FROM "T1" "T1" WHERE
              TO_NUMBER("T1"."KEY")=:B1) UNION ALL  (SELECT "KEY" FROM "T2" "T2"
              WHERE TO_NUMBER("T2"."KEY")=:B2)))
   4 - filter(TO_NUMBER("T1"."KEY")=:B1)
   5 - filter(TO_NUMBER("T2"."KEY")=:B1)

Statistics
----------------------------------------------------------
         39  recursive calls
          0  db block gets
       4176  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
          5  rows processed
```

> 可以看到上述SQL走了filter型別的執行計劃，執行步驟為2-4-5-3-1，子查詢在最後才進行過濾，即兩種展開子查詢方法均未使用。

## 2.3 SQL執行計劃分析

- SQL子查詢中有union all聚合運算，所以不滿足直接拿基表出來與主查詢中表drv做關聯的子查詢展開情形，也沒有運算等，所以其實複合子查詢展開的第二種情形即將子查詢做內嵌檢視，然後與基表drv做半連線查詢。
- 根據上一條，那麼該SQL就有兩條執行計劃：
  1. 全表掃描drv，得到結果集1，全表掃描T1、T2然後做union all得到結果集2，使用結果集1驅動結果集2走filter（filter其實是一種改良nested loop）型別執行計劃使用子查詢中條件篩選得到最終結果集，，即不做子查詢展開的執行計劃。
  2. 全表掃描drv，得到結果集1，全表掃描T1、T2然後做union all得到結果集2，使用兩個結果集中較小結果集驅動較大結果集進行半連線(由於使用exists)，可以發現只需要對drv、T1、T2表掃描一次即可，即做子查詢展開第二種情形的執行計劃。
- 目前執行計劃是最優的嗎？
  1. filter型別執行計劃是一種改良nested loop，主查詢中記錄數決定了對子查詢中表掃描次數，本例中drv表有1005條記錄，則將會對T1、T2表全表掃描1005次（下面會證明這個結果），當然未必所有filter型別執行計劃都會驅動子查詢執行主查詢結果集那麼多次。
  2. 如果做子查詢展開，則只會對drv、T1、T2全表掃描一次（由於三張表均無索引），將會大大提高執行效率，那麼是什麼導致最佳化器未選擇cost明顯更低的執行計劃呢？
     - 統計資訊不準導致，很顯然統計資訊我們是收集了，並且執行計劃中預估rows也是準確的。
     - bug導致，後面會說明相關bug以及打補丁之後測試。

```
1. 使用gather_plan_statistics獲取真正執行計劃
SQL > SELECT /*+gather_plan_statistics*/
 KEY
  FROM DRV
 WHERE EXISTS (SELECT NULL
          FROM T1
         WHERE DRV.KEY = TO_NUMBER(T1.KEY)
        UNION ALL
        SELECT NULL
          FROM T2
         WHERE DRV.KEY = TO_NUMBER(T2.KEY))

2. 查詢
SQL > select sql_id,sql_text,child_number,executions from v$sql where sql_text like 'select /*+gather_plan_statistics*/key from drv%';

SQL_ID
-------------
7cq2303g62nb7

3.獲取真正執行計劃
SQL > select * from table(dbms_xplan.display_cursor('7cq2303g62nb7',null,format=>'advanced allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7cq2303g62nb7, child number 0
-------------------------------------
select /*+gather_plan_statistics*/key from drv where exists (
select null     from t1     where drv.key = to_number(t1.key)     union
all     select null     from t2     where drv.key = to_number(t2.key)
)

Plan hash value: 1881039188

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |       |  3040 (100)|          |      5 |00:00:00.01 |    8097 |
|*  1 |  FILTER             |      |      1 |        |       |            |          |      5 |00:00:00.01 |    8097 |
|   2 |   TABLE ACCESS FULL | DRV  |      1 |   1005 |  5025 |    23   (0)| 00:00:01 |   1005 |00:00:00.01 |      75 |
|   3 |   UNION-ALL         |      |   1005 |        |       |            |          |      5 |00:00:00.05 |    8022 |
|*  4 |    TABLE ACCESS FULL| T1   |   1005 |      1 |     4 |     3   (0)| 00:00:01 |      3 |00:00:00.02 |    6018 |
|*  5 |    TABLE ACCESS FULL| T2   |   1002 |      1 |     4 |     3   (0)| 00:00:01 |      2 |00:00:00.02 |    2004 |
----------------------------------------------------------------------------------------------------------------------
...省略部分

# 可以看到starts列，對T1、T2全表掃描了1005次，這顯然是不合理的，也是我們不希望看到的。

```

## 3. bug說明

透過搜尋Mos發現如下bug

> Bug 19523291 - Subquery unnesting does not happen or ORA-1790 is signaled during subquery unnesting (Doc ID 19523291.8)
![image.png](https://oss-emcsprod-public.modb.pro/image/editor/20210418-475835d6-4aaa-4392-9a3e-857547cbc1e8.png)
## 4. 打補丁之後測試是否生效

### 4.1 打補丁

> Mos有提供上述bug的patch，下載下來打補丁之後測試效果
>
> 隨後附件會附加mos文件與patch

```
$ unzip p25103607_112040_Linux-x86-64.zip 
$ /oracle/app/oracle/product/11.2/db_1/OPatch/opatch apply
Oracle Interim Patch Installer version 11.2.0.3.25
Copyright (c) 2021, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle/app/oracle/product/11.2/db_1
Central Inventory : /oracle/app/oraInventory
   from           : /oracle/app/oracle/product/11.2/db_1/oraInst.loc
OPatch version    : 11.2.0.3.25
OUI version       : 11.2.0.4.0
Log file location : /oracle/app/oracle/product/11.2/db_1/cfgtoollogs/opatch/opatch2021-04-12_21-48-11PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   25103607

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/app/oracle/product/11.2/db_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '25103607' to OH '/oracle/app/oracle/product/11.2/db_1'

Patching component oracle.rdbms, 11.2.0.4.0...
Patch 25103607 successfully applied.
Log file location: /oracle/app/oracle/product/11.2/db_1/cfgtoollogs/opatch/opatch2021-04-12_21-48-11PM_1.log

OPatch succeeded.

SQL > startup;
ORACLE instance started.

Total System Global Area                                471830528 bytes
Fixed Size                                                2254344 bytes
Variable Size                                           276826616 bytes
Database Buffers                                        188743680 bytes
Redo Buffers                                              4005888 bytes
Database mounted.
Database opened.
```

### 4.2 再次測試

```
SQL > alter session set "_optimizer_unnest_corr_set_subq" = TRUE;

Session altered.

SQL > set autot on
SQL > select key from drv
  2  where exists (
  3      select null
  4      from t1
  5      where drv.key = to_number(t1.key)
  6      union all
  7      select null
  8      from t2
  9      where drv.key = to_number(t2.key)
 10    );

KEY
----------------------------------------
                                       1
                                       2
                                       3
                                       4
                                       5

Execution Plan
----------------------------------------------------------
Plan hash value: 1657361037

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    18 |    29   (0)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|         |     1 |    18 |    29   (0)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1 |   200 |  2600 |     6   (0)| 00:00:01 |
|   3 |    UNION-ALL         |         |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T1      |   100 |   400 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2      |   100 |   400 |     3   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL  | DRV     |  1005 |  5025 |    23   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

1 - access("DRV"."KEY"="VW_COL_1")

Statistics
----------------------------------------------------------
         37  recursive calls
          0  db block gets
        166  consistent gets
         79  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
          5  rows processed
```

可以看到補丁已經解決了子查詢不展開的bug，Oracle已經將該子查詢作為一個內嵌檢視與外部基表進行了連線，即子查詢展開的第二種情形，cost已經大大降低。

## 5. 透過SQL改寫，避免最佳化器相關特性

如果子查詢可以展開，且我們可以清楚知道展開後cost會遠低於不展開，那麼其實我們可以直接寫出改寫後SQL，省去最佳化器自己判斷，使執行計劃更加穩定。

改寫一定要基於等價原則。

```
# SQL原文
select key from drv
where exists (
    select null
    from t1
    where drv.key = to_number(t1.key)
    union all
    select null
    from t2
    where drv.key = to_number(t2.key)
  );
```

### 5.1 改寫1

```
# SQL原文
select key from drv
where exists (
    select null
    from t1
    where drv.key = to_number(t1.key)
    union all
    select null
    from t2
    where drv.key = to_number(t2.key)
  );

# 由於子查詢使用了exists，所以等價於預先distinct對t1、t2進行去重，然後與drv表做內連線
優點：CBO可以直接對內嵌檢視與drv表做連線。
缺點：由於子查詢會去重，所以可以看到結果會排序，原SQL結果集沒有排序，所以一旦t1/t2表較大時，排序將變的更加昂貴。
SELECT A.KEY
  FROM DRV A
  JOIN (select distinct key 
	from (SELECT KEY FROM T1 
	      UNION ALL 
	      SELECT KEY FROM T2) v_vso
	     ) V_1
    ON A.KEY = v_1.KEY;

KEY
----------------------------------------
                                       1
                                       2
                                       3
                                       4
                                       5

Execution Plan
----------------------------------------------------------
Plan hash value: 3618528922

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |   100 |  2700 |    31   (4)| 00:00:01 |
|*  1 |  HASH JOIN             |      |   100 |  2700 |    31   (4)| 00:00:01 |
|   2 |   VIEW                 |      |   100 |  2200 |     8  (13)| 00:00:01 |
|   3 |    HASH UNIQUE         |      |   100 |   400 |     8  (13)| 00:00:01 |
|   4 |     VIEW               |      |   202 |   808 |     7   (0)| 00:00:01 |
|   5 |      UNION-ALL         |      |       |       |            |          |
|   6 |       TABLE ACCESS FULL| T1   |   102 |   408 |     4   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL| T2   |   100 |   400 |     3   (0)| 00:00:01 |
|   8 |   TABLE ACCESS FULL    | DRV  |  1005 |  5025 |    23   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - access("A"."KEY"=TO_NUMBER("V_1"."KEY"))

Statistics
----------------------------------------------------------
         52  recursive calls
          0  db block gets
        204  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
          5  rows processed
```

### 5.2 改寫2

```
# SQL原文
select key from drv
where exists (
    select null
    from t1
    where drv.key = to_number(t1.key)
    union all
    select null
    from t2
    where drv.key = to_number(t2.key)
  );
  
  
# 當t1 t2 key無重複值時，即當t1.key與t2.key有主鍵唯一等約束時，且需要不奧正t1.key與t2.key聯合無重複值，則也等價於下面內連線形式
select a.key from drv a
join
(select key from t1
union all
select key from t2) b
on a.key = b.key;

KEY
----------------------------------------
                                       1
                                       2
                                       3
                                       4
                                       5

Execution Plan
----------------------------------------------------------
Plan hash value: 1119802508

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   200 |  5400 |    29   (0)| 00:00:01 |
|*  1 |  HASH JOIN           |      |   200 |  5400 |    29   (0)| 00:00:01 |
|   2 |   VIEW               |      |   200 |  4400 |     6   (0)| 00:00:01 |
|   3 |    UNION-ALL         |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T1   |   100 |   400 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   |   100 |   400 |     3   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL  | DRV  |  1005 |  5025 |    23   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

1 - access("A"."KEY"=TO_NUMBER("B"."KEY"))

Statistics
----------------------------------------------------------
         97  recursive calls
          0  db block gets
        219  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         19  sorts (memory)
          0  sorts (disk)
          5  rows processed
```

> 最後再次感謝[楊 昱明](https://blog.csdn.net/weixin_50513167)大佬文章https://blog.csdn.net/weixin_50513167/article/details/115694814，對最佳化器多了一些瞭解。

附件：
Mos文章：https://www.modb.pro/doc/26323
Patch:  https://www.modb.pro/download/71385

從Oracle 11.2.0.4 BUG到Oracle子查詢展開分析

相關文章