[20210203]max优化的困惑.txt

来源：这里教程网时间：2026-03-03 16:26:02 作者：

[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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推