这样的适不适合索引

张沛

  select * from v$version;
1        Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
2        PL/SQL Release 10.2.0.3.0 - Production
3        CORE        10.2.0.3.0        Production
4        TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
5        NLSRTL Version 10.2.0.3.0 - Production

对于一张表的查询，表共94万行数据，查询语句中where中内容
where oola.ordered_quantity - nvl(to_number(oola.attribute10), 0) > 0

54 - filter("OOLA"."ORDERED_QUANTITY"-NVL(TO_NUMBER("OOLA"."ATTRIBUTE10"),0)>0)


执行计划走的全表扫描
|* 54 |        TABLE ACCESS FULL        | OE_ORDER_LINES_ALL           | 47455 |  1714K|       | 32754   (1)| 00:06:34 |
返回了4万行数据

oola表的ORDERED_QUANTITY和ATTRIBUTE10这两列适不适合建索引，让查询的时候用索引代替全表扫面

Liu Maclean(刘相兵

请学会使用code模式编辑日志 代码 执行计划

1. 我是code模式

复制代码

Liu Maclean(刘相兵

test

1. 1* select t1,t2 from comparetab where t1<t2
   
2. SQL> /
   
3. 
   
4. 99999 rows selected.
   
5. 
   
6. 
   
7. Execution Plan
   
8. ----------------------------------------------------------
   
9. Plan hash value: 782809411
   
10. 
    
11. --------------------------------------------------------------------------------
    
12. | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
    
13. --------------------------------------------------------------------------------
    
14. |   0 | SELECT STATEMENT  |            | 87756 |  2228K|    47   (3)| 00:00:01 |
    
15. |*  1 |  TABLE ACCESS FULL| COMPARETAB | 87756 |  2228K|    47   (3)| 00:00:01 |
    
16. --------------------------------------------------------------------------------
    
17. 
    
18. Predicate Information (identified by operation id):
    
19. ---------------------------------------------------
    
20. 
    
21.    1 - filter("T1"<"T2")
    
22. 
    
23. Note
    
24. -----
    
25.    - dynamic sampling used for this statement (level=2)
    
26. 
    
27. 
    
28. Statistics
    
29. ----------------------------------------------------------
    
30.           0  recursive calls
    
31.           0  db block gets
    
32.        6824  consistent gets
    
33.          29  physical reads
    
34.           0  redo size
    
35.     1773851  bytes sent via SQL*Net to client
    
36.       73869  bytes received via SQL*Net from client
    
37.        6668  SQL*Net roundtrips to/from client
    
38.           0  sorts (memory)
    
39.           0  sorts (disk)
    
40.       99999  rows processed
    
41. 
    
42. SQL>
    
43. SQL> create index ind_compare on comparetab(t1,t2);
    
44. 
    
45. Index created.
    
46. 
    
47. SQL> select t1,t2 from comparetab where t1<t2;
    
48. 
    
49. 99999 rows selected.
    
50. 
    
51. 
    
52. Execution Plan
    
53. ----------------------------------------------------------
    
54. Plan hash value: 782809411
    
55. 
    
56. --------------------------------------------------------------------------------
    
57. | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
    
58. --------------------------------------------------------------------------------
    
59. |   0 | SELECT STATEMENT  |            | 87756 |  2228K|    47   (3)| 00:00:01 |
    
60. |*  1 |  TABLE ACCESS FULL| COMPARETAB | 87756 |  2228K|    47   (3)| 00:00:01 |
    
61. --------------------------------------------------------------------------------
    
62. 
    
63. Predicate Information (identified by operation id):
    
64. ---------------------------------------------------
    
65. 
    
66.    1 - filter("T1"<"T2")
    
67. 
    
68. Note
    
69. -----
    
70.    - dynamic sampling used for this statement (level=2)
    
71. 
    
72. 
    
73. Statistics
    
74. ----------------------------------------------------------
    
75.           5  recursive calls
    
76.           0  db block gets
    
77.        6881  consistent gets
    
78.           0  physical reads
    
79.           0  redo size
    
80.     1773851  bytes sent via SQL*Net to client
    
81.       73869  bytes received via SQL*Net from client
    
82.        6668  SQL*Net roundtrips to/from client
    
83.           0  sorts (memory)
    
84.           0  sorts (disk)
    
85.       99999  rows processed
    
86. 
    
87. SQL> alter system flush shared_pool;
    
88. 
    
89. System altered.
    
90. 
    
91. SQL> select t1,t2 from comparetab where t1<t2;
    
92. 
    
93. 99999 rows selected.
    
94. 
    
95. 
    
96. Execution Plan
    
97. ----------------------------------------------------------
    
98. Plan hash value: 782809411
    
99. 
    
100. --------------------------------------------------------------------------------
     
101. | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
     
102. --------------------------------------------------------------------------------
     
103. |   0 | SELECT STATEMENT  |            | 87756 |  2228K|    47   (3)| 00:00:01 |
     
104. |*  1 |  TABLE ACCESS FULL| COMPARETAB | 87756 |  2228K|    47   (3)| 00:00:01 |
     
105. --------------------------------------------------------------------------------
     
106. 
     
107. Predicate Information (identified by operation id):
     
108. ---------------------------------------------------
     
109. 
     
110.    1 - filter("T1"<"T2")
     
111. 
     
112. Note
     
113. -----
     
114.    - dynamic sampling used for this statement (level=2)
     
115. 
     
116. 
     
117. Statistics
     
118. ----------------------------------------------------------
     
119.          44  recursive calls
     
120.           0  db block gets
     
121.        6930  consistent gets
     
122.           1  physical reads
     
123.           0  redo size
     
124.     1773851  bytes sent via SQL*Net to client
     
125.       73869  bytes received via SQL*Net from client
     
126.        6668  SQL*Net roundtrips to/from client
     
127.           4  sorts (memory)
     
128.           0  sorts (disk)
     
129.       99999  rows processed
     
130. 
     
131. SQL> select t1,t2 from comparetab where t1>t2;
     
132. 
     
133. no rows selected
     
134. 
     
135. 
     
136. Execution Plan
     
137. ----------------------------------------------------------
     
138. Plan hash value: 782809411
     
139. 
     
140. --------------------------------------------------------------------------------
     
141. | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
     
142. --------------------------------------------------------------------------------
     
143. |   0 | SELECT STATEMENT  |            |     2 |    52 |    47   (3)| 00:00:01 |
     
144. |*  1 |  TABLE ACCESS FULL| COMPARETAB |     2 |    52 |    47   (3)| 00:00:01 |
     
145. --------------------------------------------------------------------------------
     
146. 
     
147. Predicate Information (identified by operation id):
     
148. ---------------------------------------------------
     
149. 
     
150.    1 - filter("T1">"T2")
     
151. 
     
152. Note
     
153. -----
     
154.    - dynamic sampling used for this statement (level=2)
     
155. 
     
156. 
     
157. Statistics
     
158. ----------------------------------------------------------
     
159.           5  recursive calls
     
160.           0  db block gets
     
161.         214  consistent gets
     
162.           0  physical reads
     
163.           0  redo size
     
164.         402  bytes sent via SQL*Net to client
     
165.         532  bytes received via SQL*Net from client
     
166.           1  SQL*Net roundtrips to/from client
     
167.           0  sorts (memory)
     
168.           0  sorts (disk)
     
169.           0  rows processed
     
170. 
     
171. SQL> set arraysize 5000
     
172. SQL> select t1,t2 from comparetab where t1<t2;
     
173. 
     
174. 99999 rows selected.
     
175. 
     
176. 
     
177. Execution Plan
     
178. ----------------------------------------------------------
     
179. Plan hash value: 782809411
     
180. 
     
181. --------------------------------------------------------------------------------
     
182. | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
     
183. --------------------------------------------------------------------------------
     
184. |   0 | SELECT STATEMENT  |            | 87756 |  2228K|    47   (3)| 00:00:01 |
     
185. |*  1 |  TABLE ACCESS FULL| COMPARETAB | 87756 |  2228K|    47   (3)| 00:00:01 |
     
186. --------------------------------------------------------------------------------
     
187. 
     
188. Predicate Information (identified by operation id):
     
189. ---------------------------------------------------
     
190. 
     
191.    1 - filter("T1"<"T2")
     
192. 
     
193. Note
     
194. -----
     
195.    - dynamic sampling used for this statement (level=2)
     
196. 
     
197. 
     
198. Statistics
     
199. ----------------------------------------------------------
     
200.           0  recursive calls
     
201.           0  db block gets
     
202.         177  consistent gets
     
203.           0  physical reads
     
204.           0  redo size
     
205.      504274  bytes sent via SQL*Net to client
     
206.         752  bytes received via SQL*Net from client
     
207.          21  SQL*Net roundtrips to/from client
     
208.           0  sorts (memory)
     
209.           0  sorts (disk)
     
210.       99999  rows processed
     
211. 
     
212. SQL>  select t1,t2 from comparetab where t1>t2;
     
213. 
     
214. no rows selected
     
215. 
     
216. 
     
217. Execution Plan
     
218. ----------------------------------------------------------
     
219. Plan hash value: 782809411
     
220. 
     
221. --------------------------------------------------------------------------------
     
222. | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
     
223. --------------------------------------------------------------------------------
     
224. |   0 | SELECT STATEMENT  |            |     2 |    52 |    47   (3)| 00:00:01 |
     
225. |*  1 |  TABLE ACCESS FULL| COMPARETAB |     2 |    52 |    47   (3)| 00:00:01 |
     
226. --------------------------------------------------------------------------------
     
227. 
     
228. Predicate Information (identified by operation id):
     
229. ---------------------------------------------------
     
230. 
     
231.    1 - filter("T1">"T2")
     
232. 
     
233. Note
     
234. -----
     
235.    - dynamic sampling used for this statement (level=2)
     
236. 
     
237. 
     
238. Statistics
     
239. ----------------------------------------------------------
     
240.           4  recursive calls
     
241.           0  db block gets
     
242.         214  consistent gets
     
243.           0  physical reads
     
244.           0  redo size
     
245.         402  bytes sent via SQL*Net to client
     
246.         532  bytes received via SQL*Net from client
     
247.           1  SQL*Net roundtrips to/from client
     
248.           0  sorts (memory)
     
249.           0  sorts (disk)
     
250.           0  rows processed
     
251. 
     
252. SQL>
     
253. SQL> create index fun_ind on comparetab(t1-t2);
     
254. 
     
255. Index created.
     
256. 
     
257. SQL>
     
258. SQL> select t1,t2 from comparetab where t1>t2;
     
259. 
     
260. no rows selected
     
261. 
     
262. 
     
263. Execution Plan
     
264. ----------------------------------------------------------
     
265. Plan hash value: 782809411
     
266. 
     
267. --------------------------------------------------------------------------------
     
268. | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
     
269. --------------------------------------------------------------------------------
     
270. |   0 | SELECT STATEMENT  |            |     2 |    52 |    47   (3)| 00:00:01 |
     
271. |*  1 |  TABLE ACCESS FULL| COMPARETAB |     2 |    52 |    47   (3)| 00:00:01 |
     
272. --------------------------------------------------------------------------------
     
273. 
     
274. Predicate Information (identified by operation id):
     
275. ---------------------------------------------------
     
276. 
     
277.    1 - filter("T1">"T2")
     
278. 
     
279. Note
     
280. -----
     
281.    - dynamic sampling used for this statement (level=2)
     
282. 
     
283. 
     
284. Statistics
     
285. ----------------------------------------------------------
     
286.           5  recursive calls
     
287.           0  db block gets
     
288.         215  consistent gets
     
289.           0  physical reads
     
290.           0  redo size
     
291.         402  bytes sent via SQL*Net to client
     
292.         532  bytes received via SQL*Net from client
     
293.           1  SQL*Net roundtrips to/from client
     
294.           0  sorts (memory)
     
295.           0  sorts (disk)
     
296.           0  rows processed
     
297. 
     
298. SQL>  select t1,t2 from comparetab where t1-t2>0;
     
299. 
     
300. no rows selected
     
301. 
     
302. 
     
303. Execution Plan
     
304. ----------------------------------------------------------
     
305. Plan hash value: 1278306072
     
306. 
     
307. --------------------------------------------------------------------------------------------------
     
308. | Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
     
309. --------------------------------------------------------------------------------------------------
     
310. |   0 | SELECT STATEMENT                    |            |  4388 |   167K|     5   (0)| 00:00:01 |
     
311. |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| COMPARETAB |  4388 |   167K|     5   (0)| 00:00:01 |
     
312. |*  2 |   INDEX RANGE SCAN                  | FUN_IND    |   790 |       |     3   (0)| 00:00:01 |
     
313. --------------------------------------------------------------------------------------------------
     
314. 
     
315. Predicate Information (identified by operation id):
     
316. ---------------------------------------------------
     
317. 
     
318.    2 - access("T1"-"T2">0)
     
319. 
     
320. Note
     
321. -----
     
322.    - dynamic sampling used for this statement (level=2)
     
323. 
     
324. 
     
325. Statistics
     
326. ----------------------------------------------------------
     
327.           6  recursive calls
     
328.           0  db block gets
     
329.          62  consistent gets
     
330.           1  physical reads
     
331.           0  redo size
     
332.         402  bytes sent via SQL*Net to client
     
333.         532  bytes received via SQL*Net from client
     
334.           1  SQL*Net roundtrips to/from client
     
335.           0  sorts (memory)
     
336.           0  sorts (disk)
     
337.           0  rows processed
     
338. 
     
339. SQL> select t1,t2 from comparetab where t1-t2<0;
     
340. 
     
341. 99999 rows selected.
     
342. 
     
343. 
     
344. Execution Plan
     
345. ----------------------------------------------------------
     
346. Plan hash value: 1278306072
     
347. 
     
348. --------------------------------------------------------------------------------------------------
     
349. | Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
     
350. --------------------------------------------------------------------------------------------------
     
351. |   0 | SELECT STATEMENT                    |            |  4388 |   167K|     5   (0)| 00:00:01 |
     
352. |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| COMPARETAB |  4388 |   167K|     5   (0)| 00:00:01 |
     
353. |*  2 |   INDEX RANGE SCAN                  | FUN_IND    |   790 |       |     3   (0)| 00:00:01 |
     
354. --------------------------------------------------------------------------------------------------
     
355. 
     
356. Predicate Information (identified by operation id):
     
357. ---------------------------------------------------
     
358. 
     
359.    2 - access("T1"-"T2"<0)
     
360. 
     
361. Note
     
362. -----
     
363.    - dynamic sampling used for this statement (level=2)
     
364. 
     
365. 
     
366. Statistics
     
367. ----------------------------------------------------------
     
368.           4  recursive calls
     
369.           0  db block gets
     
370.         462  consistent gets
     
371.         209  physical reads
     
372.           0  redo size
     
373.      504274  bytes sent via SQL*Net to client
     
374.         752  bytes received via SQL*Net from client
     
375.          21  SQL*Net roundtrips to/from client
     
376.           0  sorts (memory)
     
377.           0  sorts (disk)
     
378.       99999  rows processed
     

复制代码