现象
环境：oracle 11.2.0.4
今天客户说，一个视图查询很慢，让帮忙分析。
分析后，发现是rownum导致的视图不能走索引。
模拟
对原查询进行简化模拟：
这里modify_time列为varchar2类型，但是因为某些原因，web应用查询该字段必须使用date类型，所以我在该列上创建了函数索引：
1DROPTABLE TB;
2CREATETABLE TB(IDINT, modify_time varchar2(14));
3
4INSERTINTO TB (id,modify_time)
5SELECTrownum, to_char(sysdate + rownum,'YYYYMMDDHH24MISS')
6from dual
7CONNECTBYLEVEL <= 20000;
8
9createindex idx_1 on tb(to_date(modify_time,'YYYYMMDDHH24MISS'));
10createindex idx_2 on tb(modify_time);
11
12begin DBMS_STATS.gather_table_stats('lhr', 'tb',cascade => TRUE, DEGREE=>16,no_invalidate => false); end;
13
14
15
16createorreplaceview vw1
17as
18selectid,modify_time,to_date(modify_time,'YYYYMMDDHH24MISS') modify_at,to_date(modify_time,'YYYYMMDDHH24miss') modify_at1
19from tb
20;
21
22createorreplaceview vw2
23as
24selectid,modify_time,to_date(modify_time,'YYYYMMDDHH24MISS') modify_at,rownum rn
25from tb
26;
27
28-- 走索引
29
30select * from vw1 where modify_time= '2024-03-01';
31select * from vw1 where modify_at= to_date('2024-03-01','yyyy-mm-dd');
32select * from vw1 where modify_at= to_date('20240301000000','syyyy-mm-dd hh24:mi:ss');
33
34-- 不走索引（to_date函数大小写问题）
35select * from vw1 where modify_at1= to_date('2024-03-01','yyyy-mm-dd');
36
37-- 不走索引（rownum问题）
38select * from vw2 where modify_at= to_date('2024-03-01','yyyy-mm-dd');
39select * from vw2 where modify_time= '2024-03-01';
执行计划：
1SYS@LHR11G> conn lhr/lhr
2Connected.
3LHR@LHR11G> set autot on
4LHR@LHR11G> select * from vw1 where modify_at= to_date('2024-03-01','yyyy-mm-dd');
5
6no rows selected
7
8
9Execution Plan
10----------------------------------------------------------
11Plan hash value: 4159717772
12
13-------------------------------------------------------------------------------------
14| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
15-------------------------------------------------------------------------------------
16| 0 | SELECTSTATEMENT | | 1 | 31 | 1 (0)| 00:00:01 |
17| 1 | TABLEACCESSBYINDEXROWID| TB | 1 | 31 | 1 (0)| 00:00:01 |
18|* 2 | INDEXRANGESCAN | IDX_1 | 1 | | 1 (0)| 00:00:01 |
19-------------------------------------------------------------------------------------
20
21Predicate Information (identifiedby operation id):
22---------------------------------------------------
23
242 - access(TO_DATE("MODIFY_TIME",'YYYYMMDDHH24MISS')=TO_DATE(' 2024-03-01
25 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
26
27Note
28-----
29 - dynamic sampling used for this statement (level=2)
30
31
32Statistics
33----------------------------------------------------------
3421recursive calls
350 db block gets
36150consistent gets
371physicalreads
380redosize
39473bytes sent via SQL*Net toclient
40512bytes received via SQL*Net fromclient
411SQL*Net roundtrips to/fromclient
420 sorts (memory)
430 sorts (disk)
440rows processed
45
46LHR@LHR11G> select * from vw1 where modify_at= to_date('20240301000000','syyyy-mm-dd hh24:mi:ss');
47
48no rows selected
49
50
51Execution Plan
52----------------------------------------------------------
53Plan hash value: 4159717772
54
55-------------------------------------------------------------------------------------
56| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
57-------------------------------------------------------------------------------------
58| 0 | SELECTSTATEMENT | | 1 | 31 | 1 (0)| 00:00:01 |
59| 1 | TABLEACCESSBYINDEXROWID| TB | 1 | 31 | 1 (0)| 00:00:01 |
60|* 2 | INDEXRANGESCAN | IDX_1 | 1 | | 1 (0)| 00:00:01 |
61-------------------------------------------------------------------------------------
62
63Predicate Information (identifiedby operation id):
64---------------------------------------------------
65
662 - access(TO_DATE("MODIFY_TIME",'YYYYMMDDHH24MISS')=TO_DATE('202403010000
67 00','syyyy-mm-dd hh24:mi:ss'))
68
69Note
70-----
71 - dynamic sampling used for this statement (level=2)
72
73
74Statistics
75----------------------------------------------------------
7621recursive calls
770 db block gets
78150consistent gets
790physicalreads
800redosize
81473bytes sent via SQL*Net toclient
82512bytes received via SQL*Net fromclient
831SQL*Net roundtrips to/fromclient
840 sorts (memory)
850 sorts (disk)
860rows processed
87
88LHR@LHR11G> select * from vw1 where modify_at1= to_date('2024-03-01','yyyy-mm-dd');
89
90no rows selected
91
92
93Execution Plan
94----------------------------------------------------------
95Plan hash value: 2878482057
96
97--------------------------------------------------------------------------
98| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
99--------------------------------------------------------------------------
100| 0 | SELECTSTATEMENT | | 1 | 31 | 22 (0)| 00:00:01 |
101|* 1 | TABLEACCESSFULL| TB | 1 | 31 | 22 (0)| 00:00:01 |
102--------------------------------------------------------------------------
103
104Predicate Information (identifiedby operation id):
105---------------------------------------------------
106
1071 - filter(TO_DATE("MODIFY_TIME",'YYYYMMDDHH24miss')=TO_DATE('
108 2024-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
109
110Note
111-----
112 - dynamic sampling used for this statement (level=2)
113
114
115Statistics
116----------------------------------------------------------
1170recursive calls
1180 db block gets
11976consistent gets
1200physicalreads
1210redosize
122473bytes sent via SQL*Net toclient
123512bytes received via SQL*Net fromclient
1241SQL*Net roundtrips to/fromclient
1250 sorts (memory)
1260 sorts (disk)
1270rows processed
128
129LHR@LHR11G> select * from vw2 where modify_at= to_date('2024-03-01','yyyy-mm-dd');
130
131no rows selected
132
133
134Execution Plan
135----------------------------------------------------------
136Plan hash value: 1470101981
137
138----------------------------------------------------------------------------
139| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
140----------------------------------------------------------------------------
141| 0 | SELECTSTATEMENT | | 19745 | 674K| 22 (0)| 00:00:01 |
142|* 1 | VIEW | VW2 | 19745 | 674K| 22 (0)| 00:00:01 |
143| 2 | COUNT | | | | | |
144| 3 | TABLEACCESSFULL| TB | 19745 | 424K| 22 (0)| 00:00:01 |
145----------------------------------------------------------------------------
146
147Predicate Information (identifiedby operation id):
148---------------------------------------------------
149
1501 - filter("MODIFY_AT"=TO_DATE(' 2024-03-01 00:00:00', 'syyyy-mm-dd
151 hh24:mi:ss'))
152
153Note
154-----
155 - dynamic sampling used for this statement (level=2)
156
157
158Statistics
159----------------------------------------------------------
16021recursive calls
1610 db block gets
162216consistent gets
1630physicalreads
1640redosize
165465bytes sent via SQL*Net toclient
166512bytes received via SQL*Net fromclient
1671SQL*Net roundtrips to/fromclient
1680 sorts (memory)
1690 sorts (disk)
1700rows processed
171
172LHR@LHR11G> select * from vw1 where modify_time= '2024-03-01';
173
174no rows selected
175
176
177Execution Plan
178----------------------------------------------------------
179Plan hash value: 956939525
180
181-------------------------------------------------------------------------------------
182| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
183-------------------------------------------------------------------------------------
184| 0 | SELECTSTATEMENT | | 1 | 31 | 1 (0)| 00:00:01 |
185| 1 | TABLEACCESSBYINDEXROWID| TB | 1 | 31 | 1 (0)| 00:00:01 |
186|* 2 | INDEXRANGESCAN | IDX_2 | 1 | | 1 (0)| 00:00:01 |
187-------------------------------------------------------------------------------------
188
189Predicate Information (identifiedby operation id):
190---------------------------------------------------
191
1922 - access("MODIFY_TIME"='2024-03-01')
193
194Note
195-----
196 - dynamic sampling used for this statement (level=2)
197
198
199Statistics
200----------------------------------------------------------
20137recursive calls
2020 db block gets
20381consistent gets
2040physicalreads
2050redosize
206546bytes sent via SQL*Net toclient
207512bytes received via SQL*Net fromclient
2081SQL*Net roundtrips to/fromclient
2090 sorts (memory)
2100 sorts (disk)
2110rows processed
212
213LHR@LHR11G> select * from vw2 where modify_time= '2024-03-01';
214
215no rows selected
216
217
218Execution Plan
219----------------------------------------------------------
220Plan hash value: 1470101981
221
222----------------------------------------------------------------------------
223| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
224----------------------------------------------------------------------------
225| 0 | SELECTSTATEMENT | | 19745 | 848K| 22 (0)| 00:00:01 |
226|* 1 | VIEW | VW2 | 19745 | 848K| 22 (0)| 00:00:01 |
227| 2 | COUNT | | | | | |
228| 3 | TABLEACCESSFULL| TB | 19745 | 597K| 22 (0)| 00:00:01 |
229----------------------------------------------------------------------------
230
231Predicate Information (identifiedby operation id):
232---------------------------------------------------
233
2341 - filter("MODIFY_TIME"='2024-03-01')
235
236Note
237-----
238 - dynamic sampling used for this statement (level=2)
239
240
241Statistics
242----------------------------------------------------------
24321recursive calls
2440 db block gets
245218consistent gets
2460physicalreads
2470redosize
248538bytes sent via SQL*Net toclient
249512bytes received via SQL*Net fromclient
2501SQL*Net roundtrips to/fromclient
2510 sorts (memory)
2520 sorts (disk)
2530rows processed
254
255LHR@LHR11G>
通过10053事件，发现SQL语句最终被修改成：
1-- 原SQL
2select * from vw2 where modify_time= '2024-03-01';
3
4-- 内部执行
5SELECT"VW2"."ID""ID","VW2"."MODIFY_TIME""MODIFY_TIME","VW2"."MODIFY_AT""MODIFY_AT","VW2"."RN""RN"FROM (SELECT"TB"."ID""ID","TB"."MODIFY_TIME""MODIFY_TIME",TO_DATE("TB"."MODIFY_TIME",'YYYYMMDDHH24MISS') "MODIFY_AT",ROWNUM"RN"FROM LHR."TB""TB") "VW2"WHERE"VW2"."MODIFY_TIME"='2024-03-01'
总结
1、to_date函数索引的大小写要和使用时的大小写和格式一致，否则也会不走索引
2、若视图中含有rownum伪列，则不会走索引
3、其它情况导致的不能走索引（索引失效）情况请参考：https://www.xmmup.com/oracleweihezaichaxunzhongsuoyinweibeishiyong-wendang-id-1549181-1.html
参考
https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:32812348052
https://blog.csdn.net/sharqueen_wu/article/details/42105477
https://blog.csdn.net/bisal/article/details/9713621
