[20210203]max優化的困惑.txt

--//昨天看連結:https://jonathanlewis.wordpress.com/2021/01/25/index-hints-3/
--//原來索引還支援許多提示,我自己都不知道.
Version Hint
8.0      index
8.1      index_asc, index_desc, index_ffs, no_index
9.0      index_ss, index_ss_asc, index_ss_desc
10.1     no_index_ffs, no_index_ss
11.1     index_rs_asc, index_rs_desc

--//突然想起以前遇到的max優化問題,通過例子說明:

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立測試例子：
create table t tablespace users as
select
rownum id1
,trunc(dbms_random.value(1,200)) id2
,sysdate-dbms_random.value(1, 400) cr_date
,decode(mod(rownum,10),'0','1','0') flag
from dual connect by level<=1e6;

create index i_t_id2_cr_date on t(id2,cr_date);
--//分析略。

3.測試：
SCOTT@book> alter session set statistics_level = all;
Session altered.

SCOTT@book> select max(cr_date) from t where id2=42 ;
MAX(CR_DATE)
-------------------
2021-02-02 08:37:17

Plan hash value: 3363495196
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                    | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       3 |
|   1 | SORT AGGREGATE              |                 |      1 |      1 |    12 |            |          |      1 |00:00:00.01 |       3 |
|   2 |   FIRST ROW                  |                 |      1 |      1 |    12 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|* 3 |    INDEX RANGE SCAN (MIN/MAX)| I_T_ID2_CR_DATE |      1 |      1 |    12 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------------------------------------
--//注意執行計劃的INDEX RANGE SCAN (MIN/MAX).如果我在加入flag='1'.

SCOTT@book> select max(cr_date) from t where id2=42 and flag='1';
MAX(CR_DATE)
-------------------
2021-02-01 21:03:07

Plan hash value: 2966233522
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |   906 (100)|          |      1 |00:00:00.07 |    3253 |   3251 |
|   1 | SORT AGGREGATE    |      |      1 |      1 |    14 |            |          |      1 |00:00:00.07 |    3253 |   3251 |
|* 2 |   TABLE ACCESS FULL| T    |      1 |   2513 | 35182 |   906   (1)| 00:00:11 |    487 |00:00:00.07 |    3253 |   3251 |
------------------------------------------------------------------------------------------------------------------------------
--//我僅僅加了一個條件flag='1',不再出現INDEX RANGE SCAN (MIN/MAX).因為索引範圍掃描不佳,oracle選擇全表掃描.

--//如果我改用索引:
SCOTT@book> select /*+ index(t) */ max(cr_date) from t where id2=42 and flag='1';
MAX(CR_DATE)
-------------------
2021-02-01 21:03:07

Plan hash value: 1695966225
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                    | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |       | 5043 (100)|          |      1 |00:00:00.02 |    5135 |
|   1 | SORT AGGREGATE              |                 |      1 |      1 |    14 |            |          |      1 |00:00:00.02 |    5135 |
|* 2 |   TABLE ACCESS BY INDEX ROWID| T               |      1 |   2513 | 35182 | 5043   (1)| 00:01:01 |    487 |00:00:00.02 |    5135 |
|* 3 |    INDEX RANGE SCAN          | I_T_ID2_CR_DATE |      1 |   5025 |       |    18   (0)| 00:00:01 |   5117 |00:00:00.01 |      19 |
------------------------------------------------------------------------------------------------------------------------------------------
--//因為oracle不走INDEX RANGE SCAN (MIN/MAX),選擇INDEX RANGE SCAN,回表記錄太多,執行效率底低下.

--//改用index_desc呢?
SCOTT@book> select /*+ index_desc(t) */ max(cr_date) from t where id2=42 and flag='1';
MAX(CR_DATE)
-------------------
2021-02-01 21:03:07

Plan hash value: 1016148072
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                     | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |       | 5043 (100)|          |      1 |00:00:00.02 |    5135 |
|   1 | SORT AGGREGATE               |                 |      1 |      1 |    14 |            |          |      1 |00:00:00.02 |    5135 |
|* 2 |   TABLE ACCESS BY INDEX ROWID | T               |      1 |   2513 | 35182 | 5043   (1)| 00:01:01 |    487 |00:00:00.02 |    5135 |
|* 3 |    INDEX RANGE SCAN DESCENDING| I_T_ID2_CR_DATE |      1 |   5025 |       |    18   (0)| 00:00:01 |   5117 |00:00:00.01 |      19 |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"='1')
   3 - access("ID2"=42)
--//有時候感覺oracle優化器很笨,理論我從cr_date高階掃描,只要遇到滿足條件的flag='1'的條件就停止了.而實際的情況不是,要掃描滿足條件的索引段.

3.繼續分析:
--//我必須把3個欄位索引都包括,建立如下:
create index i_t_id2_cr_date_flag on t(id2,cr_date,flag);

SCOTT@book> select /*+ index(t) */ max(cr_date) from t where id2=42 and flag='1';
MAX(CR_DATE)
-------------------
2021-02-01 21:03:07

Plan hash value: 2904766522
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                    | Name                 | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |      1 |        |       |    15 (100)|          |      1 |00:00:00.01 |       3 |      2 |
|   1 | SORT AGGREGATE              |                      |      1 |      1 |    14 |            |          |      1 |00:00:00.01 |       3 |      2 |
|   2 |   FIRST ROW                  |                      |      1 |      1 |    14 |    15   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      2 |
|* 3 |    INDEX RANGE SCAN (MIN/MAX)| I_T_ID2_CR_DATE_FLAG |      1 |      1 |    14 |    15   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      2 |
--------------------------------------------------------------------------------------------------------------------------------------------------------
--//但是有時候這個不是我需要的,我們遇到的where可不僅僅一個flag欄位,往往有很多欄位,我不大可能把全部索引包括在索引裡面.

SCOTT@book> ALTER INDEX SCOTT.I_T_ID2_CR_DATE_FLAG INVISIBLE;
Index altered.

--//我一般改寫如下:
SCOTT@book> select * from (select cr_date from t where id2=42 and flag='1' order by cr_date desc) where rownum=1;
CR_DATE
-------------------
2021-02-01 21:03:07

Plan hash value: 3671452359
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                      | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |       |     6 (100)|          |      1 |00:00:00.01 |       9 |
|* 1 | COUNT STOPKEY                 |                 |      1 |        |       |            |          |      1 |00:00:00.01 |       9 |
|   2 |   VIEW                         |                 |      1 |      1 |     9 |     6   (0)| 00:00:01 |      1 |00:00:00.01 |       9 |
|* 3 |    TABLE ACCESS BY INDEX ROWID | T               |      1 |   2513 | 35182 |     6   (0)| 00:00:01 |      1 |00:00:00.01 |       9 |
|* 4 |     INDEX RANGE SCAN DESCENDING| I_T_ID2_CR_DATE |      1 |      2 |       |     3   (0)| 00:00:01 |      6 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------------------------------
--//這樣可以充分利用INDEX RANGE SCAN DESCENDING以及rownum=1的限制,在實際的生產應用中邏輯讀不會太高.
--//最壞的情況就是掃描該範圍索引段,這個在實際應用中一般很少出現.比如我查詢如下:

SCOTT@book> select * from (select cr_date from t where id2=42 and flag='0' order by cr_date desc) where rownum=1;
CR_DATE
-------------------
2021-02-02 08:37:17

Plan hash value: 3671452359
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                      | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |       |     6 (100)|          |      1 |00:00:00.01 |       4 |
|* 1 | COUNT STOPKEY                 |                 |      1 |        |       |            |          |      1 |00:00:00.01 |       4 |
|   2 |   VIEW                         |                 |      1 |      1 |     9 |     6   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|* 3 |    TABLE ACCESS BY INDEX ROWID | T               |      1 |   2513 | 35182 |     6   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|* 4 |     INDEX RANGE SCAN DESCENDING| I_T_ID2_CR_DATE |      1 |      2 |       |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------------------------------
--//id=4,A-rows=1,而不是前面的A-rows=6.這樣回表的查詢邏輯讀減少.
--//關於這類語句大家有什麼好建議呢,最好不要開發改語句的方式.比如我們生產系統語句如下:

SELECT SBXH
FROM MS_GHMX
WHERE     (SELECT MAX (ghsj)
              FROM ms_ghmx
             WHERE     BRID = :1
                   AND (KSDM = :2 OR KSDM = :3 OR YSDM = :"SYS_B_0")
                   AND THBZ = :"SYS_B_1") = ghsj
       AND BRID = :4
       AND (KSDM = :5 OR KSDM = :6 OR YSDM = :"SYS_B_2")
       AND THBZ = :"SYS_B_3";

--//說明:MS_GHMX 掛號明細表. brid表示病人ID,先開始開發建立的索引僅僅包含1個欄位.我刪除後建立了brid,ghsj索引.
--//我本來想如果執行計劃能利用min/max減少邏輯讀以及物理讀,我發現執行計劃無法實現,走的而是INDEX RANGE SCAN,
--//一旦回表如果brid記錄很多的情況下也就是就診次數很多,邏輯讀,物理讀會很高.這條語句排在SQL ordered by Reads靠前的位置.
--//我只能改寫如下,只要返回一條,就可以改寫如下:
SELECT /*+ gather_plan_statistics */
      sbxh
FROM ( SELECT ghsj, sbxh
            FROM ms_ghmx
           WHERE     BRID = :1
                 AND (KSDM = :2 OR KSDM = :3 OR YSDM = :"SYS_B_0")
                 AND THBZ = :"SYS_B_1"
        ORDER BY brid, ghsj DESC)
WHERE ROWNUM = 1;

[20210203]max優化的困惑.txt

相關文章