[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;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2755616/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
