原 Oracle视图中含有rownum导致不能走索引

2024年4月22日  

0    348    1

Tags： Oracle原创索引rownum

👉 本文共约627个字，系统预计阅读时间或需3分钟。

显示/隐藏全文目录

目录

• 现象
• 模拟
• 总结
• 参考

现象

环境：oracle 11.2.0.4

今天客户说，一个视图查询很慢，让帮忙分析。

分析后，发现是rownum导致的视图不能走索引。

模拟

对原查询进行简化模拟：

这里modify_time列为varchar2类型，但是因为某些原因，web应用查询该字段必须使用date类型，所以我在该列上创建了函数索引：

DROP TABLE TB; CREATE TABLE TB(ID INT, modify_time varchar2(14)); INSERT INTO TB (id,modify_time) SELECT rownum, to_char(sysdate + rownum,'YYYYMMDDHH24MISS') from dual CONNECT BY LEVEL <= 20000; create index idx_1 on tb(to_date(modify_time,'YYYYMMDDHH24MISS')); create index idx_2 on tb(modify_time); begin DBMS_STATS.gather_table_stats('lhr', 'tb',cascade => TRUE, DEGREE=>16,no_invalidate => false); end; create or replace view vw1 as select id,modify_time,to_date(modify_time,'YYYYMMDDHH24MISS') modify_at,to_date(modify_time,'YYYYMMDDHH24miss') modify_at1 from tb ; create or replace view vw2 as select id,modify_time,to_date(modify_time,'YYYYMMDDHH24MISS') modify_at,rownum rn from tb ; -- 走索引 select * from vw1 where modify_time= '2024-03-01'; select * from vw1 where modify_at= to_date('2024-03-01','yyyy-mm-dd'); select * from vw1 where modify_at= to_date('20240301000000','syyyy-mm-dd hh24:mi:ss'); -- 不走索引（to_date函数大小写问题） select * from vw1 where modify_at1= to_date('2024-03-01','yyyy-mm-dd'); -- 不走索引（rownum问题） select * from vw2 where modify_at= to_date('2024-03-01','yyyy-mm-dd'); select * from vw2 where modify_time= '2024-03-01';

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

DROP TABLE TB; CREATE TABLE TB(ID INT, modify_time varchar2(14));   INSERT INTO TB (id,modify_time) SELECT rownum, to_char(sysdate +  rownum,'YYYYMMDDHH24MISS') from dual CONNECT BY LEVEL <= 20000;   create index idx_1 on tb(to_date(modify_time,'YYYYMMDDHH24MISS')); create index idx_2 on tb(modify_time);   begin DBMS_STATS.gather_table_stats('lhr', 'tb',cascade => TRUE, DEGREE=>16,no_invalidate => false); end;   create or replace view vw1 as select id,modify_time,to_date(modify_time,'YYYYMMDDHH24MISS') modify_at,to_date(modify_time,'YYYYMMDDHH24miss') modify_at1   from  tb ;   create or replace view vw2 as select id,modify_time,to_date(modify_time,'YYYYMMDDHH24MISS') modify_at,rownum rn   from  tb ;   -- 走索引   select * from vw1  where  modify_time= '2024-03-01'; select * from vw1  where  modify_at= to_date('2024-03-01','yyyy-mm-dd'); select * from vw1  where  modify_at= to_date('20240301000000','syyyy-mm-dd hh24:mi:ss');   -- 不走索引（to_date函数大小写问题） select * from vw1  where  modify_at1= to_date('2024-03-01','yyyy-mm-dd');   -- 不走索引（rownum问题） select * from vw2  where  modify_at= to_date('2024-03-01','yyyy-mm-dd'); select * from vw2  where  modify_time= '2024-03-01';

执行计划：

SYS@LHR11G> conn lhr/lhr Connected. LHR@LHR11G> set autot on LHR@LHR11G> select * from vw1 where modify_at= to_date('2024-03-01','yyyy-mm-dd'); no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 4159717772 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 31 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TB | 1 | 31 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(TO_DATE("MODIFY_TIME",'YYYYMMDDHH24MISS')=TO_DATE(' 2024-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 21 recursive calls 0 db block gets 150 consistent gets 1 physical reads 0 redo size 473 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed LHR@LHR11G> select * from vw1 where modify_at= to_date('20240301000000','syyyy-mm-dd hh24:mi:ss'); no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 4159717772 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 31 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TB | 1 | 31 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_1 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(TO_DATE("MODIFY_TIME",'YYYYMMDDHH24MISS')=TO_DATE('202403010000 00','syyyy-mm-dd hh24:mi:ss')) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 21 recursive calls 0 db block gets 150 consistent gets 0 physical reads 0 redo size 473 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed LHR@LHR11G> select * from vw1 where modify_at1= to_date('2024-03-01','yyyy-mm-dd'); no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2878482057 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 31 | 22 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TB | 1 | 31 | 22 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_DATE("MODIFY_TIME",'YYYYMMDDHH24miss')=TO_DATE(' 2024-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 76 consistent gets 0 physical reads 0 redo size 473 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed LHR@LHR11G> select * from vw2 where modify_at= to_date('2024-03-01','yyyy-mm-dd'); no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1470101981 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19745 | 674K| 22 (0)| 00:00:01 | |* 1 | VIEW | VW2 | 19745 | 674K| 22 (0)| 00:00:01 | | 2 | COUNT | | | | | | | 3 | TABLE ACCESS FULL| TB | 19745 | 424K| 22 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("MODIFY_AT"=TO_DATE(' 2024-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 21 recursive calls 0 db block gets 216 consistent gets 0 physical reads 0 redo size 465 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed LHR@LHR11G> select * from vw1 where modify_time= '2024-03-01'; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 956939525 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 31 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TB | 1 | 31 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_2 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("MODIFY_TIME"='2024-03-01') Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 37 recursive calls 0 db block gets 81 consistent gets 0 physical reads 0 redo size 546 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed LHR@LHR11G> select * from vw2 where modify_time= '2024-03-01'; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1470101981 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19745 | 848K| 22 (0)| 00:00:01 | |* 1 | VIEW | VW2 | 19745 | 848K| 22 (0)| 00:00:01 | | 2 | COUNT | | | | | | | 3 | TABLE ACCESS FULL| TB | 19745 | 597K| 22 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("MODIFY_TIME"='2024-03-01') Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 21 recursive calls 0 db block gets 218 consistent gets 0 physical reads 0 redo size 538 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed LHR@LHR11G>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243

SYS@LHR11G> conn lhr/lhr Connected. LHR@LHR11G> set autot on LHR@LHR11G> select * from vw1  where  modify_at= to_date('2024-03-01','yyyy-mm-dd');   no rows selected   Execution Plan ---------------------------------------------------------- Plan hash value: 4159717772   ------------------------------------------------------------------------------------- | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |       |     1 |    31 |     1   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| TB    |     1 |    31 |     1   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | IDX_1 |     1 |       |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access(TO_DATE("MODIFY_TIME",'YYYYMMDDHH24MISS')=TO_DATE(' 2024-03-01               00:00:00', 'syyyy-mm-dd hh24:mi:ss'))   Note -----    - dynamic sampling used for this statement (level=2)   Statistics ----------------------------------------------------------          21  recursive calls           0  db block gets         150  consistent gets           1  physical reads           0  redo size         473  bytes sent via SQL*Net to client         512  bytes received via SQL*Net from client           1  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           0  rows processed   LHR@LHR11G> select * from vw1  where  modify_at= to_date('20240301000000','syyyy-mm-dd hh24:mi:ss');   no rows selected   Execution Plan ---------------------------------------------------------- Plan hash value: 4159717772   ------------------------------------------------------------------------------------- | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |       |     1 |    31 |     1   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| TB    |     1 |    31 |     1   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | IDX_1 |     1 |       |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access(TO_DATE("MODIFY_TIME",'YYYYMMDDHH24MISS')=TO_DATE('202403010000               00','syyyy-mm-dd hh24:mi:ss'))   Note -----    - dynamic sampling used for this statement (level=2)   Statistics ----------------------------------------------------------          21  recursive calls           0  db block gets         150  consistent gets           0  physical reads           0  redo size         473  bytes sent via SQL*Net to client         512  bytes received via SQL*Net from client           1  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           0  rows processed   LHR@LHR11G> select * from vw1  where  modify_at1= to_date('2024-03-01','yyyy-mm-dd');   no rows selected   Execution Plan ---------------------------------------------------------- Plan hash value: 2878482057   -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |    31 |    22   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| TB   |     1 |    31 |    22   (0)| 00:00:01 | --------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter(TO_DATE("MODIFY_TIME",'YYYYMMDDHH24miss')=TO_DATE('               2024-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))   Note -----    - dynamic sampling used for this statement (level=2)   Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets          76  consistent gets           0  physical reads           0  redo size         473  bytes sent via SQL*Net to client         512  bytes received via SQL*Net from client           1  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           0  rows processed   LHR@LHR11G> select * from vw2  where  modify_at= to_date('2024-03-01','yyyy-mm-dd');   no rows selected   Execution Plan ---------------------------------------------------------- Plan hash value: 1470101981   ---------------------------------------------------------------------------- | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT    |      | 19745 |   674K|    22   (0)| 00:00:01 | |*  1 |  VIEW               | VW2  | 19745 |   674K|    22   (0)| 00:00:01 | |   2 |   COUNT             |      |       |       |            |          | |   3 |    TABLE ACCESS FULL| TB   | 19745 |   424K|    22   (0)| 00:00:01 | ----------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("MODIFY_AT"=TO_DATE(' 2024-03-01 00:00:00', 'syyyy-mm-dd               hh24:mi:ss'))   Note -----    - dynamic sampling used for this statement (level=2)   Statistics ----------------------------------------------------------          21  recursive calls           0  db block gets         216  consistent gets           0  physical reads           0  redo size         465  bytes sent via SQL*Net to client         512  bytes received via SQL*Net from client           1  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           0  rows processed   LHR@LHR11G> select * from vw1  where  modify_time= '2024-03-01';   no rows selected   Execution Plan ---------------------------------------------------------- Plan hash value: 956939525   ------------------------------------------------------------------------------------- | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |       |     1 |    31 |     1   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| TB    |     1 |    31 |     1   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | IDX_2 |     1 |       |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("MODIFY_TIME"='2024-03-01')   Note -----    - dynamic sampling used for this statement (level=2)   Statistics ----------------------------------------------------------          37  recursive calls           0  db block gets          81  consistent gets           0  physical reads           0  redo size         546  bytes sent via SQL*Net to client         512  bytes received via SQL*Net from client           1  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           0  rows processed   LHR@LHR11G> select * from vw2  where  modify_time= '2024-03-01';   no rows selected   Execution Plan ---------------------------------------------------------- Plan hash value: 1470101981   ---------------------------------------------------------------------------- | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT    |      | 19745 |   848K|    22   (0)| 00:00:01 | |*  1 |  VIEW               | VW2  | 19745 |   848K|    22   (0)| 00:00:01 | |   2 |   COUNT             |      |       |       |            |          | |   3 |    TABLE ACCESS FULL| TB   | 19745 |   597K|    22   (0)| 00:00:01 | ----------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("MODIFY_TIME"='2024-03-01')   Note -----    - dynamic sampling used for this statement (level=2)   Statistics ----------------------------------------------------------          21  recursive calls           0  db block gets         218  consistent gets           0  physical reads           0  redo size         538  bytes sent via SQL*Net to client         512  bytes received via SQL*Net from client           1  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           0  rows processed   LHR@LHR11G>

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

相关文章

• 在Oracle中， IS NULL如何用到索引？
• 在Oracle的存储过程、函数或触发器中，如何将相关错误信息写入告警日志中
• Oracle中的临时表空间简介和维护
• Oracle中，若临时表空间使用率过高有什么调优思路？
• Oracle环境中报错TNS-00511、TNS-12541、No listener，其实监听已启动
• Oracle 12c的DG自动同步密码文件--ASM 新特性：共享密码文件
• Oracle DG中日志不同步的ORA-16191错误解决整理
• Oracle的DG环境中由于配置了登陆触发器导致日志不能同步报错ORA-16191的实验
• Oracle将秒转换为时分秒的格式
• 在Oracle的DG环境中，主库新增数据文件的过程
• Oracle对索引做monitoring和nomonitoring会导致游标失效
• 客户某套Oracle rac业务库出现严重的library cache等待排查
• Oracle中的临时表介绍
• Oracle 23ai介绍（含docker和单机安装）
• 在Docker中快速体验Oracle 23ai版
• Oracle登录报错ORA-12504: TNS: 监听程序在 CONNECT_DATA 中未获得 SERVICE_NAME
• 如何dump Oracle数据库内部结构？
• Oracle解析错误的跟踪办法
• 什么是Oracle的高版本游标（High Version Count）？如何排查？（持续更新）
• 如何得到已执行的目标SQL中的绑定变量的值？

打赏赞(1)分享

标签： Oracle原创索引rownum

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

• 下一篇 如何dump Oracle数据库内部结构？
• 上一篇 在GreenPlum中配置PgBouncer连接池并使用Prometheus+Grafana对pgbouncer监控

您可能还喜欢...

• 0

  RMAN备份及恢复归档日志的语法

  2022年11月7日

   由 小麦苗 · Published 2022年11月7日 · Last modified 2022年11月17日

  · 已更新 2022年11月17日  

  0    375    1
• 0

  Oracle如何提高或加速数据泵(expdp和impdp)的操作性能

  2022年11月8日

   由 小麦苗 · Published 2022年11月8日 · Last modified 2022年12月3日

  · 已更新 2022年12月3日  

  0    846    3
• 0

  什么是Oracle的绑定变量分级？

  2024年4月14日

   由 小麦苗 · Published 2024年4月14日 · Last modified 2024年4月15日

  · 已更新 2024年4月15日  

  0    314    1