in_list 11g優化器改進

yangzhangyue發表於2013-07-24

測試指令碼：
create table audience as
select
        trunc(dbms_random.value(1,13)) month_no
from
        all_objects
where
        rownum <= 1200
;

begin
        dbms_stats.gather_table_stats(
                user,
                'audience',
                cascade => true,
                estimate_percent => null,
                method_opt => 'for all columns size 1'
        );
end;
/
set autotrace traceonly explain

select count(*) from audience
where month_no = 25
;

select count(*) from audience
where month_no in (4, 4)
;

select count(*) from audience
where month_no in (3, 25)
;

select count(*) from audience
where month_no in (3, 25, 26)
;

select count(*) from audience
where month_no in (3, 25, 25, 26)
;

select count(*) from audience
where month_no in (3, 25, null)
;

select count(*) from audience
where month_no in (:b1, :b2, :b3)
;

set autotrace off

現象：
10g
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |     1 |     3 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=25)

Note
-----
   - cpu costing is off (consider enabling it)

Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |   100 |   300 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=4)

Note
-----
   - cpu costing is off (consider enabling it)

Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |   100 |   300 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25)

Note
-----
   - cpu costing is off (consider enabling it)

Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |   101 |   303 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)

Note
-----
   - cpu costing is off (consider enabling it)

Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |   101 |   303 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)

Note
-----
   - cpu costing is off (consider enabling it)

Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |   200 |   600 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR
              "MONTH_NO"=TO_NUMBER(NULL))

Note
-----
   - cpu costing is off (consider enabling it)

Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |   300 |   900 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=TO_NUMBER(:B1) OR "MONTH_NO"=TO_NUMBER(:B2) OR
              "MONTH_NO"=TO_NUMBER(:B3))

11g:
Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |     1 |     3 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=25)

Note
-----
   - cpu costing is off (consider enabling it)

Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |   100 |   300 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=4)

Note
-----
   - cpu costing is off (consider enabling it)

Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |   100 |   300 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25)

Note
-----
   - cpu costing is off (consider enabling it)

Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |   101 |   303 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)

Note
-----
   - cpu costing is off (consider enabling it)

Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |   101 |   303 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25 OR "MONTH_NO"=26)

Note
-----
   - cpu costing is off (consider enabling it)

Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |   100 |   300 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=3 OR "MONTH_NO"=25)

Note
-----
   - cpu costing is off (consider enabling it)

Execution Plan
----------------------------------------------------------
Plan hash value: 3337892515

---------------------------------------------------------------
| Id | Operation          | Name     | Rows | Bytes | Cost |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 | SORT AGGREGATE    |          |     1 |     3 |       |
|* 2 |   TABLE ACCESS FULL| AUDIENCE |   300 |   900 |     2 |
---------------------------------------------------------------

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

   2 - filter("MONTH_NO"=TO_NUMBER(:B1) OR "MONTH_NO"=TO_NUMBER(:B2) OR
              "MONTH_NO"=TO_NUMBER(:B3))

   觀測紅色字型部分的內容。我們會發現11g中能更精確的計算基數card的值。在劉易斯《基於成本的oracle優化法則中》，他所測試的9i/10g還不能正確計算超出最大最小值界限，只可以很好的處理重複值
我所測試的10g版本已經可以很好的處理最大最小值的問題。11.2.0.3.6中進一步完善了演算法，可以更好的處理異常值，但幫定變數還不行

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29033984/viewspace-767028/，如需轉載，請註明出處，否則將追究法律責任。

改進資料庫效能-SQL查詢優化
2014-05-18
資料庫SQL優化
11g Oracle ORAPWD的改進
2010-11-20
Oracle
oracle passwordfile作用 &11g改進
2010-04-29
Oracle
Vue 前端許可權控制的優化改進版
2021-07-06
Vue前端優化
11g優化器增加Null aware anti join
2011-04-15
優化Null
11g Oracle DBNEWID的改進
2009-04-07
Oracle
SQL改寫優化
2012-05-03
SQL優化
MySQL核心月報2015.01-MySQL·優化改進·複製效能改進過程
2016-05-23
MySql優化
oracle 11g對於表壓縮改進
2010-01-27
Oracle
按照oracle效能改進方法論的步驟來優化系統！
2008-05-22
Oracle優化
PL/SQL解數獨在簡單優化基礎上的改進
2016-09-23
SQL優化
優化器
2012-10-14
優化
騰訊AI Lab提出翻譯改進模型Transformer的3個優化方法
2018-10-24
AI模型ORM優化
優化器提示
2012-10-14
優化
in_list查詢與or擴充套件
2011-12-08
套件
SQL Server調優系列進階篇（查詢優化器的執行方式）
2014-12-22
SQLServer優化
Java11改進的垃圾回收器
2024-03-05
Java
MySQL效能優化之簡單sql改寫
2019-11-12
MySql優化
SQLServer效能優化之改寫SQL語句
2010-07-28
SQLServer優化
Python 3.8 新功能-來自社群翻譯貼-個人改進程式碼優化版
2019-11-27
Python優化
oracle 的優化器
2015-03-16
Oracle優化
查詢優化器
2015-05-08
優化
【摘】 oracle優化器
2009-04-18
Oracle優化
Oracle的優化器
2010-07-13
Oracle優化
神經網路進化能否改變機器學習？
2018-04-18
神經網路機器學習
生產系統調優之_毫秒級的改進
2014-06-03
11g中關於控制檔案自動備份的改進
2015-08-08
Android效能優化篇：從程式碼角度進行優化
2014-11-28
Android優化
優化-瀏覽器快取和壓縮優化
2018-02-05
優化瀏覽器快取
前端效能優化之桌面瀏覽器優化策略
2017-12-29
前端優化瀏覽器
前端效能優化——桌面瀏覽器前端優化策略
2018-01-14
前端優化瀏覽器
SQL 層功能改進 - lookupJoin 的最佳化
2023-03-14
SQL
瀏覽器前端優化
2017-08-26
瀏覽器前端優化
【筆記】oracle 優化器
2009-01-14
筆記Oracle優化
優化器的侷限
2011-06-07
優化
PG 優化器的精彩
2012-08-02
優化
共享儲存器優化
2010-06-24
優化
Android退出應用最優雅的方式(改進版)
2016-01-07
Android

in_list 11g優化器改進

相關文章