Home » SQL & PL/SQL » SQL & PL/SQL » Huge Query
| Huge Query [message #307966] |
Thu, 20 March 2008 11:00  |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
Dear All,
I have 2 working queries, which I've been unable to merge:
The first one generates all the letters of the alphabet.
WITH AlphaBet AS
(
SELECT
CHR((ROWNUM + 64)) AS Character
FROM DUAL
CONNECT BY LEVEL <= 26
)
SELECT *
FROM AlphaBet;
..and I would like to use it to simplify the following giant query (which already produces correct results, but it's just too huge):
Can anyone help ?
RDBMS Version is 10.2.0.3.0
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'A'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'B'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'C'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'D'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'E'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'F'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'G'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'H'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'I'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'J'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'K'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'L'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'M'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'N'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'O'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'P'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'Q'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'R'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'S'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'T'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'U'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'V'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'W'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'X'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'Y'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
UNION ALL
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'Z'
ORDER BY fName,
sName,
Id)
WHERE ROWNUM <= 10
[Updated on: Thu, 20 March 2008 11:14] Report message to a moderator |
|
|
|
|
|
| Re: Huge Query [message #307995 is a reply to message #307966] |
Thu, 20 March 2008 13:44  |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
Sounds to me like you are trying to get the first 10 rows for each surname beginning with each letter of the alphabet. The query you have will give you duplicates if you have less than 10 customers for a particular letter.
I cant think you would need the duplicates, so
This is pretty much equivalent without the duplicates:-
SELECT *
FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
Row_number() OVER(PARTITION BY substr(fName,1,1) ORDER BY sName, Id) alphabet_rn,
fName,
sName,
Id
FROM Customer
WHERE fName >= 'A'
)
WHERE alphabet_rn <= 10
ORDER BY fName,
sName,
Id
|
|
|
|
| Re: Huge Query [message #307996 is a reply to message #307966] |
Thu, 20 March 2008 13:44 |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- test environment:
SCOTT@orcl_11g> DROP TABLE CUSTOMER
2 /
Table dropped.
SCOTT@orcl_11g> CREATE TABLE CUSTOMER
2 (
3 ID NUMBER(10),
4 SNAME VARCHAR2(80 BYTE) NOT NULL,
5 FNAME VARCHAR2(50 BYTE),
6 DOB DATE
7 )
8 /
Table created.
SCOTT@orcl_11g> INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB)
2 SELECT rownum, CHR(mod(rownum,26)+64) || substr(to_char(TIMESTAMP),4,14), to_char(OBJECT_NAME), LAST_DDL_TIME
3 FROM ALL_OBJECTS
4 /
68286 rows created.
SCOTT@orcl_11g> CREATE UNIQUE INDEX CUSTOMER ON CUSTOMER (FNAME, SNAME, ID)
2 /
Index created.
SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'CUSTOMER')
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> COLUMN fname FORMAT A15 WORD_WRAPPED
SCOTT@orcl_11g> COLUMN sname FORMAT A30 WORD_WRAPPED
-- original:
SCOTT@orcl_11g> SELECT *
2 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
3 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
4 fName,
5 sName,
6 Id
7 FROM Customer
8 WHERE fName >= 'A'
9 ORDER BY fName,
10 sName,
11 Id)
12 WHERE ROWNUM <= 10
13 UNION ALL
14 SELECT *
15 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
16 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
17 fName,
18 sName,
19 Id
20 FROM Customer
21 WHERE fName >= 'B'
22 ORDER BY fName,
23 sName,
24 Id)s
25 WHERE ROWNUM <= 10
26 UNION ALL
27 SELECT *
28 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
29 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
30 fName,
31 sName,
32 Id
33 FROM Customer
34 WHERE fName >= 'C'
35 ORDER BY fName,
36 sName,
37 Id)
38 WHERE ROWNUM <= 10
39 UNION ALL
40 SELECT *
41 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
42 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
43 fName,
44 sName,
45 Id
46 FROM Customer
47 WHERE fName >= 'D'
48 ORDER BY fName,
49 sName,
50 Id)
51 WHERE ROWNUM <= 10
52 UNION ALL
53 SELECT *
54 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
55 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
56 fName,
57 sName,
58 Id
59 FROM Customer
60 WHERE fName >= 'E'
61 ORDER BY fName,
62 sName,
63 Id)
64 WHERE ROWNUM <= 10
65 UNION ALL
66 SELECT *
67 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
68 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
69 fName,
70 sName,
71 Id
72 FROM Customer
73 WHERE fName >= 'F'
74 ORDER BY fName,
75 sName,
76 Id)
77 WHERE ROWNUM <= 10
78 UNION ALL
79 SELECT *
80 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
81 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
82 fName,
83 sName,
84 Id
85 FROM Customer
86 WHERE fName >= 'G'
87 ORDER BY fName,
88 sName,
89 Id)
90 WHERE ROWNUM <= 10
91 UNION ALL
92 SELECT *
93 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
94 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
95 fName,
96 sName,
97 Id
98 FROM Customer
99 WHERE fName >= 'H'
100 ORDER BY fName,
101 sName,
102 Id)
103 WHERE ROWNUM <= 10
104 UNION ALL
105 SELECT *
106 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
107 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
108 fName,
109 sName,
110 Id
111 FROM Customer
112 WHERE fName >= 'I'
113 ORDER BY fName,
114 sName,
115 Id)
116 WHERE ROWNUM <= 10
117 UNION ALL
118 SELECT *
119 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
120 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
121 fName,
122 sName,
123 Id
124 FROM Customer
125 WHERE fName >= 'J'
126 ORDER BY fName,
127 sName,
128 Id)
129 WHERE ROWNUM <= 10
130 UNION ALL
131 SELECT *
132 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
133 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
134 fName,
135 sName,
136 Id
137 FROM Customer
138 WHERE fName >= 'K'
139 ORDER BY fName,
140 sName,
141 Id)
142 WHERE ROWNUM <= 10
143 UNION ALL
144 SELECT *
145 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
146 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
147 fName,
148 sName,
149 Id
150 FROM Customer
151 WHERE fName >= 'L'
152 ORDER BY fName,
153 sName,
154 Id)
155 WHERE ROWNUM <= 10
156 UNION ALL
157 SELECT *
158 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
159 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
160 fName,
161 sName,
162 Id
163 FROM Customer
164 WHERE fName >= 'M'
165 ORDER BY fName,
166 sName,
167 Id)
168 WHERE ROWNUM <= 10
169 UNION ALL
170 SELECT *
171 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
172 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
173 fName,
174 sName,
175 Id
176 FROM Customer
177 WHERE fName >= 'N'
178 ORDER BY fName,
179 sName,
180 Id)
181 WHERE ROWNUM <= 10
182 UNION ALL
183 SELECT *
184 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
185 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
186 fName,
187 sName,
188 Id
189 FROM Customer
190 WHERE fName >= 'O'
191 ORDER BY fName,
192 sName,
193 Id)
194 WHERE ROWNUM <= 10
195 UNION ALL
196 SELECT *
197 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
198 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
199 fName,
200 sName,
201 Id
202 FROM Customer
203 WHERE fName >= 'P'
204 ORDER BY fName,
205 sName,
206 Id)
207 WHERE ROWNUM <= 10
208 UNION ALL
209 SELECT *
210 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
211 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
212 fName,
213 sName,
214 Id
215 FROM Customer
216 WHERE fName >= 'Q'
217 ORDER BY fName,
218 sName,
219 Id)
220 WHERE ROWNUM <= 10
221 UNION ALL
222 SELECT *
223 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
224 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
225 fName,
226 sName,
227 Id
228 FROM Customer
229 WHERE fName >= 'R'
230 ORDER BY fName,
231 sName,
232 Id)
233 WHERE ROWNUM <= 10
234 UNION ALL
235 SELECT *
236 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
237 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
238 fName,
239 sName,
240 Id
241 FROM Customer
242 WHERE fName >= 'S'
243 ORDER BY fName,
244 sName,
245 Id)
246 WHERE ROWNUM <= 10
247 UNION ALL
248 SELECT *
249 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
250 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
251 fName,
252 sName,
253 Id
254 FROM Customer
255 WHERE fName >= 'T'
256 ORDER BY fName,
257 sName,
258 Id)
259 WHERE ROWNUM <= 10
260 UNION ALL
261 SELECT *
262 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
263 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
264 fName,
265 sName,
266 Id
267 FROM Customer
268 WHERE fName >= 'U'
269 ORDER BY fName,
270 sName,
271 Id)
272 WHERE ROWNUM <= 10
273 UNION ALL
274 SELECT *
275 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
276 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
277 fName,
278 sName,
279 Id
280 FROM Customer
281 WHERE fName >= 'V'
282 ORDER BY fName,
283 sName,
284 Id)
285 WHERE ROWNUM <= 10
286 UNION ALL
287 SELECT *
288 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
289 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
290 fName,
291 sName,
292 Id
293 FROM Customer
294 WHERE fName >= 'W'
295 ORDER BY fName,
296 sName,
297 Id)
298 WHERE ROWNUM <= 10
299 UNION ALL
300 SELECT *
301 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
302 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
303 fName,
304 sName,
305 Id
306 FROM Customer
307 WHERE fName >= 'X'
308 ORDER BY fName,
309 sName,
310 Id)
311 WHERE ROWNUM <= 10
312 UNION ALL
313 SELECT *
314 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
315 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
316 fName,
317 sName,
318 Id
319 FROM Customer
320 WHERE fName >= 'Y'
321 ORDER BY fName,
322 sName,
323 Id)
324 WHERE ROWNUM <= 10
325 UNION ALL
326 SELECT *
327 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
328 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
329 fName,
330 sName,
331 Id
332 FROM Customer
333 WHERE fName >= 'Z'
334 ORDER BY fName,
335 sName,
336 Id)
337 WHERE ROWNUM <= 10
338 /
I FNAME SNAME ID
---------- --------------- ------------------------------ ----------
1 A6-04-19:10:56: ORDDATASOURCE 54835
1 A7-10-15:10:09: ALL_CATALOG 2809
1 A7-10-15:10:09: ALL_TAB_COLS 2939
1 A7-10-15:10:09: CACHE_STATS_0$ 391
1 A7-10-15:10:09: CDC_SUBSCRIBED_TABLES$ 599
1 A7-10-15:10:09: CLU$ 27
1 A7-10-15:10:09: CMP_ROW_DIF_UNIQ_IDX_1 729
1 A7-10-15:10:09: DATABASE_COMPATIBLE_LEVEL 2575
1 A7-10-15:10:09: DBA_COL_PRIVS 2835
1 A7-10-15:10:09: DBA_IND_COLUMNS 2861
1 B7-10-15:10:09: ALL_CATALOG 2810
1 B7-10-15:10:09: CMP_ROW_DIF_UNIQ_IDX_2 730
1 B7-10-15:10:09: DATABASE_COMPATIBLE_LEVEL 2576
1 B7-10-15:10:09: DBA_IND_COLUMNS 2862
1 B7-10-15:10:09: DBA_TAB_COLS 2940
1 B7-10-15:10:09: DIM$ 834
1 B7-10-15:10:09: DIR$ 444
1 B7-10-15:10:09: DIR$SERVICE_OPERATIONS 288
1 B7-10-15:10:09: EXPDEPACT$ 860
1 B7-10-15:10:09: GV$LOGMNR_PARAMETERS 2212
1 C1-12-06:13:00: SDO_GEORASTER 61285
1 C7-04-12:12:59: SQL_PLAN_STAT_ROW_TYPE 4683
1 C7-10-15:10:09: ALL_SYNONYMS 2915
1 C7-10-15:10:09: ALL_TAB_PRIVS_RECD 2967
1 C7-10-15:10:09: CACHE_STATS_SEQ_0 393
1 C7-10-15:10:09: CDC_SUBSCRIBED_COLUMNS$ 601
1 C7-10-15:10:09: COL 2785
1 C7-10-15:10:09: COLTYPE$ 81
1 C7-10-15:10:09: COMPARISON_SEQ$ 731
1 C7-10-15:10:09: CON$ 3
1 D1-12-06:13:00: SDO_RASTER 61286
1 D3-06-10:13:37: ORDIMAGESIGNATURE 54838
1 D3-12-25:09:11: SDO_TOPO_NSTD_TBL 58322
1 D7-10-15:10:09: ALL_COL_PRIVS_MADE 2838
1 D7-10-15:10:09: ALL_OBJECTS_AE 2890
1 D7-10-15:10:09: ALL_SYNONYMS 2916
1 D7-10-15:10:09: ALL_TAB_PRIVS_RECD 2968
1 D7-10-15:10:09: APPLY$_DEST_OBJ_ID 654
1 D7-10-15:10:09: ASSOCIATION$ 394
1 D7-10-15:10:09: ATTRCOL$ 446
1 E0-08-26:11:25: SDO_MBR 58193
1 E1-01-08:11:44: ORDDOC 54865
1 E1-12-06:13:00: SDO_RASTERSET 61287
1 E3-05-20:10:08: WM$NV_PAIR_TYPE 11471
1 E7-04-12:12:59: SQL_PLAN_ALLSTAT_ROW_TYPE 4685
1 E7-10-15:10:09: ALL_COL_PRIVS_MADE 2839
1 E7-10-15:10:09: ALL_IND_EXPRESSIONS 2865
1 E7-10-15:10:09: ALL_OBJECTS_AE 2891
1 E7-10-15:10:09: ALL_XML_SCHEMAS2 1071
1 E7-10-15:10:09: APPLY$_DEST_OBJ_CMAP 655
1 F1-07-29:12:08: WM$ED_UNDO_CODE_NODE_TYPE 11420
1 F1-12-06:13:00: SDO_GEOR_METADATA 61288
1 F3-05-20:10:08: WM$NV_PAIR_NT_TYPE 11472
1 F6-04-19:10:55: ORDDICOM 54866
1 F7-10-15:10:09: ALL_IND_EXPRESSIONS 2866
1 F7-10-15:10:09: ASSOC2 396
1 F7-10-15:10:09: BINARY_DOUBLE 968
1 F7-10-15:10:09: CDC_CHANGE_COLUMNS$ 604
1 F7-10-15:10:09: COLS 2944
1 F7-10-15:10:09: DBA_OBJECTS_AE 2892
1 G1-07-29:12:08: WM$ED_UNDO_CODE_TABLE_TYPE 11421
1 G1-12-06:13:00: SDO_GEOR_SRS 61289
1 G2-07-18:08:04: SI_COLOR 54841
1 G3-05-20:10:08: WM$EVENT_TYPE 11473
1 G7-10-15:10:09: ALL_TAB_COLUMNS 2945
1 G7-10-15:10:09: ALL_VIEWS 2971
1 G7-10-15:10:09: APPLY$_DEST_OBJ_OPS 657
1 G7-10-15:10:09: BINARY_FLOAT 969
1 G7-10-15:10:09: CLU 2815
1 G7-10-15:10:09: DBA_IND_EXPRESSIONS 2867
1 H2-10-14:18:00: SDO_SMPL_GEOMETRY 58196
1 H3-05-21:10:52: WM_PERIOD 11474
1 H7-10-15:10:09: ALL_CLUSTERS 2816
1 H7-10-15:10:09: ALL_COL_PRIVS_RECD 2842
1 H7-10-15:10:09: ALL_TAB_COLUMNS 2946
1 H7-10-15:10:09: ALL_VIEWS 2972
1 H7-10-15:10:09: BLOB 970
1 H7-10-15:10:09: CDC_RSID_SEQ$ 606
1 H7-10-15:10:09: CONTEXT$ 138
1 H7-10-15:10:09: DBA_IND_EXPRESSIONS 2868
1 I1-12-06:13:00: SDO_GEOR_HISTOGRAM 61291
1 I2-10-01:12:41: SDO_REGION 58197
1 I6-02-16:11:08: SDO_ORGSCL_TYPE 61655
1 I7-10-15:10:09: ALL_CLUSTERS 2817
1 I7-10-15:10:09: ALL_COL_PRIVS_RECD 2843
1 I7-10-15:10:09: APPLY$_ERROR 659
1 I7-10-15:10:09: ATEMPTAB$ 165
1 I7-10-15:10:09: CANONICAL 971
1 I7-10-15:10:09: CDC_PROPAGATIONS$ 607
1 I7-10-15:10:09: DBA_ROLLBACK_SEGS 2895
1 J1-12-06:13:00: SDO_GEOR_GRAYSCALE 61292
1 J2-10-01:12:46: SDO_REGIONSET 58198
1 J7-10-15:10:09: ATEMPIND$ 166
1 J7-10-15:10:09: CFILE 972
1 J7-10-15:10:09: C_MLOG# 556
1 J7-10-15:10:09: C_RG# 582
1 J7-10-15:10:09: C_TOID_VERSION# 452
1 J7-10-15:10:09: DBA_CLUSTERS 2818
1 J7-10-15:10:09: DBA_ENCRYPTED_COLUMNS 2844
1 J7-10-15:10:09: DBA_TAB_COLUMNS 2948
1 K1-12-06:13:00: SDO_GEOR_COLORMAP 61293
1 K6-02-16:10:48: SDO_PC_BLK 61657
1 K6-04-18:00:00: STANDARD 1051
1 K7-04-12:12:59: SQLPROF_ATTR 5679
1 K7-10-15:10:09: ALL_CONS_COLUMNS 2767
1 K7-10-15:10:09: APPLY$_ERROR_TXN 661
1 K7-10-15:10:09: ARGUMENT$ 193
1 K7-10-15:10:09: CDC_PROPAGATED_SETS$ 609
1 K7-10-15:10:09: CHAR 973
1 K7-10-15:10:09: C_FILE#_BLOCK# 37
1 L1-07-29:12:06: WM$LOCK_INFO_TYPE 11400
1 L7-10-15:10:09: ALL_CONS_COLUMNS 2768
1 L7-10-15:10:09: ALL_ENCRYPTED_COLUMNS 2846
1 L7-10-15:10:09: ALL_TABLES 2924
1 L7-10-15:10:09: CLOB 974
1 L7-10-15:10:09: C_USER# 38
1 L7-10-15:10:09: DBA_ROLE_PRIVS 2898
1 L7-10-15:10:09: DEFROLE$ 116
1 L7-10-15:10:09: DIMATTR$ 844
1 L7-10-15:10:09: DIR$QUIESCE_OPERATIONS 298
1 M1-07-29:12:06: WM$LOCK_TABLE_TYPE 11401
1 M1-12-06:13:00: SDO_NUMBER_ARRAY 58201
1 M2-10-23:15:20: AQ$_JMS_MESSAGES 7995
1 M7-04-12:12:59: TSM$SESSION_ID 5005
1 M7-10-15:10:09: ALL_ENCRYPTED_COLUMNS 2847
1 M7-10-15:10:09: ALL_TABLES 2925
1 M7-10-15:10:09: ALL_TAB_COMMENTS 2951
1 M7-10-15:10:09: APPLY$_ERROR_HANDLER_SEQUENCE 663
1 M7-10-15:10:09: ASSEMBLY$ 221
1 M7-10-15:10:09: AUD$ 351
1 N1-12-06:13:00: SDO_STRING_ARRAY 58202
1 N2-07-18:08:04: SI_STILLIMAGE 54848
1 N2-10-23:15:20: AQ$_JMS_TEXT_MESSAGES 7996
1 N7-04-12:12:59: TSM$SESSION_ID_LIST 5006
1 N7-10-15:10:09: ALL_OBJECT_TABLES 2926
1 N7-10-15:10:09: ALL_TAB_COMMENTS 2952
1 N7-10-15:10:09: APPLY$_ERROR_HANDLER 664
1 N7-10-15:10:09: AW_IND$ 742
1 N7-10-15:10:09: BOOTSTRAP$ 40
1 N7-10-15:10:09: DATE 976
1 O2-07-17:16:54: SDO_TOPO_GEOMETRY 58333
1 O2-07-18:08:04: SI_AVERAGECOLOR 54849
1 O2-10-23:15:20: AQ$_JMS_BYTES_MESSAGES 7997
1 O6-08-06:16:01: SDO_PC_BLK_TYPE 61661
1 O7-10-15:10:09: ALL_JOIN_IND_COLUMNS 2875
1 O7-10-15:10:09: ALL_OBJECT_TABLES 2927
1 O7-10-15:10:09: APPLY$_ERROR_HANDLER_UNQ 665
1 O7-10-15:10:09: APPLY$_SOURCE_OBJ 639
1 O7-10-15:10:09: DBA_CLU_COLUMNS 2823
1 O7-10-15:10:09: DBA_TAB_COMMENTS 2953
1 P1-07-29:12:06: WM$CONFLICT_PAYLOAD_TYPE 11430
1 P2-07-18:08:04: SI_COLORHISTOGRAM 54850
1 P2-10-23:15:20: AQ$_JMS_MAP_MESSAGES 7998
1 P6-02-16:10:51: SDO_PC 61662
1 P7-04-12:12:59: AQ$_AGENT 5190
1 P7-10-15:10:09: ALL_ALL_TABLES 2928
1 P7-10-15:10:09: ALL_JOIN_IND_COLUMNS 2876
1 P7-10-15:10:09: APPLY$_CONF_HDLR_COLUMNS 666
1 P7-10-15:10:09: COLUMN_PRIVILEGES 2798
1 P7-10-15:10:09: DBA_TAB_COMMENTS 2954
1 Q2-07-18:08:04: SI_POSITIONALCOLOR 54851
1 Q2-10-23:15:20: AQ$_JMS_STREAM_MESSAGES 7999
1 Q6-02-03:13:00: SDO_STRING2_ARRAY 61195
1 Q7-04-12:12:59: AQ$_DEQUEUE_HISTORY 5191
1 Q7-10-15:10:09: ALL_ALL_TABLES 2929
1 Q7-10-15:10:09: ALL_LOG_GROUP_COLUMNS 2773
1 Q7-10-15:10:09: ALL_SEQUENCES 2903
1 Q7-10-15:10:09: APPLY$_CONF_HDLR_COLUMNS_UNQ1 667
1 Q7-10-15:10:09: AUDSES$ 329
1 Q7-10-15:10:09: CCOL$ 43
1 R2-07-18:08:04: SI_TEXTURE 54852
1 R2-10-23:15:20: AQ$_JMS_OBJECT_MESSAGES 8000
1 R6-02-03:13:00: SDO_STRING2_ARRAYSET 61196
1 R6-05-31:09:18: SDO_TIN_BLK 61664
1 R7-04-12:12:59: AQ$_SUBSCRIBERS 5192
1 R7-04-12:12:59: SQL_BIND 5634
1 R7-10-15:10:09: ALL_COL_COMMENTS 2826
1 R7-10-15:10:09: ALL_LOG_GROUP_COLUMNS 2774
1 R7-10-15:10:09: ALL_SEQUENCES 2904
1 R7-10-15:10:09: APPLY$_CONF_HDLR_COLUMNS_UNQ2 668
1 S2-07-18:08:04: SI_FEATURELIST 54853
1 S2-10-23:15:30: AQ$_JMS_MESSAGE_PROPERTY 8001
1 S7-04-12:12:59: AQ$_RECIPIENTS 5193
1 S7-04-12:12:59: RE$NV_NODE 4751
1 S7-10-15:10:09: ALL_COL_COMMENTS 2827
1 S7-10-15:10:09: ALL_INDEXES 2853
1 S7-10-15:10:09: ALL_TAB_PRIVS 2957
1 S7-10-15:10:09: APPLY$_SOURCE_SCHEMA 643
1 S7-10-15:10:09: AW_OBJ$ 747
1 S7-10-15:10:09: CDC_CHANGE_SOURCES$ 591
1 T2-10-23:15:31: AQ$_JMS_MESSAGE_PROPERTIES 8002
1 T7-04-12:12:59: AQ$_HISTORY 5194
1 T7-04-12:12:59: RE$NV_ARRAY 4752
1 T7-04-12:12:59: SQL_BIND_SET 5636
1 T7-04-12:12:59: STREAMS$NV_NODE 9900
1 T7-10-15:10:09: ALL_INDEXES 2854
1 T7-10-15:10:09: ALL_LOBS 2802
1 T7-10-15:10:09: ALL_TAB_PRIVS 2958
1 T7-10-15:10:09: CLUSTER_DATABASES 280
1 T7-10-15:10:09: COLLECTION$ 462
1 U2-10-23:15:33: AQ$_JMS_ARRAY_MSGID_INFO 8003
1 U7-04-12:12:59: AQ$_DEQUEUE_HISTORY_T 5195
1 U7-04-12:12:59: STREAMS$NV_ARRAY 9901
1 U7-10-15:10:09: ALL_LOBS 2803
1 U7-10-15:10:09: APPLY$_VIRTUAL_OBJ_CONS 645
1 U7-10-15:10:09: APPROLE$ 359
1 U7-10-15:10:09: AW_PROP$ 749
1 U7-10-15:10:09: CDC_CHANGE_SETS$ 593
1 U7-10-15:10:09: CLUSTER_NODES 281
1 U7-10-15:10:09: CMPCOL_UNIQ_IDX1 723
1 V2-07-17:16:42: SDO_TOPO_GEOMETRY_LAYER 58314
1 V2-10-23:15:34: AQ$_JMS_ARRAY_MSGIDS 8004
1 V6-08-06:16:05: SDO_TIN_BLK_TYPE 61668
1 V7-04-12:12:59: SQL_PLAN_ROW_TYPE 4676
1 V7-04-12:12:59: STREAMS$TRANSFORMATION_INFO 9902
1 V7-10-15:10:09: ALL_OBJECTS 2882
1 V7-10-15:10:09: CLUSTER_INSTANCES 282
1 V7-10-15:10:09: COLLELEMIND 464
1 V7-10-15:10:09: COMPARISON_SCAN$ 724
1 V7-10-15:10:09: C_TS# 22
1 W0-09-28:12:59: AQ$_SIG_PROP 5197
1 W1-09-26:18:27: RE$NV_LIST 4755
1 W2-10-23:15:33: AQ$_JMS_ARRAY_ERROR_INFO 8005
1 W6-01-09:10:00: SDO_RANGE 58211
1 W6-02-16:10:52: SDO_TIN 61669
1 W7-04-09:14:32: ST_ANNOTATIONTEXTELEMENT 58887
1 W7-10-15:10:09: ALL_OBJECTS 2883
1 W7-10-15:10:09: APPLY$_CONSTRAINT_COLUMNS 647
1 W7-10-15:10:09: ATTRIBUTE$ 465
1 W7-10-15:10:09: AW_TRACK$ 751
1 X2-07-17:16:45: SDO_TOPO_GEOMETRY_LAYER_ARRAY 58316
1 X2-10-23:15:34: AQ$_JMS_ARRAY_ERRORS 8006
1 X6-01-09:10:00: SDO_RANGE_ARRAY 58212
1 X7-04-12:12:59: SQL_PLAN_TABLE_TYPE 4678
1 X7-05-02:14:39: ST_ANNOT_TEXTELEMENT_ARRAY 58888
1 X7-10-15:10:09: ALL_COL_PRIVS 2832
1 X7-10-15:10:09: APPLY$_CONSTRAINT_COLUMNS_UIX1 648
1 X7-10-15:10:09: CACHE_STATS_1$ 388
1 X7-10-15:10:09: CATALOG 2780
1 X7-10-15:10:09: COL$ 24
1 Y7-04-09:14:30: ST_ANNOTATIONTEXTELEMENT_ARRAY 58889
1 Y7-10-15:10:09: ACCESS$ 103
1 Y7-10-15:10:09: ALL_COL_PRIVS 2833
1 Y7-10-15:10:09: ALL_IND_COLUMNS 2859
1 Y7-10-15:10:09: ALL_TAB_PRIVS_MADE 2963
1 Y7-10-15:10:09: APPLY$_CONSTRAINT_COLUMNS_IDX1 649
1 Y7-10-15:10:09: AW_PRG$ 753
1 Y7-10-15:10:09: CATALOG 2781
1 Y7-10-15:10:09: CDC_SUBSCRIBERS$ 597
1 Y7-10-15:10:09: CDEF$ 51
250 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3520074619
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 260 | 24700 | 6516 (93)| 00:01:19 |
| 1 | UNION-ALL | | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 66125 | 6134K| 514 (1)| 00:00:07 |
| 4 | WINDOW NOSORT | | 66125 | 2970K| 514 (1)| 00:00:07 |
|* 5 | INDEX RANGE SCAN| CUSTOMER | 66125 | 2970K| 514 (1)| 00:00:07 |
|* 6 | COUNT STOPKEY | | | | | |
| 7 | VIEW | | 63396 | 5881K| 493 (1)| 00:00:06 |
| 8 | WINDOW NOSORT | | 63396 | 2847K| 493 (1)| 00:00:06 |
|* 9 | INDEX RANGE SCAN| CUSTOMER | 63396 | 2847K| 493 (1)| 00:00:06 |
|* 10 | COUNT STOPKEY | | | | | |
| 11 | VIEW | | 60667 | 5628K| 472 (1)| 00:00:06 |
| 12 | WINDOW NOSORT | | 60667 | 2725K| 472 (1)| 00:00:06 |
|* 13 | INDEX RANGE SCAN| CUSTOMER | 60667 | 2725K| 472 (1)| 00:00:06 |
|* 14 | COUNT STOPKEY | | | | | |
| 15 | VIEW | | 57939 | 5375K| 451 (1)| 00:00:06 |
| 16 | WINDOW NOSORT | | 57939 | 2602K| 451 (1)| 00:00:06 |
|* 17 | INDEX RANGE SCAN| CUSTOMER | 57939 | 2602K| 451 (1)| 00:00:06 |
|* 18 | COUNT STOPKEY | | | | | |
| 19 | VIEW | | 55210 | 5122K| 430 (1)| 00:00:06 |
| 20 | WINDOW NOSORT | | 55210 | 2480K| 430 (1)| 00:00:06 |
|* 21 | INDEX RANGE SCAN| CUSTOMER | 55210 | 2480K| 430 (1)| 00:00:06 |
|* 22 | COUNT STOPKEY | | | | | |
| 23 | VIEW | | 52481 | 4868K| 409 (1)| 00:00:05 |
| 24 | WINDOW NOSORT | | 52481 | 2357K| 409 (1)| 00:00:05 |
|* 25 | INDEX RANGE SCAN| CUSTOMER | 52481 | 2357K| 409 (1)| 00:00:05 |
|* 26 | COUNT STOPKEY | | | | | |
| 27 | VIEW | | 49752 | 4615K| 387 (1)| 00:00:05 |
| 28 | WINDOW NOSORT | | 49752 | 2234K| 387 (1)| 00:00:05 |
|* 29 | INDEX RANGE SCAN| CUSTOMER | 49752 | 2234K| 387 (1)| 00:00:05 |
|* 30 | COUNT STOPKEY | | | | | |
| 31 | VIEW | | 47023 | 4362K| 366 (1)| 00:00:05 |
| 32 | WINDOW NOSORT | | 47023 | 2112K| 366 (1)| 00:00:05 |
|* 33 | INDEX RANGE SCAN| CUSTOMER | 47023 | 2112K| 366 (1)| 00:00:05 |
|* 34 | COUNT STOPKEY | | | | | |
| 35 | VIEW | | 44294 | 4109K| 345 (1)| 00:00:05 |
| 36 | WINDOW NOSORT | | 44294 | 1989K| 345 (1)| 00:00:05 |
|* 37 | INDEX RANGE SCAN| CUSTOMER | 44294 | 1989K| 345 (1)| 00:00:05 |
|* 38 | COUNT STOPKEY | | | | | |
| 39 | VIEW | | 41565 | 3856K| 323 (0)| 00:00:04 |
| 40 | WINDOW NOSORT | | 41565 | 1867K| 323 (0)| 00:00:04 |
|* 41 | INDEX RANGE SCAN| CUSTOMER | 41565 | 1867K| 323 (0)| 00:00:04 |
|* 42 | COUNT STOPKEY | | | | | |
| 43 | VIEW | | 38836 | 3602K| 302 (0)| 00:00:04 |
| 44 | WINDOW NOSORT | | 38836 | 1744K| 302 (0)| 00:00:04 |
|* 45 | INDEX RANGE SCAN| CUSTOMER | 38836 | 1744K| 302 (0)| 00:00:04 |
|* 46 | COUNT STOPKEY | | | | | |
| 47 | VIEW | | 36108 | 3349K| 281 (0)| 00:00:04 |
| 48 | WINDOW NOSORT | | 36108 | 1622K| 281 (0)| 00:00:04 |
|* 49 | INDEX RANGE SCAN| CUSTOMER | 36108 | 1622K| 281 (0)| 00:00:04 |
|* 50 | COUNT STOPKEY | | | | | |
| 51 | VIEW | | 33379 | 3096K| 260 (0)| 00:00:04 |
| 52 | WINDOW NOSORT | | 33379 | 1499K| 260 (0)| 00:00:04 |
|* 53 | INDEX RANGE SCAN| CUSTOMER | 33379 | 1499K| 260 (0)| 00:00:04 |
|* 54 | COUNT STOPKEY | | | | | |
| 55 | VIEW | | 30650 | 2843K| 239 (0)| 00:00:03 |
| 56 | WINDOW NOSORT | | 30650 | 1376K| 239 (0)| 00:00:03 |
|* 57 | INDEX RANGE SCAN| CUSTOMER | 30650 | 1376K| 239 (0)| 00:00:03 |
|* 58 | COUNT STOPKEY | | | | | |
| 59 | VIEW | | 27921 | 2590K| 218 (0)| 00:00:03 |
| 60 | WINDOW NOSORT | | 27921 | 1254K| 218 (0)| 00:00:03 |
|* 61 | INDEX RANGE SCAN| CUSTOMER | 27921 | 1254K| 218 (0)| 00:00:03 |
|* 62 | COUNT STOPKEY | | | | | |
| 63 | VIEW | | 25192 | 2337K| 197 (0)| 00:00:03 |
| 64 | WINDOW NOSORT | | 25192 | 1131K| 197 (0)| 00:00:03 |
|* 65 | INDEX RANGE SCAN| CUSTOMER | 25192 | 1131K| 197 (0)| 00:00:03 |
|* 66 | COUNT STOPKEY | | | | | |
| 67 | VIEW | | 22463 | 2083K| 176 (0)| 00:00:03 |
| 68 | WINDOW NOSORT | | 22463 | 1009K| 176 (0)| 00:00:03 |
|* 69 | INDEX RANGE SCAN| CUSTOMER | 22463 | 1009K| 176 (0)| 00:00:03 |
|* 70 | COUNT STOPKEY | | | | | |
| 71 | VIEW | | 19734 | 1830K| 155 (0)| 00:00:02 |
| 72 | WINDOW NOSORT | | 19734 | 886K| 155 (0)| 00:00:02 |
|* 73 | INDEX RANGE SCAN| CUSTOMER | 19734 | 886K| 155 (0)| 00:00:02 |
|* 74 | COUNT STOPKEY | | | | | |
| 75 | VIEW | | 17005 | 1577K| 134 (0)| 00:00:02 |
| 76 | WINDOW NOSORT | | 17005 | 763K| 134 (0)| 00:00:02 |
|* 77 | INDEX RANGE SCAN| CUSTOMER | 17005 | 763K| 134 (0)| 00:00:02 |
|* 78 | COUNT STOPKEY | | | | | |
| 79 | VIEW | | 14276 | 1324K| 113 (0)| 00:00:02 |
| 80 | WINDOW NOSORT | | 14276 | 641K| 113 (0)| 00:00:02 |
|* 81 | INDEX RANGE SCAN| CUSTOMER | 14276 | 641K| 113 (0)| 00:00:02 |
|* 82 | COUNT STOPKEY | | | | | |
| 83 | VIEW | | 11548 | 1071K| 92 (0)| 00:00:02 |
| 84 | WINDOW NOSORT | | 11548 | 518K| 92 (0)| 00:00:02 |
|* 85 | INDEX RANGE SCAN| CUSTOMER | 11548 | 518K| 92 (0)| 00:00:02 |
|* 86 | COUNT STOPKEY | | | | | |
| 87 | VIEW | | 8819 | 818K| 71 (0)| 00:00:01 |
| 88 | WINDOW NOSORT | | 8819 | 396K| 71 (0)| 00:00:01 |
|* 89 | INDEX RANGE SCAN| CUSTOMER | 8819 | 396K| 71 (0)| 00:00:01 |
|* 90 | COUNT STOPKEY | | | | | |
| 91 | VIEW | | 6090 | 564K| 49 (0)| 00:00:01 |
| 92 | WINDOW NOSORT | | 6090 | 273K| 49 (0)| 00:00:01 |
|* 93 | INDEX RANGE SCAN| CUSTOMER | 6090 | 273K| 49 (0)| 00:00:01 |
|* 94 | COUNT STOPKEY | | | | | |
| 95 | VIEW | | 3361 | 311K| 28 (0)| 00:00:01 |
| 96 | WINDOW NOSORT | | 3361 | 150K| 28 (0)| 00:00:01 |
|* 97 | INDEX RANGE SCAN| CUSTOMER | 3361 | 150K| 28 (0)| 00:00:01 |
|* 98 | COUNT STOPKEY | | | | | |
| 99 | VIEW | | 632 | 60040 | 7 (0)| 00:00:01 |
| 100 | WINDOW NOSORT | | 632 | 29072 | 7 (0)| 00:00:01 |
|*101 | INDEX RANGE SCAN| CUSTOMER | 632 | 29072 | 7 (0)| 00:00:01 |
|*102 | COUNT STOPKEY | | | | | |
| 103 | VIEW | | 32 | 3040 | 3 (0)| 00:00:01 |
| 104 | WINDOW NOSORT | | 32 | 1472 | 3 (0)| 00:00:01 |
|*105 | INDEX RANGE SCAN| CUSTOMER | 32 | 1472 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=10)
5 - access("FNAME">='A' AND "FNAME" IS NOT NULL)
6 - filter(ROWNUM<=10)
9 - access("FNAME">='B' AND "FNAME" IS NOT NULL)
10 - filter(ROWNUM<=10)
13 - access("FNAME">='C' AND "FNAME" IS NOT NULL)
14 - filter(ROWNUM<=10)
17 - access("FNAME">='D' AND "FNAME" IS NOT NULL)
18 - filter(ROWNUM<=10)
21 - access("FNAME">='E' AND "FNAME" IS NOT NULL)
22 - filter(ROWNUM<=10)
25 - access("FNAME">='F' AND "FNAME" IS NOT NULL)
26 - filter(ROWNUM<=10)
29 - access("FNAME">='G' AND "FNAME" IS NOT NULL)
30 - filter(ROWNUM<=10)
33 - access("FNAME">='H' AND "FNAME" IS NOT NULL)
34 - filter(ROWNUM<=10)
37 - access("FNAME">='I' AND "FNAME" IS NOT NULL)
38 - filter(ROWNUM<=10)
41 - access("FNAME">='J' AND "FNAME" IS NOT NULL)
42 - filter(ROWNUM<=10)
45 - access("FNAME">='K' AND "FNAME" IS NOT NULL)
46 - filter(ROWNUM<=10)
49 - access("FNAME">='L' AND "FNAME" IS NOT NULL)
50 - filter(ROWNUM<=10)
53 - access("FNAME">='M' AND "FNAME" IS NOT NULL)
54 - filter(ROWNUM<=10)
57 - access("FNAME">='N' AND "FNAME" IS NOT NULL)
58 - filter(ROWNUM<=10)
61 - access("FNAME">='O' AND "FNAME" IS NOT NULL)
62 - filter(ROWNUM<=10)
65 - access("FNAME">='P' AND "FNAME" IS NOT NULL)
66 - filter(ROWNUM<=10)
69 - access("FNAME">='Q' AND "FNAME" IS NOT NULL)
70 - filter(ROWNUM<=10)
73 - access("FNAME">='R' AND "FNAME" IS NOT NULL)
74 - filter(ROWNUM<=10)
77 - access("FNAME">='S' AND "FNAME" IS NOT NULL)
78 - filter(ROWNUM<=10)
81 - access("FNAME">='T' AND "FNAME" IS NOT NULL)
82 - filter(ROWNUM<=10)
85 - access("FNAME">='U' AND "FNAME" IS NOT NULL)
86 - filter(ROWNUM<=10)
89 - access("FNAME">='V' AND "FNAME" IS NOT NULL)
90 - filter(ROWNUM<=10)
93 - access("FNAME">='W' AND "FNAME" IS NOT NULL)
94 - filter(ROWNUM<=10)
97 - access("FNAME">='X' AND "FNAME" IS NOT NULL)
98 - filter(ROWNUM<=10)
101 - access("FNAME">='Y' AND "FNAME" IS NOT NULL)
102 - filter(ROWNUM<=10)
105 - access("FNAME">='Z' AND "FNAME" IS NOT NULL)
-- simplified:
SCOTT@orcl_11g> WITH AlphaBet AS
2 (SELECT CHR(ROWNUM + 64) AS Character
3 FROM DUAL
4 CONNECT BY LEVEL <= 26)
5 SELECT fname, sname, id
6 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
7 Row_number() OVER(PARTITION BY Character ORDER BY fname, sname, Id) i,
8 fName, sName, Id, Alphabet.Character
9 FROM Customer, Alphabet
10 WHERE fName >= Alphabet.Character)
11 WHERE i <= 10
12 ORDER BY character, fName, sName, Id
13 /
FNAME SNAME ID
--------------- ------------------------------ ----------
A6-04-19:10:56: ORDDATASOURCE 54835
A7-10-15:10:09: ALL_CATALOG 2809
A7-10-15:10:09: ALL_TAB_COLS 2939
A7-10-15:10:09: CACHE_STATS_0$ 391
A7-10-15:10:09: CDC_SUBSCRIBED_TABLES$ 599
A7-10-15:10:09: CLU$ 27
A7-10-15:10:09: CMP_ROW_DIF_UNIQ_IDX_1 729
A7-10-15:10:09: DATABASE_COMPATIBLE_LEVEL 2575
A7-10-15:10:09: DBA_COL_PRIVS 2835
A7-10-15:10:09: DBA_IND_COLUMNS 2861
B7-10-15:10:09: ALL_CATALOG 2810
B7-10-15:10:09: CMP_ROW_DIF_UNIQ_IDX_2 730
B7-10-15:10:09: DATABASE_COMPATIBLE_LEVEL 2576
B7-10-15:10:09: DBA_IND_COLUMNS 2862
B7-10-15:10:09: DBA_TAB_COLS 2940
B7-10-15:10:09: DIM$ 834
B7-10-15:10:09: DIR$ 444
B7-10-15:10:09: DIR$SERVICE_OPERATIONS 288
B7-10-15:10:09: EXPDEPACT$ 860
B7-10-15:10:09: GV$LOGMNR_PARAMETERS 2212
C1-12-06:13:00: SDO_GEORASTER 61285
C7-04-12:12:59: SQL_PLAN_STAT_ROW_TYPE 4683
C7-10-15:10:09: ALL_SYNONYMS 2915
C7-10-15:10:09: ALL_TAB_PRIVS_RECD 2967
C7-10-15:10:09: CACHE_STATS_SEQ_0 393
C7-10-15:10:09: CDC_SUBSCRIBED_COLUMNS$ 601
C7-10-15:10:09: COL 2785
C7-10-15:10:09: COLTYPE$ 81
C7-10-15:10:09: COMPARISON_SEQ$ 731
C7-10-15:10:09: CON$ 3
D1-12-06:13:00: SDO_RASTER 61286
D3-06-10:13:37: ORDIMAGESIGNATURE 54838
D3-12-25:09:11: SDO_TOPO_NSTD_TBL 58322
D7-10-15:10:09: ALL_COL_PRIVS_MADE 2838
D7-10-15:10:09: ALL_OBJECTS_AE 2890
D7-10-15:10:09: ALL_SYNONYMS 2916
D7-10-15:10:09: ALL_TAB_PRIVS_RECD 2968
D7-10-15:10:09: APPLY$_DEST_OBJ_ID 654
D7-10-15:10:09: ASSOCIATION$ 394
D7-10-15:10:09: ATTRCOL$ 446
E0-08-26:11:25: SDO_MBR 58193
E1-01-08:11:44: ORDDOC 54865
E1-12-06:13:00: SDO_RASTERSET 61287
E3-05-20:10:08: WM$NV_PAIR_TYPE 11471
E7-04-12:12:59: SQL_PLAN_ALLSTAT_ROW_TYPE 4685
E7-10-15:10:09: ALL_COL_PRIVS_MADE 2839
E7-10-15:10:09: ALL_IND_EXPRESSIONS 2865
E7-10-15:10:09: ALL_OBJECTS_AE 2891
E7-10-15:10:09: ALL_XML_SCHEMAS2 1071
E7-10-15:10:09: APPLY$_DEST_OBJ_CMAP 655
F1-07-29:12:08: WM$ED_UNDO_CODE_NODE_TYPE 11420
F1-12-06:13:00: SDO_GEOR_METADATA 61288
F3-05-20:10:08: WM$NV_PAIR_NT_TYPE 11472
F6-04-19:10:55: ORDDICOM 54866
F7-10-15:10:09: ALL_IND_EXPRESSIONS 2866
F7-10-15:10:09: ASSOC2 396
F7-10-15:10:09: BINARY_DOUBLE 968
F7-10-15:10:09: CDC_CHANGE_COLUMNS$ 604
F7-10-15:10:09: COLS 2944
F7-10-15:10:09: DBA_OBJECTS_AE 2892
G1-07-29:12:08: WM$ED_UNDO_CODE_TABLE_TYPE 11421
G1-12-06:13:00: SDO_GEOR_SRS 61289
G2-07-18:08:04: SI_COLOR 54841
G3-05-20:10:08: WM$EVENT_TYPE 11473
G7-10-15:10:09: ALL_TAB_COLUMNS 2945
G7-10-15:10:09: ALL_VIEWS 2971
G7-10-15:10:09: APPLY$_DEST_OBJ_OPS 657
G7-10-15:10:09: BINARY_FLOAT 969
G7-10-15:10:09: CLU 2815
G7-10-15:10:09: DBA_IND_EXPRESSIONS 2867
H2-10-14:18:00: SDO_SMPL_GEOMETRY 58196
H3-05-21:10:52: WM_PERIOD 11474
H7-10-15:10:09: ALL_CLUSTERS 2816
H7-10-15:10:09: ALL_COL_PRIVS_RECD 2842
H7-10-15:10:09: ALL_TAB_COLUMNS 2946
H7-10-15:10:09: ALL_VIEWS 2972
H7-10-15:10:09: BLOB 970
H7-10-15:10:09: CDC_RSID_SEQ$ 606
H7-10-15:10:09: CONTEXT$ 138
H7-10-15:10:09: DBA_IND_EXPRESSIONS 2868
I1-12-06:13:00: SDO_GEOR_HISTOGRAM 61291
I2-10-01:12:41: SDO_REGION 58197
I6-02-16:11:08: SDO_ORGSCL_TYPE 61655
I7-10-15:10:09: ALL_CLUSTERS 2817
I7-10-15:10:09: ALL_COL_PRIVS_RECD 2843
I7-10-15:10:09: APPLY$_ERROR 659
I7-10-15:10:09: ATEMPTAB$ 165
I7-10-15:10:09: CANONICAL 971
I7-10-15:10:09: CDC_PROPAGATIONS$ 607
I7-10-15:10:09: DBA_ROLLBACK_SEGS 2895
J1-12-06:13:00: SDO_GEOR_GRAYSCALE 61292
J2-10-01:12:46: SDO_REGIONSET 58198
J7-10-15:10:09: ATEMPIND$ 166
J7-10-15:10:09: CFILE 972
J7-10-15:10:09: C_MLOG# 556
J7-10-15:10:09: C_RG# 582
J7-10-15:10:09: C_TOID_VERSION# 452
J7-10-15:10:09: DBA_CLUSTERS 2818
J7-10-15:10:09: DBA_ENCRYPTED_COLUMNS 2844
J7-10-15:10:09: DBA_TAB_COLUMNS 2948
K1-12-06:13:00: SDO_GEOR_COLORMAP 61293
K6-02-16:10:48: SDO_PC_BLK 61657
K6-04-18:00:00: STANDARD 1051
K7-04-12:12:59: SQLPROF_ATTR 5679
K7-10-15:10:09: ALL_CONS_COLUMNS 2767
K7-10-15:10:09: APPLY$_ERROR_TXN 661
K7-10-15:10:09: ARGUMENT$ 193
K7-10-15:10:09: CDC_PROPAGATED_SETS$ 609
K7-10-15:10:09: CHAR 973
K7-10-15:10:09: C_FILE#_BLOCK# 37
L1-07-29:12:06: WM$LOCK_INFO_TYPE 11400
L7-10-15:10:09: ALL_CONS_COLUMNS 2768
L7-10-15:10:09: ALL_ENCRYPTED_COLUMNS 2846
L7-10-15:10:09: ALL_TABLES 2924
L7-10-15:10:09: CLOB 974
L7-10-15:10:09: C_USER# 38
L7-10-15:10:09: DBA_ROLE_PRIVS 2898
L7-10-15:10:09: DEFROLE$ 116
L7-10-15:10:09: DIMATTR$ 844
L7-10-15:10:09: DIR$QUIESCE_OPERATIONS 298
M1-07-29:12:06: WM$LOCK_TABLE_TYPE 11401
M1-12-06:13:00: SDO_NUMBER_ARRAY 58201
M2-10-23:15:20: AQ$_JMS_MESSAGES 7995
M7-04-12:12:59: TSM$SESSION_ID 5005
M7-10-15:10:09: ALL_ENCRYPTED_COLUMNS 2847
M7-10-15:10:09: ALL_TABLES 2925
M7-10-15:10:09: ALL_TAB_COMMENTS 2951
M7-10-15:10:09: APPLY$_ERROR_HANDLER_SEQUENCE 663
M7-10-15:10:09: ASSEMBLY$ 221
M7-10-15:10:09: AUD$ 351
N1-12-06:13:00: SDO_STRING_ARRAY 58202
N2-07-18:08:04: SI_STILLIMAGE 54848
N2-10-23:15:20: AQ$_JMS_TEXT_MESSAGES 7996
N7-04-12:12:59: TSM$SESSION_ID_LIST 5006
N7-10-15:10:09: ALL_OBJECT_TABLES 2926
N7-10-15:10:09: ALL_TAB_COMMENTS 2952
N7-10-15:10:09: APPLY$_ERROR_HANDLER 664
N7-10-15:10:09: AW_IND$ 742
N7-10-15:10:09: BOOTSTRAP$ 40
N7-10-15:10:09: DATE 976
O2-07-17:16:54: SDO_TOPO_GEOMETRY 58333
O2-07-18:08:04: SI_AVERAGECOLOR 54849
O2-10-23:15:20: AQ$_JMS_BYTES_MESSAGES 7997
O6-08-06:16:01: SDO_PC_BLK_TYPE 61661
O7-10-15:10:09: ALL_JOIN_IND_COLUMNS 2875
O7-10-15:10:09: ALL_OBJECT_TABLES 2927
O7-10-15:10:09: APPLY$_ERROR_HANDLER_UNQ 665
O7-10-15:10:09: APPLY$_SOURCE_OBJ 639
O7-10-15:10:09: DBA_CLU_COLUMNS 2823
O7-10-15:10:09: DBA_TAB_COMMENTS 2953
P1-07-29:12:06: WM$CONFLICT_PAYLOAD_TYPE 11430
P2-07-18:08:04: SI_COLORHISTOGRAM 54850
P2-10-23:15:20: AQ$_JMS_MAP_MESSAGES 7998
P6-02-16:10:51: SDO_PC 61662
P7-04-12:12:59: AQ$_AGENT 5190
P7-10-15:10:09: ALL_ALL_TABLES 2928
P7-10-15:10:09: ALL_JOIN_IND_COLUMNS 2876
P7-10-15:10:09: APPLY$_CONF_HDLR_COLUMNS 666
P7-10-15:10:09: COLUMN_PRIVILEGES 2798
P7-10-15:10:09: DBA_TAB_COMMENTS 2954
Q2-07-18:08:04: SI_POSITIONALCOLOR 54851
Q2-10-23:15:20: AQ$_JMS_STREAM_MESSAGES 7999
Q6-02-03:13:00: SDO_STRING2_ARRAY 61195
Q7-04-12:12:59: AQ$_DEQUEUE_HISTORY 5191
Q7-10-15:10:09: ALL_ALL_TABLES 2929
Q7-10-15:10:09: ALL_LOG_GROUP_COLUMNS 2773
Q7-10-15:10:09: ALL_SEQUENCES 2903
Q7-10-15:10:09: APPLY$_CONF_HDLR_COLUMNS_UNQ1 667
Q7-10-15:10:09: AUDSES$ 329
Q7-10-15:10:09: CCOL$ 43
R2-07-18:08:04: SI_TEXTURE 54852
R2-10-23:15:20: AQ$_JMS_OBJECT_MESSAGES 8000
R6-02-03:13:00: SDO_STRING2_ARRAYSET 61196
R6-05-31:09:18: SDO_TIN_BLK 61664
R7-04-12:12:59: AQ$_SUBSCRIBERS 5192
R7-04-12:12:59: SQL_BIND 5634
R7-10-15:10:09: ALL_COL_COMMENTS 2826
R7-10-15:10:09: ALL_LOG_GROUP_COLUMNS 2774
R7-10-15:10:09: ALL_SEQUENCES 2904
R7-10-15:10:09: APPLY$_CONF_HDLR_COLUMNS_UNQ2 668
S2-07-18:08:04: SI_FEATURELIST 54853
S2-10-23:15:30: AQ$_JMS_MESSAGE_PROPERTY 8001
S7-04-12:12:59: AQ$_RECIPIENTS 5193
S7-04-12:12:59: RE$NV_NODE 4751
S7-10-15:10:09: ALL_COL_COMMENTS 2827
S7-10-15:10:09: ALL_INDEXES 2853
S7-10-15:10:09: ALL_TAB_PRIVS 2957
S7-10-15:10:09: APPLY$_SOURCE_SCHEMA 643
S7-10-15:10:09: AW_OBJ$ 747
S7-10-15:10:09: CDC_CHANGE_SOURCES$ 591
T2-10-23:15:31: AQ$_JMS_MESSAGE_PROPERTIES 8002
T7-04-12:12:59: AQ$_HISTORY 5194
T7-04-12:12:59: RE$NV_ARRAY 4752
T7-04-12:12:59: SQL_BIND_SET 5636
T7-04-12:12:59: STREAMS$NV_NODE 9900
T7-10-15:10:09: ALL_INDEXES 2854
T7-10-15:10:09: ALL_LOBS 2802
T7-10-15:10:09: ALL_TAB_PRIVS 2958
T7-10-15:10:09: CLUSTER_DATABASES 280
T7-10-15:10:09: COLLECTION$ 462
U2-10-23:15:33: AQ$_JMS_ARRAY_MSGID_INFO 8003
U7-04-12:12:59: AQ$_DEQUEUE_HISTORY_T 5195
U7-04-12:12:59: STREAMS$NV_ARRAY 9901
U7-10-15:10:09: ALL_LOBS 2803
U7-10-15:10:09: APPLY$_VIRTUAL_OBJ_CONS 645
U7-10-15:10:09: APPROLE$ 359
U7-10-15:10:09: AW_PROP$ 749
U7-10-15:10:09: CDC_CHANGE_SETS$ 593
U7-10-15:10:09: CLUSTER_NODES 281
U7-10-15:10:09: CMPCOL_UNIQ_IDX1 723
V2-07-17:16:42: SDO_TOPO_GEOMETRY_LAYER 58314
V2-10-23:15:34: AQ$_JMS_ARRAY_MSGIDS 8004
V6-08-06:16:05: SDO_TIN_BLK_TYPE 61668
V7-04-12:12:59: SQL_PLAN_ROW_TYPE 4676
V7-04-12:12:59: STREAMS$TRANSFORMATION_INFO 9902
V7-10-15:10:09: ALL_OBJECTS 2882
V7-10-15:10:09: CLUSTER_INSTANCES 282
V7-10-15:10:09: COLLELEMIND 464
V7-10-15:10:09: COMPARISON_SCAN$ 724
V7-10-15:10:09: C_TS# 22
W0-09-28:12:59: AQ$_SIG_PROP 5197
W1-09-26:18:27: RE$NV_LIST 4755
W2-10-23:15:33: AQ$_JMS_ARRAY_ERROR_INFO 8005
W6-01-09:10:00: SDO_RANGE 58211
W6-02-16:10:52: SDO_TIN 61669
W7-04-09:14:32: ST_ANNOTATIONTEXTELEMENT 58887
W7-10-15:10:09: ALL_OBJECTS 2883
W7-10-15:10:09: APPLY$_CONSTRAINT_COLUMNS 647
W7-10-15:10:09: ATTRIBUTE$ 465
W7-10-15:10:09: AW_TRACK$ 751
X2-07-17:16:45: SDO_TOPO_GEOMETRY_LAYER_ARRAY 58316
X2-10-23:15:34: AQ$_JMS_ARRAY_ERRORS 8006
X6-01-09:10:00: SDO_RANGE_ARRAY 58212
X7-04-12:12:59: SQL_PLAN_TABLE_TYPE 4678
X7-05-02:14:39: ST_ANNOT_TEXTELEMENT_ARRAY 58888
X7-10-15:10:09: ALL_COL_PRIVS 2832
X7-10-15:10:09: APPLY$_CONSTRAINT_COLUMNS_UIX1 648
X7-10-15:10:09: CACHE_STATS_1$ 388
X7-10-15:10:09: CATALOG 2780
X7-10-15:10:09: COL$ 24
Y7-04-09:14:30: ST_ANNOTATIONTEXTELEMENT_ARRAY 58889
Y7-10-15:10:09: ACCESS$ 103
Y7-10-15:10:09: ALL_COL_PRIVS 2833
Y7-10-15:10:09: ALL_IND_COLUMNS 2859
Y7-10-15:10:09: ALL_TAB_PRIVS_MADE 2963
Y7-10-15:10:09: APPLY$_CONSTRAINT_COLUMNS_IDX1 649
Y7-10-15:10:09: AW_PRG$ 753
Y7-10-15:10:09: CATALOG 2781
Y7-10-15:10:09: CDC_SUBSCRIBERS$ 597
Y7-10-15:10:09: CDEF$ 51
250 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2825474100
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3414 | 323K| | 52 (2)| 00:00:01 |
|* 1 | VIEW | | 3414 | 323K| | 52 (2)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK | | 3414 | 160K| 424K| 52 (2)| 00:00:01 |
| 3 | NESTED LOOPS | | 3414 | 160K| | 8 (0)| 00:00:01 |
| 4 | VIEW | | 1 | 2 | | 2 (0)| 00:00:01 |
| 5 | COUNT | | | | | | |
|* 6 | CONNECT BY WITHOUT FILTERING| | | | | | |
| 7 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | CUSTOMER | 3414 | 153K| | 6 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("I"<=10)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "CHARACTER" ORDER BY "FNAME","SNAME","ID")<=10)
6 - filter(LEVEL<=26)
8 - access("FNAME">="ALPHABET"."CHARACTER" AND "FNAME" IS NOT NULL)
SCOTT@orcl_11g>
|
|
|
|
| Re: Huge Query [message #307997 is a reply to message #307995] |
Thu, 20 March 2008 13:52 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I posted in the same minute as coleing. After seeing his, his looks better. I figured I would post the test of his for comparison, since I already have the environment set up.
SCOTT@orcl_11g> SELECT *
2 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
3 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
4 Row_number() OVER(PARTITION BY substr(fName,1,1) ORDER BY sName, Id) alphabet_rn,
5 fName,
6 sName,
7 Id
8 FROM Customer
9 WHERE fName >= 'A'
10 )
11 WHERE alphabet_rn <= 10
12 ORDER BY fName,
13 sName,
14 Id
15 /
I ALPHABET_RN FNAME SNAME ID
---------- ----------- --------------- ------------------------------ ----------
1 5 A7-10-15:10:27: /10948dc3_PermissionImpl 12169
1 2 A7-10-15:10:28: /1005bd30_LnkdConstant 21373
1 4 A7-10-15:10:28: /10845320_TypeMapImpl 17915
1 9 A7-10-15:10:28: /11604791_PowerPCCodeGenerator 14743
1 7 A7-10-15:10:29: /109cc334_ThreadPoolImpl 31435
1 10 A7-10-15:10:29: /117b1329_VMDisconnectEvent 30005
1 1 A7-10-15:10:31: /1000e8d1_LinkedHashMapValueIt 42641
1 3 A7-10-15:10:31: /108343f6_MultiColorChooserUI 44435
1 8 A7-10-15:10:31: /10afd42d_PsuedoNames 43421
1 6 A7-10-15:10:34: /109a284b_OracleXMLStaticQuery 51689
1 2 B7-10-15:10:28: /10501902_BasicFileChooserUINe 19580
1 3 B7-10-15:10:28: /10588c59_BasicTabbedPaneUIScr 19346
1 4 B7-10-15:10:28: /10804ae7_Constants 16902
1 10 B7-10-15:10:28: /11a95555_ConsNullIterator 18566
1 7 B7-10-15:10:29: /109cc334_ThreadPoolImpl 31436
1 9 B7-10-15:10:29: /117b1329_VMDisconnectEvent 30006
1 1 B7-10-15:10:31: /1000e8d1_LinkedHashMapValueIt 42642
1 6 B7-10-15:10:31: /1095ce9b_MultiComboBoxUI 44436
1 8 B7-10-15:10:31: /10afd42d_PsuedoNames 43422
1 5 B7-10-15:10:42: /1081ca83_MlibDilate3SquareOpI 56838
1 3 C7-10-15:10:28: /10501902_BasicFileChooserUINe 19581
1 4 C7-10-15:10:28: /10588c59_BasicTabbedPaneUIScr 19347
1 5 C7-10-15:10:28: /10804ae7_Constants 16903
1 8 C7-10-15:10:28: /11a95555_ConsNullIterator 18567
1 1 C7-10-15:10:29: /1020ed5e_Param 26601
1 10 C7-10-15:10:29: /11f2d793_SubstitutionGroupHan 25379
1 7 C7-10-15:10:30: /1169ca20_AdapterActivator 33127
1 9 C7-10-15:10:30: /11eac1b7_DGCAckHandler1 35545
1 6 C7-10-15:10:31: /1095ce9b_MultiComboBoxUI 44437
1 2 C7-10-15:10:33: /1048734f_DefaultFolder 49897
1 7 D7-10-15:10:27: /128475c8_CrlRevocationChecker 12120
1 9 D7-10-15:10:28: /12f1c4b5_LoopOptimizationsLoo 15058
1 1 D7-10-15:10:29: /1020ed5e_Param 26602
1 6 D7-10-15:10:29: /11f2d793_SubstitutionGroupHan 25380
1 3 D7-10-15:10:30: /1169ca20_AdapterActivator 33128
1 5 D7-10-15:10:30: /11eac1b7_DGCAckHandler1 35546
1 8 D7-10-15:10:30: /12a273e6_InternationalFormatt 34818
1 2 D7-10-15:10:31: /110c86bd_LocaleElements_es_DO 48338
1 4 D7-10-15:10:34: /119ace83_configproperties 51952
1 10 D7-10-15:10:34: /130764b4_StreamMsgEntity 52550
1 6 E7-10-15:10:27: /128475c8_CrlRevocationChecker 12121
1 3 E7-10-15:10:29: /114e2135_UsageMessageNeededEx 23561
1 7 E7-10-15:10:29: /128a5f99_CachingParserPoolSha 24965
1 1 E7-10-15:10:30: /109dbb46_MetalLookAndFeelFont 35209
1 4 E7-10-15:10:30: /121bd8f2_SSLServerSocketFacto 38615
1 9 E7-10-15:10:30: /134c4ace_DataTransferer3 36015
1 10 E7-10-15:10:30: /136a10f9_UnsafeQualifiedInteg 39369
1 2 E7-10-15:10:31: /110c86bd_LocaleElements_es_DO 48339
1 8 E7-10-15:10:34: /12fd44ad_XSLOtherElements 50965
1 5 E7-10-15:10:41: /1281e4ac_BorderExtenderReflec 56087
1 1 F7-10-15:10:28: /10697cee_Relation 19974
1 6 F7-10-15:10:28: /120f4708_FullHTMLDocumentatio 16490
1 9 F7-10-15:10:28: /12a88bc6_LittleEndianRowidBin 20962
1 10 F7-10-15:10:28: /132c2c26_ClassLoaderChooser1 13656
1 4 F7-10-15:10:29: /114e2135_UsageMessageNeededEx 23562
1 8 F7-10-15:10:29: /128a5f99_CachingParserPoolSha 24966
1 3 F7-10-15:10:30: /109dbb46_MetalLookAndFeelFont 35210
1 7 F7-10-15:10:30: /121bd8f2_SSLServerSocketFacto 38616
1 5 F7-10-15:10:31: /11d50912_DateFormatZoneData_z 48236
1 2 F7-10-15:10:41: /1081ca83_MlibDilate3SquareOpI 55672
1 5 G7-10-15:10:28: /10697cee_Relation 19975
1 10 G7-10-15:10:28: /10ee0685_ExecutorsPrivilegedC 22003
1 6 G7-10-15:10:29: /10832ae4_SecuritySupport127 26215
1 9 G7-10-15:10:29: /10e8e51e_ETypeInfo 28867
1 1 G7-10-15:10:30: /104a4549_FrameViewFrameEditor 36797
1 4 G7-10-15:10:30: /1056cf94_DrawLineTraceDrawLin 33885
1 7 G7-10-15:10:30: /108b4b6_AnyImpl 32091
1 8 G7-10-15:10:30: /10dbe49_AdapterInactiveHelper 33157
1 2 G7-10-15:10:31: /104f94f3_AiffFileWriter 43245
1 3 G7-10-15:10:33: /105072e7_HttpSessionBindingEv 50161
1 4 H7-10-15:10:28: /105f39a1_AQDequeueOptions 20704
1 9 H7-10-15:10:28: /10ee0685_ExecutorsPrivilegedC 22004
1 5 H7-10-15:10:29: /10832ae4_SecuritySupport127 26216
1 8 H7-10-15:10:29: /10e8e51e_ETypeInfo 28868
1 1 H7-10-15:10:30: /104a4549_FrameViewFrameEditor 36798
1 3 H7-10-15:10:30: /1056cf94_DrawLineTraceDrawLin 33886
1 6 H7-10-15:10:30: /108b4b6_AnyImpl 32092
1 7 H7-10-15:10:30: /10dbe49_AdapterInactiveHelper 33158
1 2 H7-10-15:10:31: /104f94f3_AiffFileWriter 43246
1 10 H7-10-15:10:31: /110f99d1_SemanticErrorsText_k 46522
1 2 I7-10-15:10:28: /105f39a1_AQDequeueOptions 20705
1 9 I7-10-15:10:28: /1146b53d_BasicSliderUIScrollL 19379
1 3 I7-10-15:10:29: /11040ed1_PrimitiveEntry 23409
1 5 I7-10-15:10:29: /1107af3_ValidatorHandlerImpl 25099
1 8 I7-10-15:10:29: /112b66aa_ServerHandshaker2 28271
1 7 I7-10-15:10:30: /11125a97_BMPImageReader 35707
1 6 I7-10-15:10:31: /110f99d1_SemanticErrorsText_k 46523
1 1 I7-10-15:10:41: /1013c29d_PlanarImageServerPro 56403
1 4 I7-10-15:10:42: /1106a6c1_MultiplyOpImage 57079
1 10 I7-10-15:10:42: /116f06e2_HistogramOpImage 57027
1 2 J7-10-15:10:27: /10313c9e_LocalMonitoredVmNoti 12464
1 1 J7-10-15:10:28: /10076b23_OraCustomDatumClosur 17690
1 5 J7-10-15:10:29: /11040ed1_PrimitiveEntry 23410
1 6 J7-10-15:10:29: /1107af3_ValidatorHandlerImpl 25100
1 9 J7-10-15:10:29: /112b66aa_ServerHandshaker2 28272
1 7 J7-10-15:10:30: /11125a97_BMPImageReader 35708
1 3 J7-10-15:10:31: /10a45bfe_ProfilePrinterErrors 46420
1 4 J7-10-15:10:31: /10ab4aa5_LocaleElements_lv_LV 48422
1 8 J7-10-15:10:31: /11196bbe_JobImpressions 44132
1 10 J7-10-15:10:31: /112bf7d4_HTMLFrameElement 45900
1 2 K7-10-15:10:27: /10313c9e_LocalMonitoredVmNoti 12465
1 1 K7-10-15:10:28: /10076b23_OraCustomDatumClosur 17691
1 3 K7-10-15:10:29: /1098f5b7_SecuritySupport125 24841
1 6 K7-10-15:10:29: /10d4431e_DTMDefaultBaseIterat 27415
1 7 K7-10-15:10:30: /10d95c5c_ClientDelegate 32953
1 4 K7-10-15:10:31: /10a45bfe_ProfilePrinterErrors 46421
1 5 K7-10-15:10:31: /10ab4aa5_LocaleElements_lv_LV 48423
1 8 K7-10-15:10:31: /11196bbe_JobImpressions 44133
1 9 K7-10-15:10:31: /112bf7d4_HTMLFrameElement 45901
1 10 K7-10-15:10:47: /1138e59d_DBFReaderJGeomLogica 58979
1 4 L7-10-15:10:27: /11444b1a_JavaPClassPrinterCod 11920
1 6 L7-10-15:10:28: /118c67ee_BeanContextSupportBC 22294
1 10 L7-10-15:10:28: /12951c8_IBMWrappedRTStmt 18186
1 1 L7-10-15:10:29: /1098f5b7_SecuritySupport125 24842
1 2 L7-10-15:10:29: /10d4431e_DTMDefaultBaseIterat 27416
1 7 L7-10-15:10:29: /11a66a3_IORInfoExt 30692
1 9 L7-10-15:10:29: /1267d256_ComponentContext1 29002
1 3 L7-10-15:10:30: /10d95c5c_ClientDelegate 32954
1 5 L7-10-15:10:42: /1186c016_SampleModelProxy 57186
1 8 L7-10-15:10:42: /12551759_IDFTCRIF 57030
1 7 M7-10-15:10:27: /11444b1a_JavaPClassPrinterCod 11921
1 1 M7-10-15:10:29: /101419a4_NormalDataCollector 30771
1 4 M7-10-15:10:29: /10de22c6_ClassTypeImpl2 29653
1 8 M7-10-15:10:29: /114ebde3_ContextFactory 23855
1 3 M7-10-15:10:30: /10c4ec62_ImageTypeSpecifierGr 35919
1 6 M7-10-15:10:30: /1129774b_JPEGImageReadParam 35789
1 9 M7-10-15:10:31: /11631c0_DESedeParameters 43121
1 2 M7-10-15:10:34: /10c4e898_OXQueryItemFactory 52091
1 5 M7-10-15:10:41: /1106a6c1_MultiplyOpImage 55913
1 10 M7-10-15:10:41: /116f06e2_HistogramOpImage 55861
1 5 N7-10-15:10:28: /10e16f89_OracleReturnResultSe 20866
1 9 N7-10-15:10:28: /117c8d34_ArrayBlockingQueueIt 22114
1 2 N7-10-15:10:29: /101419a4_NormalDataCollector 30772
1 4 N7-10-15:10:29: /10de22c6_ClassTypeImpl2 29654
1 7 N7-10-15:10:29: /114ebde3_ContextFactory 23856
1 10 N7-10-15:10:29: /11c57b66_IFNE 27054
1 3 N7-10-15:10:30: /10c4ec62_ImageTypeSpecifierGr 35920
1 6 N7-10-15:10:30: /1129774b_JPEGImageReadParam 35790
1 1 N7-10-15:10:31: /10128284_OpenMBeanAttributeIn 44058
1 8 N7-10-15:10:31: /11631c0_DESedeParameters 43122
1 5 O7-10-15:10:28: /10e16f89_OracleReturnResultSe 20867
1 7 O7-10-15:10:28: /11799933_SchemaProtectionDoma 16317
1 3 O7-10-15:10:29: /106f958c_Type 27887
1 4 O7-10-15:10:29: /10b067f1_PKCS12PBECipherCoreP 30565
1 10 O7-10-15:10:29: /11fe6909_ArrayInstruction 26873
1 8 O7-10-15:10:30: /11ab5385_JTextComponentAccess 37117
1 9 O7-10-15:10:30: /11d9ae7d_CodeSetConversionUTF 32177
1 2 O7-10-15:10:31: /10128284_OpenMBeanAttributeIn 44059
1 1 O7-10-15:10:43: /100cb3d7_DicomLocatorPathDico 58073
1 6 O7-10-15:10:47: /1145142e_LogicalNetLink 59243
1 2 P7-10-15:10:28: /10378bc5_IA64BaseLIRInstrFSAB 14264
1 8 P7-10-15:10:28: /113ef1a_ParamRegProfileParamR 17800
1 3 P7-10-15:10:29: /106f958c_Type 27888
1 5 P7-10-15:10:29: /10b067f1_PKCS12PBECipherCoreP 30566
1 10 P7-10-15:10:30: /11ab5385_JTextComponentAccess 37118
1 6 P7-10-15:10:33: /1111d1d9_DOMImplementationLis 50924
1 9 P7-10-15:10:41: /1186c016_SampleModelProxy 56020
1 1 P7-10-15:10:42: /1013c29d_PlanarImageServerPro 57554
1 7 P7-10-15:10:47: /1138e59d_DBFReaderJGeomLogica 59036
1 4 P7-10-15:10:53: /10862847_Intersection 59530
1 2 Q7-10-15:10:28: /10378bc5_IA64BaseLIRInstrFSAB 14265
1 10 Q7-10-15:10:28: /113ef1a_ParamRegProfileParamR 17801
1 3 Q7-10-15:10:29: /103fb81b_DocumentBuilderImpl 25159
1 7 Q7-10-15:10:29: /10f3e1ab_SizeLimitExceededExc 29059
1 4 Q7-10-15:10:30: /1073eda1_NotificationBroadcas 41227
1 9 Q7-10-15:10:31: /111b9d88_RenderableImageProdu 43801
1 6 Q7-10-15:10:34: /109a284b_OracleXMLStaticQuery 51653
1 1 Q7-10-15:10:42: /1025308f_SunTileScheduler 57243
1 8 Q7-10-15:10:42: /11025bfb_DicomMetadataReader 57867
1 5 Q7-10-15:10:48: /10862847_Intersection 59583
1 8 R7-10-15:10:28: /1101cbe5_BinaryOpValueExp 20064
1 10 R7-10-15:10:28: /111e1b18_BasicTabbedPaneUIHan 19336
1 2 R7-10-15:10:29: /103fb81b_DocumentBuilderImpl 25160
1 7 R7-10-15:10:29: /10f3e1ab_SizeLimitExceededExc 29060
1 4 R7-10-15:10:30: /1073eda1_NotificationBroadcas 41228
1 3 R7-10-15:10:31: /104ea7da_Messages_ko 45076
1 5 R7-10-15:10:31: /108c800b_BasicFormattedTextFi 44322
1 6 R7-10-15:10:31: /10ee5208_LocaleElements_ar_YE 48274
1 9 R7-10-15:10:31: /111b9d88_RenderableImageProdu 43802
1 1 R7-10-15:10:33: /10297c91_SAXAttrList 50536
1 5 S7-10-15:10:28: /1079c94d_NumberConstantData 22613
1 9 S7-10-15:10:28: /1101cbe5_BinaryOpValueExp 20065
1 7 S7-10-15:10:30: /10d70c51_ProcessEnvironmentSt 39591
1 10 S7-10-15:10:30: /1116be6_RoundRectangle2DFloat 34521
1 1 S7-10-15:10:31: /100c1606_StandardMidiFileRead 43257
1 3 S7-10-15:10:31: /104ea7da_Messages_ko 45077
1 6 S7-10-15:10:31: /108c800b_BasicFormattedTextFi 44323
1 8 S7-10-15:10:31: /10ee5208_LocaleElements_ar_YE 48275
1 2 S7-10-15:10:34: /10297c91_SAXAttrList 51213
1 4 S7-10-15:10:34: /106ba0a5_ArrayEnumeration 51577
1 2 T7-10-15:10:28: /1065f59e_DescriptorAccess 20040
1 3 T7-10-15:10:28: /106faabc_BasicTreeUIKeyHandle 19208
1 4 T7-10-15:10:28: /1079c94d_NumberConstantData 22614
1 5 T7-10-15:10:30: /10d70c51_ProcessEnvironmentSt 39592
1 7 T7-10-15:10:30: /1116be6_RoundRectangle2DFloat 34522
1 10 T7-10-15:10:30: /117cfcc9_ConnectionCache 32988
1 1 T7-10-15:10:31: /100c1606_StandardMidiFileRead 43258
1 6 T7-10-15:10:31: /110b84a_CharConvRepackage 45104
1 8 T7-10-15:10:31: /11351672_minimizegif 48640
1 9 T7-10-15:10:31: /113a521c_AppInputStream 43310
1 6 U7-10-15:10:28: /1065f59e_DescriptorAccess 20041
1 8 U7-10-15:10:28: /106faabc_BasicTreeUIKeyHandle 19209
1 1 U7-10-15:10:29: /1000323d_DelegateInvocationHa 31845
1 4 U7-10-15:10:29: /1042a784_ElementImpl 25631
1 5 U7-10-15:10:29: /104371db_JDWPThreadReferenceS 30103
1 9 U7-10-15:10:30: /109cbb8e_SpanShapeRendererSim 33977
1 7 U7-10-15:10:33: /106ba0a5_ArrayEnumeration 50903
1 10 U7-10-15:10:34: /10c4e898_OXQueryItemFactory 51839
1 3 U7-10-15:10:41: /1025308f_SunTileScheduler 56077
1 2 U7-10-15:10:42: /100cb3d7_DicomLocatorPathDico 57949
1 2 V7-10-15:10:28: /1023e902_OraCharsetUTFE 13724
1 9 V7-10-15:10:28: /118e2799_PlsqlIbtBindInfo 20848
1 1 V7-10-15:10:29: /1000323d_DelegateInvocationHa 31846
1 3 V7-10-15:10:29: /1042a784_ElementImpl 25632
1 4 V7-10-15:10:29: /104371db_JDWPThreadReferenceS 30104
1 8 V7-10-15:10:29: /113c2e87_ObjectReferenceImplC 30130
1 10 V7-10-15:10:29: /11f2795a_DTDGrammarChildrenLi 25996
1 5 V7-10-15:10:30: /109cbb8e_SpanShapeRendererSim 33978
1 7 V7-10-15:10:30: /10cbea30_SubjectSecureSet2 40296
1 6 V7-10-15:10:31: /10c906a0_ProfilePrinterErrors 46406
1 1 W7-10-15:10:28: /1023e902_OraCharsetUTFE 13725
1 6 W7-10-15:10:28: /10e48aa3_StringExpressionCons 22617
1 7 W7-10-15:10:28: /118e2799_PlsqlIbtBindInfo 20849
1 9 W7-10-15:10:29: /1212dd8d_XSGrammar 26231
1 2 W7-10-15:10:30: /103a2e73_DefaultEditorKitEndP 36475
1 4 W7-10-15:10:30: /107060f6_ConditionalSpecialCa 38711
1 10 W7-10-15:10:30: /122fb53c_FontConfigurationPro 33745
1 5 W7-10-15:10:31: /10c906a0_ProfilePrinterErrors 46407
1 3 W7-10-15:10:33: /1048734f_DefaultFolder 50125
1 8 W7-10-15:10:34: /11dee1d7_AQjmsXAResourceFacto 52517
1 4 X7-10-15:10:28: /109def6d_AMD64AbstractMIR2LIR 14506
1 5 X7-10-15:10:28: /10e48aa3_StringExpressionCons 22618
1 7 X7-10-15:10:29: /1212dd8d_XSGrammar 26232
1 10 X7-10-15:10:29: /1243732_DTMDefaultBaseIterato 27428
1 1 X7-10-15:10:30: /103a2e73_DefaultEditorKitEndP 36476
1 3 X7-10-15:10:30: /107060f6_ConditionalSpecialCa 38712
1 9 X7-10-15:10:30: /122fb53c_FontConfigurationPro 33746
1 2 X7-10-15:10:33: /105072e7_HttpSessionBindingEv 50204
1 8 X7-10-15:10:34: /122bb5b3_AQjmsExceptionListen 52414
1 6 X7-10-15:10:41: /11f08aab_BandSelectCRIF 55794
1 3 Y7-10-15:10:28: /109def6d_AMD64AbstractMIR2LIR 14507
1 4 Y7-10-15:10:28: /10b74838_SecurityManagerImpl 21631
1 5 Y7-10-15:10:28: /10be5a84_GenericMIR2LIRConver 15053
1 6 Y7-10-15:10:28: /10dcd7b1_ProducerConsumerProd 15417
1 8 Y7-10-15:10:30: /10f82448_WritableRasterNative 34527
1 9 Y7-10-15:10:30: /1146c57a_OutputObject 32993
1 10 Y7-10-15:10:30: /11650694_InternationalFormatt 34813
1 7 Y7-10-15:10:31: /10e24fcf_XPathFactoryImpl 43497
1 1 Y7-10-15:10:33: /109a6960_CurDate 50699
1 2 Y7-10-15:10:34: /109a6960_CurDate 51375
250 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3474778796
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 66125 | 6974K| | 1381 (1)| 00:00:17 |
| 1 | SORT ORDER BY | | 66125 | 6974K| | 1381 (1)| 00:00:17 |
|* 2 | VIEW | | 66125 | 6974K| | 1381 (1)| 00:00:17 |
|* 3 | WINDOW SORT PUSHED RANK| | 66125 | 3422K| 8840K| 1381 (1)| 00:00:17 |
| 4 | WINDOW NOSORT | | 66125 | 3422K| | 1381 (1)| 00:00:17 |
|* 5 | INDEX RANGE SCAN | CUSTOMER | 66125 | 3422K| | 514 (1)| 00:00:07 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ALPHABET_RN"<=10)
3 - filter(ROW_NUMBER() OVER ( PARTITION BY SUBSTR("FNAME",1,1) ORDER BY
"SNAME","ID")<=10)
5 - access("FNAME">='A' AND "FNAME" IS NOT NULL)
SCOTT@orcl_11g>
|
|
|
|
| Re: Huge Query [message #308006 is a reply to message #307997] |
Thu, 20 March 2008 14:43 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
Dear Rajaram & Coleing,
Yes, I am trying to get the first 10 rows for each surname beginning with each letter of the alphabet.
As you might have guessed, this is for a long list that needs to be quickly navigated by pressing the appropriate letter on the keyboard.
I realize that I will get duplicates if I have less than 10 surnames for a particular letter, but getting a full screenful of data quickly (10 rows) is more important in my case (e.g. 3 rows of "A%" and 7 rows of "B%".
It kind of bothers me that if e.g. no surname begins with "Y" the query will return the closest next surnames (e.g. begiining with "Y" then "Z"), but many file managers respond that way, so I guess users are used to it.
I will test your solutions next.
Thanks for your help.
Regards,
George Robinson
| Quote: | S.Rajaram
Do you want the duplicates to be displayed to be as well ? And also could you please explain in plain words what you are trying to do.
|
| Quote: | coleing
Sounds to me like you are trying to get the first 10 rows for each surname beginning with each letter of the alphabet. The query you have will give you duplicates if you have less than 10 customers for a particular letter.
I cant think you would need the duplicates...
|
[Updated on: Thu, 20 March 2008 14:44] Report message to a moderator |
|
|
|
| Re: Huge Query [message #308012 is a reply to message #307997] |
Thu, 20 March 2008 15:20 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
Dear Barbara,
Thanks for your crack at this but your proposition is missing the "i" disambiguation column that consecutively numbers rows with identical FNAMEs and SNAMEs (if they occur).
Coleing's solution does not produces the correct output either (I want duplicates just like in the original UNION ALL query) but Coleing's query is 2x faster than Barbara's, however it is still 50x slower than the original huge UNION ALL query on real word data (with real first names and surnames, 10^6 rows.)
BTW: You can assume that an all ascending composite index exists on FNAME, SNAME, ID.
Coleing, thanks nonetheless.
Regards,
George Robinson
P.S.
The query should always return 260 rows if (SELECT COUNT(*) FROM CUSTOMER WHERE FNAME>='A') >= 260
[Updated on: Thu, 20 March 2008 15:36] Report message to a moderator |
|
|
|
| Re: Huge Query [message #308025 is a reply to message #308012] |
Thu, 20 March 2008 16:13 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It is easy enough to put the i back in the version that I posted, and still keep the duplicates, but it will probably be even slightly slower.
SCOTT@orcl_11g> WITH AlphaBet AS
2 (SELECT CHR(ROWNUM + 64) AS Character
3 FROM DUAL
4 CONNECT BY LEVEL <= 26)
5 SELECT i, fname, sname, id
6 FROM (SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
7 Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
8 Row_number() OVER(PARTITION BY Character ORDER BY fname, sname, Id) i2,
9 fName, sName, Id, Alphabet.Character
10 FROM Customer, Alphabet
11 WHERE fName >= Alphabet.Character)
12 WHERE i2 <= 10
13 ORDER BY character, fName, sName, Id
14 /
I FNAME SNAME ID
---------- --------------- ------------------------------ ----------
1 A6-04-19:10:56: ORDDATASOURCE 54835
1 A7-10-15:10:09: ALL_CATALOG 2809
1 A7-10-15:10:09: ALL_TAB_COLS 2939
1 A7-10-15:10:09: CACHE_STATS_0$ 391
1 A7-10-15:10:09: CDC_SUBSCRIBED_TABLES$ 599
1 A7-10-15:10:09: CLU$ 27
1 A7-10-15:10:09: CMP_ROW_DIF_UNIQ_IDX_1 729
1 A7-10-15:10:09: DATABASE_COMPATIBLE_LEVEL 2575
1 A7-10-15:10:09: DBA_COL_PRIVS 2835
1 A7-10-15:10:09: DBA_IND_COLUMNS 2861
2 B7-10-15:10:09: ALL_CATALOG 2810
2 B7-10-15:10:09: CMP_ROW_DIF_UNIQ_IDX_2 730
2 B7-10-15:10:09: DATABASE_COMPATIBLE_LEVEL 2576
2 B7-10-15:10:09: DBA_IND_COLUMNS 2862
1 B7-10-15:10:09: DBA_TAB_COLS 2940
2 B7-10-15:10:09: DIM$ 834
2 B7-10-15:10:09: DIR$ 444
2 B7-10-15:10:09: DIR$SERVICE_OPERATIONS 288
2 B7-10-15:10:09: EXPDEPACT$ 860
2 B7-10-15:10:09: GV$LOGMNR_PARAMETERS 2212
1 C1-12-06:13:00: SDO_GEORASTER 61285
2 C7-04-12:12:59: SQL_PLAN_STAT_ROW_TYPE 4683
3 C7-10-15:10:09: ALL_SYNONYMS 2915
2 C7-10-15:10:09: ALL_TAB_PRIVS_RECD 2967
2 C7-10-15:10:09: CACHE_STATS_SEQ_0 393
3 C7-10-15:10:09: CDC_SUBSCRIBED_COLUMNS$ 601
3 C7-10-15:10:09: COL 2785
3 C7-10-15:10:09: COLTYPE$ 81
2 C7-10-15:10:09: COMPARISON_SEQ$ 731
2 C7-10-15:10:09: CON$ 3
1 D1-12-06:13:00: SDO_RASTER 61286
4 D3-06-10:13:37: ORDIMAGESIGNATURE 54838
3 D3-12-25:09:11: SDO_TOPO_NSTD_TBL 58322
4 D7-10-15:10:09: ALL_COL_PRIVS_MADE 2838
1 D7-10-15:10:09: ALL_OBJECTS_AE 2890
2 D7-10-15:10:09: ALL_SYNONYMS 2916
4 D7-10-15:10:09: ALL_TAB_PRIVS_RECD 2968
3 D7-10-15:10:09: APPLY$_DEST_OBJ_ID 654
2 D7-10-15:10:09: ASSOCIATION$ 394
1 D7-10-15:10:09: ATTRCOL$ 446
5 E0-08-26:11:25: SDO_MBR 58193
1 E1-01-08:11:44: ORDDOC 54865
5 E1-12-06:13:00: SDO_RASTERSET 61287
1 E3-05-20:10:08: WM$NV_PAIR_TYPE 11471
5 E7-04-12:12:59: SQL_PLAN_ALLSTAT_ROW_TYPE 4685
1 E7-10-15:10:09: ALL_COL_PRIVS_MADE 2839
5 E7-10-15:10:09: ALL_IND_EXPRESSIONS 2865
1 E7-10-15:10:09: ALL_OBJECTS_AE 2891
5 E7-10-15:10:09: ALL_XML_SCHEMAS2 1071
1 E7-10-15:10:09: APPLY$_DEST_OBJ_CMAP 655
2 F1-07-29:12:08: WM$ED_UNDO_CODE_NODE_TYPE 11420
5 F1-12-06:13:00: SDO_GEOR_METADATA 61288
2 F3-05-20:10:08: WM$NV_PAIR_NT_TYPE 11472
5 F6-04-19:10:55: ORDDICOM 54866
2 F7-10-15:10:09: ALL_IND_EXPRESSIONS 2866
5 F7-10-15:10:09: ASSOC2 396
2 F7-10-15:10:09: BINARY_DOUBLE 968
5 F7-10-15:10:09: CDC_CHANGE_COLUMNS$ 604
2 F7-10-15:10:09: COLS 2944
5 F7-10-15:10:09: DBA_OBJECTS_AE 2892
7 G1-07-29:12:08: WM$ED_UNDO_CODE_TABLE_TYPE 11421
1 G1-12-06:13:00: SDO_GEOR_SRS 61289
7 G2-07-18:08:04: SI_COLOR 54841
1 G3-05-20:10:08: WM$EVENT_TYPE 11473
7 G7-10-15:10:09: ALL_TAB_COLUMNS 2945
1 G7-10-15:10:09: ALL_VIEWS 2971
7 G7-10-15:10:09: APPLY$_DEST_OBJ_OPS 657
1 G7-10-15:10:09: BINARY_FLOAT 969
7 G7-10-15:10:09: CLU 2815
1 G7-10-15:10:09: DBA_IND_EXPRESSIONS 2867
2 H2-10-14:18:00: SDO_SMPL_GEOMETRY 58196
7 H3-05-21:10:52: WM_PERIOD 11474
2 H7-10-15:10:09: ALL_CLUSTERS 2816
7 H7-10-15:10:09: ALL_COL_PRIVS_RECD 2842
2 H7-10-15:10:09: ALL_TAB_COLUMNS 2946
7 H7-10-15:10:09: ALL_VIEWS 2972
2 H7-10-15:10:09: BLOB 970
7 H7-10-15:10:09: CDC_RSID_SEQ$ 606
2 H7-10-15:10:09: CONTEXT$ 138
7 H7-10-15:10:09: DBA_IND_EXPRESSIONS 2868
8 I1-12-06:13:00: SDO_GEOR_HISTOGRAM 61291
3 I2-10-01:12:41: SDO_REGION 58197
8 I6-02-16:11:08: SDO_ORGSCL_TYPE 61655
3 I7-10-15:10:09: ALL_CLUSTERS 2817
8 I7-10-15:10:09: ALL_COL_PRIVS_RECD 2843
2 I7-10-15:10:09: APPLY$_ERROR 659
7 I7-10-15:10:09: ATEMPTAB$ 165
2 I7-10-15:10:09: CANONICAL 971
7 I7-10-15:10:09: CDC_PROPAGATIONS$ 607
3 I7-10-15:10:09: DBA_ROLLBACK_SEGS 2895
6 J1-12-06:13:00: SDO_GEOR_GRAYSCALE 61292
5 J2-10-01:12:46: SDO_REGIONSET 58198
6 J7-10-15:10:09: ATEMPIND$ 166
5 J7-10-15:10:09: CFILE 972
6 J7-10-15:10:09: C_MLOG# 556
5 J7-10-15:10:09: C_RG# 582
6 J7-10-15:10:09: C_TOID_VERSION# 452
5 J7-10-15:10:09: DBA_CLUSTERS 2818
6 J7-10-15:10:09: DBA_ENCRYPTED_COLUMNS 2844
5 J7-10-15:10:09: DBA_TAB_COLUMNS 2948
6 K1-12-06:13:00: SDO_GEOR_COLORMAP 61293
7 K6-02-16:10:48: SDO_PC_BLK 61657
6 K6-04-18:00:00: STANDARD 1051
6 K7-04-12:12:59: SQLPROF_ATTR 5679
6 K7-10-15:10:09: ALL_CONS_COLUMNS 2767
6 K7-10-15:10:09: APPLY$_ERROR_TXN 661
5 K7-10-15:10:09: ARGUMENT$ 193
7 K7-10-15:10:09: CDC_PROPAGATED_SETS$ 609
6 K7-10-15:10:09: CHAR 973
6 K7-10-15:10:09: C_FILE#_BLOCK# 37
5 L1-07-29:12:06: WM$LOCK_INFO_TYPE 11400
6 L7-10-15:10:09: ALL_CONS_COLUMNS 2768
6 L7-10-15:10:09: ALL_ENCRYPTED_COLUMNS 2846
7 L7-10-15:10:09: ALL_TABLES 2924
5 L7-10-15:10:09: CLOB 974
8 L7-10-15:10:09: C_USER# 38
5 L7-10-15:10:09: DBA_ROLE_PRIVS 2898
7 L7-10-15:10:09: DEFROLE$ 116
5 L7-10-15:10:09: DIMATTR$ 844
6 L7-10-15:10:09: DIR$QUIESCE_OPERATIONS 298
5 M1-07-29:12:06: WM$LOCK_TABLE_TYPE 11401
9 M1-12-06:13:00: SDO_NUMBER_ARRAY 58201
5 M2-10-23:15:20: AQ$_JMS_MESSAGES 7995
9 M7-04-12:12:59: TSM$SESSION_ID 5005
5 M7-10-15:10:09: ALL_ENCRYPTED_COLUMNS 2847
9 M7-10-15:10:09: ALL_TABLES 2925
5 M7-10-15:10:09: ALL_TAB_COMMENTS 2951
9 M7-10-15:10:09: APPLY$_ERROR_HANDLER_SEQUENCE 663
5 M7-10-15:10:09: ASSEMBLY$ 221
9 M7-10-15:10:09: AUD$ 351
6 N1-12-06:13:00: SDO_STRING_ARRAY 58202
10 N2-07-18:08:04: SI_STILLIMAGE 54848
6 N2-10-23:15:20: AQ$_JMS_TEXT_MESSAGES 7996
9 N7-04-12:12:59: TSM$SESSION_ID_LIST 5006
6 N7-10-15:10:09: ALL_OBJECT_TABLES 2926
10 N7-10-15:10:09: ALL_TAB_COMMENTS 2952
5 N7-10-15:10:09: APPLY$_ERROR_HANDLER 664
9 N7-10-15:10:09: AW_IND$ 742
5 N7-10-15:10:09: BOOTSTRAP$ 40
10 N7-10-15:10:09: DATE 976
6 O2-07-17:16:54: SDO_TOPO_GEOMETRY 58333
9 O2-07-18:08:04: SI_AVERAGECOLOR 54849
5 O2-10-23:15:20: AQ$_JMS_BYTES_MESSAGES 7997
9 O6-08-06:16:01: SDO_PC_BLK_TYPE 61661
5 O7-10-15:10:09: ALL_JOIN_IND_COLUMNS 2875
10 O7-10-15:10:09: ALL_OBJECT_TABLES 2927
5 O7-10-15:10:09: APPLY$_ERROR_HANDLER_UNQ 665
9 O7-10-15:10:09: APPLY$_SOURCE_OBJ 639
5 O7-10-15:10:09: DBA_CLU_COLUMNS 2823
11 O7-10-15:10:09: DBA_TAB_COMMENTS 2953
5 P1-07-29:12:06: WM$CONFLICT_PAYLOAD_TYPE 11430
12 P2-07-18:08:04: SI_COLORHISTOGRAM 54850
7 P2-10-23:15:20: AQ$_JMS_MAP_MESSAGES 7998
9 P6-02-16:10:51: SDO_PC 61662
6 P7-04-12:12:59: AQ$_AGENT 5190
9 P7-10-15:10:09: ALL_ALL_TABLES 2928
5 P7-10-15:10:09: ALL_JOIN_IND_COLUMNS 2876
10 P7-10-15:10:09: APPLY$_CONF_HDLR_COLUMNS 666
6 P7-10-15:10:09: COLUMN_PRIVILEGES 2798
9 P7-10-15:10:09: DBA_TAB_COMMENTS 2954
9 Q2-07-18:08:04: SI_POSITIONALCOLOR 54851
9 Q2-10-23:15:20: AQ$_JMS_STREAM_MESSAGES 7999
9 Q6-02-03:13:00: SDO_STRING2_ARRAY 61195
9 Q7-04-12:12:59: AQ$_DEQUEUE_HISTORY 5191
9 Q7-10-15:10:09: ALL_ALL_TABLES 2929
9 Q7-10-15:10:09: ALL_LOG_GROUP_COLUMNS 2773
9 Q7-10-15:10:09: ALL_SEQUENCES 2903
9 Q7-10-15:10:09: APPLY$_CONF_HDLR_COLUMNS_UNQ1 667
9 Q7-10-15:10:09: AUDSES$ 329
9 Q7-10-15:10:09: CCOL$ 43
10 R2-07-18:08:04: SI_TEXTURE 54852
10 R2-10-23:15:20: AQ$_JMS_OBJECT_MESSAGES 8000
10 R6-02-03:13:00: SDO_STRING2_ARRAYSET 61196
10 R6-05-31:09:18: SDO_TIN_BLK 61664
9 R7-04-12:12:59: AQ$_SUBSCRIBERS 5192
9 R7-04-12:12:59: SQL_BIND 5634
9 R7-10-15:10:09: ALL_COL_COMMENTS 2826
10 R7-10-15:10:09: ALL_LOG_GROUP_COLUMNS 2774
10 R7-10-15:10:09: ALL_SEQUENCES 2904
10 R7-10-15:10:09: APPLY$_CONF_HDLR_COLUMNS_UNQ2 668
9 S2-07-18:08:04: SI_FEATURELIST 54853
11 S2-10-23:15:30: AQ$_JMS_MESSAGE_PROPERTY 8001
11 S7-04-12:12:59: AQ$_RECIPIENTS 5193
10 S7-04-12:12:59: RE$NV_NODE 4751
10 S7-10-15:10:09: ALL_COL_COMMENTS 2827
10 S7-10-15:10:09: ALL_INDEXES 2853
11 S7-10-15:10:09: ALL_TAB_PRIVS 2957
11 S7-10-15:10:09: APPLY$_SOURCE_SCHEMA 643
11 S7-10-15:10:09: AW_OBJ$ 747
11 S7-10-15:10:09: CDC_CHANGE_SOURCES$ 591
10 T2-10-23:15:31: AQ$_JMS_MESSAGE_PROPERTIES 8002
11 T7-04-12:12:59: AQ$_HISTORY 5194
11 T7-04-12:12:59: RE$NV_ARRAY 4752
11 T7-04-12:12:59: SQL_BIND_SET 5636
12 T7-04-12:12:59: STREAMS$NV_NODE 9900
8 T7-10-15:10:09: ALL_INDEXES 2854
10 T7-10-15:10:09: ALL_LOBS 2802
9 T7-10-15:10:09: ALL_TAB_PRIVS 2958
10 T7-10-15:10:09: CLUSTER_DATABASES 280
8 T7-10-15:10:09: COLLECTION$ 462
10 U2-10-23:15:33: AQ$_JMS_ARRAY_MSGID_INFO 8003
10 U7-04-12:12:59: AQ$_DEQUEUE_HISTORY_T 5195
11 U7-04-12:12:59: STREAMS$NV_ARRAY 9901
9 U7-10-15:10:09: ALL_LOBS 2803
12 U7-10-15:10:09: APPLY$_VIRTUAL_OBJ_CONS 645
11 U7-10-15:10:09: APPROLE$ 359
13 U7-10-15:10:09: AW_PROP$ 749
9 U7-10-15:10:09: CDC_CHANGE_SETS$ 593
12 U7-10-15:10:09: CLUSTER_NODES 281
11 U7-10-15:10:09: CMPCOL_UNIQ_IDX1 723
8 V2-07-17:16:42: SDO_TOPO_GEOMETRY_LAYER 58314
15 V2-10-23:15:34: AQ$_JMS_ARRAY_MSGIDS 8004
8 V6-08-06:16:05: SDO_TIN_BLK_TYPE 61668
11 V7-04-12:12:59: SQL_PLAN_ROW_TYPE 4676
9 V7-04-12:12:59: STREAMS$TRANSFORMATION_INFO 9902
12 V7-10-15:10:09: ALL_OBJECTS 2882
9 V7-10-15:10:09: CLUSTER_INSTANCES 282
11 V7-10-15:10:09: COLLELEMIND 464
12 V7-10-15:10:09: COMPARISON_SCAN$ 724
12 V7-10-15:10:09: C_TS# 22
8 W0-09-28:12:59: AQ$_SIG_PROP 5197
9 W1-09-26:18:27: RE$NV_LIST 4755
8 W2-10-23:15:33: AQ$_JMS_ARRAY_ERROR_INFO 8005
16 W6-01-09:10:00: SDO_RANGE 58211
15 W6-02-16:10:52: SDO_TIN 61669
12 W7-04-09:14:32: ST_ANNOTATIONTEXTELEMENT 58887
12 W7-10-15:10:09: ALL_OBJECTS 2883
12 W7-10-15:10:09: APPLY$_CONSTRAINT_COLUMNS 647
11 W7-10-15:10:09: ATTRIBUTE$ 465
11 W7-10-15:10:09: AW_TRACK$ 751
8 X2-07-17:16:45: SDO_TOPO_GEOMETRY_LAYER_ARRAY 58316
13 X2-10-23:15:34: AQ$_JMS_ARRAY_ERRORS 8006
8 X6-01-09:10:00: SDO_RANGE_ARRAY 58212
11 X7-04-12:12:59: SQL_PLAN_TABLE_TYPE 4678
9 X7-05-02:14:39: ST_ANNOT_TEXTELEMENT_ARRAY 58888
10 X7-10-15:10:09: ALL_COL_PRIVS 2832
10 X7-10-15:10:09: APPLY$_CONSTRAINT_COLUMNS_UIX1 648
16 X7-10-15:10:09: CACHE_STATS_1$ 388
15 X7-10-15:10:09: CATALOG 2780
14 X7-10-15:10:09: COL$ 24
8 Y7-04-09:14:30: ST_ANNOTATIONTEXTELEMENT_ARRAY 58889
15 Y7-10-15:10:09: ACCESS$ 103
9 Y7-10-15:10:09: ALL_COL_PRIVS 2833
10 Y7-10-15:10:09: ALL_IND_COLUMNS 2859
16 Y7-10-15:10:09: ALL_TAB_PRIVS_MADE 2963
16 Y7-10-15:10:09: APPLY$_CONSTRAINT_COLUMNS_IDX1 649
15 Y7-10-15:10:09: AW_PRG$ 753
17 Y7-10-15:10:09: CATALOG 2781
16 Y7-10-15:10:09: CDC_SUBSCRIBERS$ 597
15 Y7-10-15:10:09: CDEF$ 51
250 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3211063680
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3414 | 366K| | 96 (3)| 00:00:02 |
|* 1 | VIEW | | 3414 | 366K| | 96 (3)| 00:00:02 |
|* 2 | WINDOW SORT PUSHED RANK | | 3414 | 160K| 424K| 96 (3)| 00:00:02 |
| 3 | WINDOW SORT | | 3414 | 160K| 424K| 96 (3)| 00:00:02 |
| 4 | NESTED LOOPS | | 3414 | 160K| | 8 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 2 | | 2 (0)| 00:00:01 |
| 6 | COUNT | | | | | | |
|* 7 | CONNECT BY WITHOUT FILTERING| | | | | | |
| 8 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | CUSTOMER | 3414 | 153K| | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("I2"<=10)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "CHARACTER" ORDER BY "FNAME","SNAME","ID")<=10)
7 - filter(LEVEL<=26)
9 - access("FNAME">="ALPHABET"."CHARACTER" AND "FNAME" IS NOT NULL)
SCOTT@orcl_11g>
|
|
|
|
|
|
|
|
|
|
| Re: Huge Query [message #308036 is a reply to message #308026] |
Thu, 20 March 2008 17:03 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
Barbara,
Yes, that statement about 260 rows was too rushed on my side.
As far as perfomance goes on my real 10^6 row table, your query takes 46sec and the giant UNION ALL query takes only 55ms.
Both queries use the same index.
As you can see the difference is Huge !
Below is a plan for your query (46sec):
Execution Plan
----------------------------------------------------------
Plan hash value: 2205094221
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21058 | 2262K| | 343 (3)| 00:00:05 |
|* 1 | VIEW | | 21058 | 2262K| | 343 (3)| 00:00:05 |
|* 2 | WINDOW SORT PUSHED RANK | | 21058 | 534K| 1512K| 343 (3)| 00:00:05 |
| 3 | WINDOW SORT | | 21058 | 534K| 1512K| 343 (3)| 00:00:05 |
| 4 | NESTED LOOPS | | 21058 | 534K| | 21 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 2 | | 2 (0)| 00:00:01 |
| 6 | COUNT | | | | | | |
|* 7 | CONNECT BY WITHOUT FILTERING| | | | | | |
| 8 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX_FNAME_SNAME_ID | 21058 | 493K| | 19 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("I2"<=10)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "CHARACTER" ORDER BY "FNAME","SNAME","ID")<=10)
7 - filter(LEVEL<=26)
9 - access("FNAME">="ALPHABET"."CHARACTER" AND "FNAME" IS NOT NULL)
Statistics
----------------------------------------------------------
306 recursive calls
42 db block gets
20645 consistent gets
64714 physical reads
0 redo size
9109 bytes sent via SQL*Net to client
583 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
1 sorts (memory)
2 sorts (disk)
260 rows processed
SQL>
An here is the plan for the huge UNION ALL query (55ms):
Execution Plan
----------------------------------------------------------
Plan hash value: 1541203473
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 260 | 24700 | 52387 (91)| 00:10:29 |
| 1 | UNION-ALL | | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 420K| 38M| 4988 (2)| 00:01:00 |
| 4 | WINDOW NOSORT | | 420K| 9866K| 4988 (2)| 00:01:00 |
|* 5 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 420K| 9866K| 1976 (1)| 00:00:24 |
|* 6 | COUNT STOPKEY | | | | | |
| 7 | VIEW | | 360K| 32M| 4274 (2)| 00:00:52 |
| 8 | WINDOW NOSORT | | 360K| 8453K| 4274 (2)| 00:00:52 |
|* 9 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 360K| 8453K| 1694 (1)| 00:00:21 |
|* 10 | COUNT STOPKEY | | | | | |
| 11 | VIEW | | 338K| 30M| 4010 (2)| 00:00:49 |
| 12 | WINDOW NOSORT | | 338K| 7929K| 4010 (2)| 00:00:49 |
|* 13 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 338K| 7929K| 1589 (1)| 00:00:20 |
|* 14 | COUNT STOPKEY | | | | | |
| 15 | VIEW | | 335K| 30M| 3973 (2)| 00:00:48 |
| 16 | WINDOW NOSORT | | 335K| 7857K| 3973 (2)| 00:00:48 |
|* 17 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 335K| 7857K| 1574 (1)| 00:00:19 |
|* 18 | COUNT STOPKEY | | | | | |
| 19 | VIEW | | 320K| 29M| 3796 (2)| 00:00:46 |
| 20 | WINDOW NOSORT | | 320K| 7502K| 3796 (2)| 00:00:46 |
|* 21 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 320K| 7502K| 1503 (1)| 00:00:19 |
|* 22 | COUNT STOPKEY | | | | | |
| 23 | VIEW | | 296K| 26M| 3519 (2)| 00:00:43 |
| 24 | WINDOW NOSORT | | 296K| 6958K| 3519 (2)| 00:00:43 |
|* 25 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 296K| 6958K| 1394 (1)| 00:00:17 |
|* 26 | COUNT STOPKEY | | | | | |
| 27 | VIEW | | 293K| 26M| 3482 (2)| 00:00:42 |
| 28 | WINDOW NOSORT | | 293K| 6886K| 3482 (2)| 00:00:42 |
|* 29 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 293K| 6886K| 1380 (1)| 00:00:17 |
|* 30 | COUNT STOPKEY | | | | | |
| 31 | VIEW | | 284K| 25M| 3368 (2)| 00:00:41 |
| 32 | WINDOW NOSORT | | 284K| 6661K| 3368 (2)| 00:00:41 |
|* 33 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 284K| 6661K| 1335 (1)| 00:00:17 |
|* 34 | COUNT STOPKEY | | | | | |
| 35 | VIEW | | 272K| 24M| 3228 (2)| 00:00:39 |
| 36 | WINDOW NOSORT | | 272K| 6380K| 3228 (2)| 00:00:39 |
|* 37 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 272K| 6380K| 1279 (1)| 00:00:16 |
|* 38 | COUNT STOPKEY | | | | | |
| 39 | VIEW | | 259K| 23M| 3077 (2)| 00:00:37 |
| 40 | WINDOW NOSORT | | 259K| 6084K| 3077 (2)| 00:00:37 |
|* 41 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 259K| 6084K| 1220 (1)| 00:00:15 |
|* 42 | COUNT STOPKEY | | | | | |
| 43 | VIEW | | 214K| 19M| 2546 (2)| 00:00:31 |
| 44 | WINDOW NOSORT | | 214K| 5033K| 2546 (2)| 00:00:31 |
|* 45 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 214K| 5033K| 1010 (1)| 00:00:13 |
|* 46 | COUNT STOPKEY | | | | | |
| 47 | VIEW | | 179K| 16M| 2128 (2)| 00:00:26 |
| 48 | WINDOW NOSORT | | 179K| 4206K| 2128 (2)| 00:00:26 |
|* 49 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 179K| 4206K| 843 (1)| 00:00:11 |
|* 50 | COUNT STOPKEY | | | | | |
| 51 | VIEW | | 168K| 15M| 1995 (2)| 00:00:24 |
| 52 | WINDOW NOSORT | | 168K| 3942K| 1995 (2)| 00:00:24 |
|* 53 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 168K| 3942K| 791 (1)| 00:00:10 |
|* 54 | COUNT STOPKEY | | | | | |
| 55 | VIEW | | 96350 | 8938K| 1144 (2)| 00:00:14 |
| 56 | WINDOW NOSORT | | 96350 | 2258K| 1144 (2)| 00:00:14 |
|* 57 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 96350 | 2258K| 454 (1)| 00:00:06 |
|* 58 | COUNT STOPKEY | | | | | |
| 59 | VIEW | | 93311 | 8656K| 1108 (2)| 00:00:14 |
| 60 | WINDOW NOSORT | | 93311 | 2186K| 1108 (2)| 00:00:14 |
|* 61 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 93311 | 2186K| 440 (1)| 00:00:06 |
|* 62 | COUNT STOPKEY | | | | | |
| 63 | VIEW | | 91596 | 8497K| 1087 (2)| 00:00:14 |
| 64 | WINDOW NOSORT | | 91596 | 2146K| 1087 (2)| 00:00:14 |
|* 65 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 91596 | 2146K| 432 (1)| 00:00:06 |
|* 66 | COUNT STOPKEY | | | | | |
| 67 | VIEW | | 76501 | 7097K| 910 (2)| 00:00:11 |
| 68 | WINDOW NOSORT | | 76501 | 1792K| 910 (2)| 00:00:11 |
|* 69 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 76501 | 1792K| 361 (1)| 00:00:05 |
|* 70 | COUNT STOPKEY | | | | | |
| 71 | VIEW | | 75039 | 6961K| 892 (2)| 00:00:11 |
| 72 | WINDOW NOSORT | | 75039 | 1758K| 892 (2)| 00:00:11 |
|* 73 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 75039 | 1758K| 354 (1)| 00:00:05 |
|* 74 | COUNT STOPKEY | | | | | |
| 75 | VIEW | | 61788 | 5732K| 734 (2)| 00:00:09 |
| 76 | WINDOW NOSORT | | 61788 | 1448K| 734 (2)| 00:00:09 |
|* 77 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 61788 | 1448K| 293 (2)| 00:00:04 |
|* 78 | COUNT STOPKEY | | | | | |
| 79 | VIEW | | 47234 | 4382K| 564 (2)| 00:00:07 |
| 80 | WINDOW NOSORT | | 47234 | 1107K| 564 (2)| 00:00:07 |
|* 81 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 47234 | 1107K| 224 (1)| 00:00:03 |
|* 82 | COUNT STOPKEY | | | | | |
| 83 | VIEW | | 33397 | 3098K| 401 (2)| 00:00:05 |
| 84 | WINDOW NOSORT | | 33397 | 782K| 401 (2)| 00:00:05 |
|* 85 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 33397 | 782K| 159 (1)| 00:00:02 |
|* 86 | COUNT STOPKEY | | | | | |
| 87 | VIEW | | 30305 | 2811K| 363 (2)| 00:00:05 |
| 88 | WINDOW NOSORT | | 30305 | 710K| 363 (2)| 00:00:05 |
|* 89 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 30305 | 710K| 144 (1)| 00:00:02 |
|* 90 | COUNT STOPKEY | | | | | |
| 91 | VIEW | | 28604 | 2653K| 343 (2)| 00:00:05 |
| 92 | WINDOW NOSORT | | 28604 | 670K| 343 (2)| 00:00:05 |
|* 93 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 28604 | 670K| 136 (1)| 00:00:02 |
|* 94 | COUNT STOPKEY | | | | | |
| 95 | VIEW | | 12430 | 1153K| 152 (2)| 00:00:02 |
| 96 | WINDOW NOSORT | | 12430 | 291K| 152 (2)| 00:00:02 |
|* 97 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 12430 | 291K| 61 (2)| 00:00:01 |
|* 98 | COUNT STOPKEY | | | | | |
| 99 | VIEW | | 12430 | 1153K| 152 (2)| 00:00:02 |
| 100 | WINDOW NOSORT | | 12430 | 291K| 152 (2)| 00:00:02 |
|*101 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 12430 | 291K| 61 (2)| 00:00:01 |
|*102 | COUNT STOPKEY | | | | | |
| 103 | VIEW | | 12430 | 1153K| 152 (2)| 00:00:02 |
| 104 | WINDOW NOSORT | | 12430 | 291K| 152 (2)| 00:00:02 |
|*105 | INDEX RANGE SCAN| IDX_FNAME_SNAME_ID | 12430 | 291K| 61 (2)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=10)
5 - access("FNAME">='A' AND "FNAME" IS NOT NULL)
6 - filter(ROWNUM<=10)
9 - access("FNAME">='B' AND "FNAME" IS NOT NULL)
10 - filter(ROWNUM<=10)
13 - access("FNAME">='C' AND "FNAME" IS NOT NULL)
14 - filter(ROWNUM<=10)
17 - access("FNAME">='D' AND "FNAME" IS NOT NULL)
18 - filter(ROWNUM<=10)
21 - access("FNAME">='E' AND "FNAME" IS NOT NULL)
22 - filter(ROWNUM<=10)
25 - access("FNAME">='F' AND "FNAME" IS NOT NULL)
26 - filter(ROWNUM<=10)
29 - access("FNAME">='G' AND "FNAME" IS NOT NULL)
30 - filter(ROWNUM<=10)
33 - access("FNAME">='H' AND "FNAME" IS NOT NULL)
34 - filter(ROWNUM<=10)
37 - access("FNAME">='I' AND "FNAME" IS NOT NULL)
38 - filter(ROWNUM<=10)
41 - access("FNAME">='J' AND "FNAME" IS NOT NULL)
42 - filter(ROWNUM<=10)
45 - access("FNAME">='K' AND "FNAME" IS NOT NULL)
46 - filter(ROWNUM<=10)
49 - access("FNAME">='L' AND "FNAME" IS NOT NULL)
50 - filter(ROWNUM<=10)
53 - access("FNAME">='M' AND "FNAME" IS NOT NULL)
54 - filter(ROWNUM<=10)
57 - access("FNAME">='N' AND "FNAME" IS NOT NULL)
58 - filter(ROWNUM<=10)
61 - access("FNAME">='O' AND "FNAME" IS NOT NULL)
62 - filter(ROWNUM<=10)
65 - access("FNAME">='P' AND "FNAME" IS NOT NULL)
66 - filter(ROWNUM<=10)
69 - access("FNAME">='Q' AND "FNAME" IS NOT NULL)
70 - filter(ROWNUM<=10)
73 - access("FNAME">='R' AND "FNAME" IS NOT NULL)
74 - filter(ROWNUM<=10)
77 - access("FNAME">='S' AND "FNAME" IS NOT NULL)
78 - filter(ROWNUM<=10)
81 - access("FNAME">='T' AND "FNAME" IS NOT NULL)
82 - filter(ROWNUM<=10)
85 - access("FNAME">='U' AND "FNAME" IS NOT NULL)
86 - filter(ROWNUM<=10)
89 - access("FNAME">='V' AND "FNAME" IS NOT NULL)
90 - filter(ROWNUM<=10)
93 - access("FNAME">='W' AND "FNAME" IS NOT NULL)
94 - filter(ROWNUM<=10)
97 - access("FNAME">='X' AND "FNAME" IS NOT NULL)
98 - filter(ROWNUM<=10)
101 - access("FNAME">='Y' AND "FNAME" IS NOT NULL)
102 - filter(ROWNUM<=10)
105 - access("FNAME">='Z' AND "FNAME" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
97 consistent gets
0 physical reads
0 redo size
8530 bytes sent via SQL*Net to client
5746 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
260 rows processed
SQL>
|
|
|
|
|
|
| Re: Huge Query [message #308042 is a reply to message #308037] |
Thu, 20 March 2008 17:44 |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
|
Unless I am mistaken, the answer is no, you don't show multiple "letters" simultaneously. You have made an assumption that the network traffic created by users jumping around would be significant. I would bet, however, that if you put a traffic monitor on the network that you couldn't find, even if you tried, when users click on a link to get ten records. I know that the load on the DB would be trivial for multiple hits of ten records. So, unless I am very mistaken, you are going through a lot of work for no real benefit. But I've been wrong before.
|
|
|
|
| Re: Huge Query [message #308043 is a reply to message #308042] |
Thu, 20 March 2008 17:53 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
Taken out of context, this problem might seem that way.
But this is a part of much bigger picture that is beyond the scope of this thread.
In the end, I can keep using the huge UNION ALL query just like I've been for a year, but it's ugly. Not everything that works well is elegant, but that's philosophy.
|
|
|
|
| Re: Huge Query [message #308044 is a reply to message #307966] |
Thu, 20 March 2008 17:59 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
BLimey, you dont want it simpler AND quicker do you?
This is only "slightly" simpler syntax (although no need for hint to use index)
Is it any quicker?
I am wondering if a single 250 row window sort might be quicker than oracle creating 26 nosort windows. Probably not, but it still should be fast.
SQL>
SQL> select Row_number() OVER(PARTITION BY fName,sName ORDER BY fName, sName, Id) i,
2 fName,
3 sName,
4 Id
5 from
6 (
7 select fname, sname, id from customer where fname >= 'A' and rownum <= 10
8 union all
9 select fname, sname, id from customer where fname >= 'B' and rownum <= 10
10 union all
11 select fname, sname, id from customer where fname >= 'C' and rownum <= 10
12 union all
13 select fname, sname, id from customer where fname >= 'D' and rownum <= 10
14 union all
15 select fname, sname, id from customer where fname >= 'E' and rownum <= 10
16 union all
17 select fname, sname, id from customer where fname >= 'F' and rownum <= 10
18 union all
19 select fname, sname, id from customer where fname >= 'G' and rownum <= 10
20 union all
21 select fname, sname, id from customer where fname >= 'H' and rownum <= 10
22 union all
23 select fname, sname, id from customer where fname >= 'I' and rownum <= 10
24 union all
25 select fname, sname, id from customer where fname >= 'J' and rownum <= 10
26 union all
27 select fname, sname, id from customer where fname >= 'K' and rownum <= 10
28 union all
29 select fname, sname, id from customer where fname >= 'L' and rownum <= 10
30 union all
31 select fname, sname, id from customer where fname >= 'M' and rownum <= 10
32 union all
33 select fname, sname, id from customer where fname >= 'N' and rownum <= 10
34 union all
35 select fname, sname, id from customer where fname >= 'O' and rownum <= 10
36 union all
37 select fname, sname, id from customer where fname >= 'P' and rownum <= 10
38 union all
39 select fname, sname, id from customer where fname >= 'Q' and rownum <= 10
40 union all
41 select fname, sname, id from customer where fname >= 'R' and rownum <= 10
42 union all
43 select fname, sname, id from customer where fname >= 'S' and rownum <= 10
44 union all
45 select fname, sname, id from customer where fname >= 'T' and rownum <= 10
46 union all
47 select fname, sname, id from customer where fname >= 'U' and rownum <= 10
48 union all
49 select fname, sname, id from customer where fname >= 'V' and rownum <= 10
50 union all
51 select fname, sname, id from customer where fname >= 'W' and rownum <= 10
52 union all
53 select fname, sname, id from customer where fname >= 'X' and rownum <= 10
54 union all
55 select fname, sname, id from customer where fname >= 'Y' and rownum <= 10
56 union all
57 select fname, sname, id from customer where fname >= 'Z' and rownum <= 10
58 );
1 A1-12-06:13:00: SDO_RASTERSET
1 A2-10-23:15:20: AQ$_JMS_MAP_MESSAGES
1 A7-04-12:12:59: RE$NV_NODE
1 A7-04-17:03:34: DEFSUBPARTLOB$
1 A7-04-17:03:34: ICOL$
1 A7-04-17:03:34: INDPART$
1 A7-04-17:03:34: I_COL1
1 A7-04-17:03:34: I_DEFROLE1
1 A7-04-17:03:34: I_DIMJOINKEY$_1
1 A7-04-17:03:34: I_ICOLDEP$_OBJ
1 B1-12-06:13:00: SDO_GEOR_METADATA
1 B2-07-18:08:04: SI_STILLIMAGE
1 B2-10-23:15:20: AQ$_JMS_STREAM_MESSAGES
1 B7-04-12:12:59: RE$NV_ARRAY
1 B7-04-17:03:34: I_DEFSUBPARTLOB$
1 B7-04-17:03:34: I_DIMJOINKEY$_2
1 B7-04-17:03:34: I_INDPART_BOPART$
1 B7-04-17:03:34: I_LINK1
1 B7-04-17:03:34: I_PROFNAME
1 B7-04-17:03:34: I_PROXY_DATA$
1 C1-12-06:13:00: SDO_GEOR_SRS
1 C2-07-18:08:04: SI_AVERAGECOLOR
1 C2-10-23:15:20: AQ$_JMS_OBJECT_MESSAGES
1 C7-04-17:03:34: CON$
1 C7-04-17:03:34: DIMATTR$
1 C7-04-17:03:34: I_COM1
1 C7-04-17:03:34: I_INDPART_OBJ$
1 C7-04-17:03:34: I_METAVIEW$
1 C7-04-17:03:34: I_OBJ1
1 C7-04-17:03:34: I_PROFILE
1 D1-12-06:13:00: SDO_GEOR_HISTOGRAM
1 D2-07-18:08:04: SI_COLORHISTOGRAM
1 D2-10-23:15:30: AQ$_JMS_MESSAGE_PROPERTY
1 D7-04-17:03:34: I_COL2
1 D7-04-17:03:34: I_DIMATTR$_1
1 D7-04-17:03:34: I_PROCEDURE1
1 D7-04-17:03:34: I_RGJOB
1 D7-04-17:03:34: I_SNAP_LOADERTIME1
1 D7-04-17:03:34: I_SYSTEM_PRIVILEGE_MAP
1 D7-04-17:03:34: I_TYPE5
1 E1-12-06:13:00: SDO_GEOR_GRAYSCALE
1 E2-07-18:08:04: SI_POSITIONALCOLOR
1 E2-10-23:15:31: AQ$_JMS_MESSAGE_PROPERTIES
1 E7-04-17:03:34: C_COBJ#
1 E7-04-17:03:34: C_MLOG#
1 E7-04-17:03:34: I_DIMATTR$_2
1 E7-04-17:03:34: I_METAFILTER$
1 E7-04-17:03:34: I_OBJ2
1 E7-04-17:03:34: I_PROCEDUREINFO1
1 E7-04-17:03:34: I_SNAP_REFTIME1
1 F0-08-26:11:25: SDO_MBR
1 F1-12-06:13:00: SDO_GEOR_COLORMAP
1 F2-07-17:16:42: SDO_TOPO_GEOMETRY_LAYER
1 F2-07-18:08:04: SI_TEXTURE
1 F2-10-23:15:33: AQ$_JMS_ARRAY_MSGID_INFO
1 F3-05-20:10:08: WM$NV_PAIR_TYPE
1 F7-04-12:12:59: AQ$_AGENT
1 F7-04-17:03:34: HIER$
1 F7-04-17:03:34: I_ARGUMENT1
1 F7-04-17:03:34: I_CCOL1
1 G1-07-29:12:08: WM$ED_UNDO_CODE_NODE_TYPE
1 G2-07-18:08:04: SI_FEATURELIST
1 G2-10-23:15:34: AQ$_JMS_ARRAY_MSGIDS
1 G3-05-20:10:08: WM$NV_PAIR_NT_TYPE
1 G7-04-12:12:59: AQ$_DEQUEUE_HISTORY
1 G7-04-17:03:34: COLLECTION$
1 G7-04-17:03:34: DUC$
1 G7-04-17:03:34: I_ARGUMENT2
1 G7-04-17:03:34: I_HIER$_1
1 G7-04-17:03:34: I_MLOG_REFCOL1
1 H1-07-29:12:08: WM$ED_UNDO_CODE_TABLE_TYPE
1 H2-07-17:16:45: SDO_TOPO_GEOMETRY_LAYER_ARRAY
1 H2-10-23:15:33: AQ$_JMS_ARRAY_ERROR_INFO
1 H3-05-20:10:08: WM$EVENT_TYPE
1 H4-09-28:09:38: SDO_CART_TEXT
1 H7-04-12:12:59: AQ$_SUBSCRIBERS
1 H7-04-17:03:34: ATTRIBUTE$
1 H7-04-17:03:34: C_FILE#_BLOCK#
1 H7-04-17:03:34: HIERLEVEL$
1 H7-04-17:03:34: INCEXP
1 I2-10-14:18:00: SDO_SMPL_GEOMETRY
1 I2-10-23:15:34: AQ$_JMS_ARRAY_ERRORS
1 I3-05-21:10:52: WM_PERIOD
1 I7-04-12:12:59: AQ$_RECIPIENTS
1 I7-04-17:03:34: C_OBJ#_INTCOL#
1 I7-04-17:03:34: C_USER#
1 I7-04-17:03:34: INDSUBPART$
1 I7-04-17:03:34: I_ATTRIBUTE1
1 I7-04-17:03:34: I_CDEF2
1 I7-04-17:03:34: I_HIERLEVEL$_1
1 J2-08-01:09:48: SDO_LIST_TYPE
1 J2-10-01:12:41: SDO_REGION
1 J7-04-12:12:59: AQ$_HISTORY
1 J7-04-17:03:34: COLTYPE$
1 J7-04-17:03:34: DEFROLE$
1 J7-04-17:03:34: INCVID
1 J7-04-17:03:34: INDTYPES$
1 J7-04-17:03:34: I_ATTRIBUTE2
1 J7-04-17:03:34: I_IDL_CHAR1
1 J7-04-17:03:34: I_INDSUBPART_POBJSUBPART$
1 K2-10-01:12:46: SDO_REGIONSET
1 K4-04-01:14:30: SDO_RDF_TRIPLE_S
1 K7-04-12:12:59: AQ$_DEQUEUE_HISTORY_T
1 K7-04-12:12:59: TSM$SESSION_ID
1 K7-04-17:03:34: BOOTSTRAP$
1 K7-04-17:03:34: FILE$
1 K7-04-17:03:34: HISTGRM$
1 K7-04-17:03:34: INCFIL
1 K7-04-17:03:34: INDOP$
1 K7-04-17:03:34: I_COLTYPE1
1 L7-04-12:12:59: SQL_PLAN_ROW_TYPE
1 L7-04-12:12:59: TSM$SESSION_ID_LIST
1 L7-04-17:03:34: AUDSES$
1 L7-04-17:03:34: FET$
1 L7-04-17:03:34: INDARRAYTYPE$
1 L7-04-17:03:34: I_COLTYPE2
1 L7-04-17:03:34: I_H_OBJ#_COL#
1 L7-04-17:03:34: I_IDL_SB41
1 L7-04-17:03:34: I_METASCRIPT2$
1 L7-04-17:03:34: I_METHOD1
1 M0-09-28:12:59: AQ$_SIG_PROP
1 M1-07-29:12:06: WM$LOCK_INFO_TYPE
1 M7-04-17:03:34: AUDIT$
1 M7-04-17:03:34: DEPENDENCY$
1 M7-04-17:03:34: HIST_HEAD$
1 M7-04-17:03:34: I_DIR1
1 M7-04-17:03:34: I_FILE#_BLOCK#
1 M7-04-17:03:34: I_SNAP_OBJCOL1
1 M7-04-17:03:34: I_SUPEROBJ1
1 M7-04-17:03:34: I_TAB1
1 N1-07-29:12:06: WM$LOCK_TABLE_TYPE
1 N1-12-06:13:00: SDO_NUMBER_ARRAY
1 N3-12-25:09:11: SDO_TOPO_NSTD_TBL
1 N7-04-12:12:59: SQL_PLAN_TABLE_TYPE
1 N7-04-17:03:34: ACCESS$
1 N7-04-17:03:34: ASSOCIATION$
1 N7-04-17:03:34: CCOL$
1 N7-04-17:03:34: EXPACT$
1 N7-04-17:03:34: I_AUDIT
1 N7-04-17:03:34: I_FILE1
1 O1-12-06:13:00: SDO_STRING_ARRAY
1 O7-04-12:12:59: SQLPROF_ATTR
1 O7-04-17:03:34: ASSOC1
1 O7-04-17:03:34: ATTRCOL$
1 O7-04-17:03:34: EXPPKGOBJ$
1 O7-04-17:03:34: INDCOMPART$
1 O7-04-17:03:34: I_CON1
1 O7-04-17:03:34: I_ERROR1
1 O7-04-17:03:34: I_HH_OBJ#_INTCOL#
1 O7-04-17:03:34: I_METASCRIPTFILTER2$
1 P1-09-26:18:27: RE$NV_LIST
1 P7-04-17:03:34: ASSOC2
1 P7-04-17:03:34: DUAL
1 P7-04-17:03:34: I_ATTRCOL1
1 P7-04-17:03:34: I_CON2
1 P7-04-17:03:34: I_INDCOMPART_BOPART$
1 P7-04-17:03:34: I_NTAB2
1 P7-04-17:03:34: I_OBJ3
1 P7-04-17:03:34: I_OBJTYPE
1 P7-04-17:03:34: I_PARAMETER1
1 Q1-07-29:12:06: WM$CONFLICT_PAYLOAD_TYPE
1 Q3-04-18:00:00: STANDARD
1 Q7-04-17:03:34: DUAL
1 Q7-04-17:03:34: EXPPKGACT$
1 Q7-04-17:03:34: I_COBJ#
1 Q7-04-17:03:34: I_DEPENDENCY1
1 Q7-04-17:03:34: I_INDCOMPART$
1 Q7-04-17:03:34: I_METANAMETRANS1$
1 Q7-04-17:03:34: I_NTAB3
1 Q7-04-17:03:34: I_PARAMETER2
1 R3-06-10:13:37: ORDIMAGESIGNATURE
1 R7-04-17:03:34: ARGUMENT$
1 R7-04-17:03:34: COM$
1 R7-04-17:03:34: I_ACTPACKAGE
1 R7-04-17:03:34: I_CDEF4
1 R7-04-17:03:34: I_DEPENDENCY2
1 R7-04-17:03:34: I_METANAMETRANS2$
1 R7-04-17:03:34: I_RLS_CTX
1 R7-04-17:03:34: I_SNAP_SITE1
1 R7-04-17:03:34: I_SUMQB$_3
1 S1-01-08:11:44: ORDDOC
1 S7-04-12:12:59: SQL_PLAN_STAT_ROW_TYPE
1 S7-04-17:03:34: CONTEXT$
1 S7-04-17:03:34: DIM$
1 S7-04-17:03:34: EXPDEPOBJ$
1 S7-04-17:03:34: ID_GENS$
1 S7-04-17:03:34: IND$
1 S7-04-17:03:34: I_ACCESS1
1 S7-04-17:03:34: I_COL3
1 S7-04-17:03:34: I_KOPM1
1 T7-04-17:03:34: C_OBJ#
1 T7-04-17:03:34: IDL_UB1$
1 T7-04-17:03:34: I_CONTEXT
1 T7-04-17:03:34: I_DEPENDOBJ
1 T7-04-17:03:34: I_DIM$_1
1 T7-04-17:03:34: I_JAVASNM1
1 T7-04-17:03:34: I_LOB1
1 T7-04-17:03:34: I_LOBFRAG_PARENTOBJFRAG$
1 T7-04-17:03:34: I_METAPATHMAP$
1 T7-04-17:03:34: I_REFCON2
1 U2-07-18:08:04: SI_COLOR
1 U7-04-12:12:59: SQL_BIND
1 U7-04-12:12:59: STREAMS$NV_NODE
1 U7-04-17:03:34: ATEMPTAB$
1 U7-04-17:03:34: C_TS#
1 U7-04-17:03:34: DIMLEVEL$
1 U7-04-17:03:34: EXTERNAL_TAB$
1 U7-04-17:03:34: IDL_CHAR$
1 U7-04-17:03:34: I_LOB2
1 U7-04-17:03:34: I_LOBFRAG$_FRAGOBJ$
1 V7-04-12:12:59: STREAMS$NV_ARRAY
1 V7-04-17:03:34: ATEMPIND$
1 V7-04-17:03:34: CDEF$
1 V7-04-17:03:34: EXPDEPACT$
1 V7-04-17:03:34: IDL_UB2$
1 V7-04-17:03:34: INDPART_PARAM$
1 V7-04-17:03:34: I_DIMLEVEL$_1
1 V7-04-17:03:34: I_EXTERNAL_TAB1$
1 V7-04-17:03:34: I_FILE2
1 V7-04-17:03:34: I_JOB_JOB
1 W7-04-12:12:59: SQL_BIND_SET
1 W7-04-12:12:59: STREAMS$TRANSFORMATION_INFO
1 W7-04-17:03:34: COL$
1 W7-04-17:03:34: C_TOID_VERSION#
1 W7-04-17:03:34: DIMLEVELKEY$
1 W7-04-17:03:34: EXTERNAL_LOCATION$
1 W7-04-17:03:34: IDL_SB4$
1 W7-04-17:03:34: I_ACTOBJ
1 W7-04-17:03:34: I_CDEF1
1 W7-04-17:03:34: I_INDPART_PARAM
1 X2-07-17:16:54: SDO_TOPO_GEOMETRY
1 X2-10-23:15:20: AQ$_JMS_MESSAGES
1 X7-04-17:03:34: DIR$
1 X7-04-17:03:34: I_DIMLEVELKEY$_1
1 X7-04-17:03:34: I_EXTERNAL_LOCATION1$
1 X7-04-17:03:34: I_ICOL1
1 X7-04-17:03:34: I_LOBCOMPPART$_PARTOBJ$
1 X7-04-17:03:34: I_OBJAUTH1
1 X7-04-17:03:34: I_SUMDETAIL$_1
1 X7-04-17:03:34: I_SUMPARTLOG$_BOPART$
1 Y1-12-06:13:00: SDO_GEORASTER
1 Y2-10-23:15:20: AQ$_JMS_TEXT_MESSAGES
1 Y7-04-17:03:34: C_RG#
1 Y7-04-17:03:34: DEFSUBPART$
1 Y7-04-17:03:34: I_CDEF3
1 Y7-04-17:03:34: I_DIMLEVELKEY$_2
1 Y7-04-17:03:34: I_MON_MODS$_OBJ
1 Y7-04-17:03:34: I_OBJAUTH2
1 Y7-04-17:03:34: I_RESULT1
1 Y7-04-17:03:34: I_SUMDETAIL$_2
250 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2069413714
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 260 | 21320 | 79 (2)| 00:00:01 |
| 1 | WINDOW SORT | | 260 | 21320 | 79 (2)| 00:00:01 |
| 2 | VIEW | | 260 | 21320 | 78 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | COUNT STOPKEY | | | | | |
|* 5 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
|* 6 | COUNT STOPKEY | | | | | |
|* 7 | INDEX RANGE SCAN| USE_ME | 12 | 540 | 3 (0)| 00:00:01 |
|* 8 | COUNT STOPKEY | | | | | |
|* 9 | INDEX RANGE SCAN| USE_ME | 12 | 540 | 3 (0)| 00:00:01 |
|* 10 | COUNT STOPKEY | | | | | |
|* 11 | INDEX RANGE SCAN| USE_ME | 12 | 540 | 3 (0)| 00:00:01 |
|* 12 | COUNT STOPKEY | | | | | |
|* 13 | INDEX RANGE SCAN| USE_ME | 12 | 540 | 3 (0)| 00:00:01 |
|* 14 | COUNT STOPKEY | | | | | |
|* 15 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
|* 16 | COUNT STOPKEY | | | | | |
|* 17 | INDEX RANGE SCAN| USE_ME | 13 | 585 | 3 (0)| 00:00:01 |
|* 18 | COUNT STOPKEY | | | | | |
|* 19 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
|* 20 | COUNT STOPKEY | | | | | |
|* 21 | INDEX RANGE SCAN| USE_ME | 12 | 540 | 3 (0)| 00:00:01 |
|* 22 | COUNT STOPKEY | | | | | |
|* 23 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
|* 24 | COUNT STOPKEY | | | | | |
|* 25 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
|* 26 | COUNT STOPKEY | | | | | |
|* 27 | INDEX RANGE SCAN| USE_ME | 12 | 540 | 3 (0)| 00:00:01 |
|* 28 | COUNT STOPKEY | | | | | |
|* 29 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
|* 30 | COUNT STOPKEY | | | | | |
|* 31 | INDEX RANGE SCAN| USE_ME | 12 | 540 | 3 (0)| 00:00:01 |
|* 32 | COUNT STOPKEY | | | | | |
|* 33 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
|* 34 | COUNT STOPKEY | | | | | |
|* 35 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
|* 36 | COUNT STOPKEY | | | | | |
|* 37 | INDEX RANGE SCAN| USE_ME | 12 | 540 | 3 (0)| 00:00:01 |
|* 38 | COUNT STOPKEY | | | | | |
|* 39 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
|* 40 | COUNT STOPKEY | | | | | |
|* 41 | INDEX RANGE SCAN| USE_ME | 12 | 540 | 3 (0)| 00:00:01 |
|* 42 | COUNT STOPKEY | | | | | |
|* 43 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
|* 44 | COUNT STOPKEY | | | | | |
|* 45 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
|* 46 | COUNT STOPKEY | | | | | |
|* 47 | INDEX RANGE SCAN| USE_ME | 12 | 540 | 3 (0)| 00:00:01 |
|* 48 | COUNT STOPKEY | | | | | |
|* 49 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
|* 50 | COUNT STOPKEY | | | | | |
|* 51 | INDEX RANGE SCAN| USE_ME | 12 | 540 | 3 (0)| 00:00:01 |
|* 52 | COUNT STOPKEY | | | | | |
|* 53 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
|* 54 | COUNT STOPKEY | | | | | |
|* 55 | INDEX RANGE SCAN| USE_ME | 11 | 495 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(ROWNUM<=10)
5 - access("FNAME">='A' AND "FNAME" IS NOT NULL)
6 - filter(ROWNUM<=10)
7 - access("FNAME">='B' AND "FNAME" IS NOT NULL)
8 - filter(ROWNUM<=10)
9 - access("FNAME">='C' AND "FNAME" IS NOT NULL)
10 - filter(ROWNUM<=10)
11 - access("FNAME">='D' AND "FNAME" IS NOT NULL)
12 - filter(ROWNUM<=10)
13 - access("FNAME">='E' AND "FNAME" IS NOT NULL)
14 - filter(ROWNUM<=10)
15 - access("FNAME">='F' AND "FNAME" IS NOT NULL)
16 - filter(ROWNUM<=10)
17 - access("FNAME">='G' AND "FNAME" IS NOT NULL)
18 - filter(ROWNUM<=10)
19 - access("FNAME">='H' AND "FNAME" IS NOT NULL)
20 - filter(ROWNUM<=10)
21 - access("FNAME">='I' AND "FNAME" IS NOT NULL)
22 - filter(ROWNUM<=10)
23 - access("FNAME">='J' AND "FNAME" IS NOT NULL)
24 - filter(ROWNUM<=10)
25 - access("FNAME">='K' AND "FNAME" IS NOT NULL)
26 - filter(ROWNUM<=10)
27 - access("FNAME">='L' AND "FNAME" IS NOT NULL)
28 - filter(ROWNUM<=10)
29 - access("FNAME">='M' AND "FNAME" IS NOT NULL)
30 - filter(ROWNUM<=10)
31 - access("FNAME">='N' AND "FNAME" IS NOT NULL)
32 - filter(ROWNUM<=10)
33 - access("FNAME">='O' AND "FNAME" IS NOT NULL)
34 - filter(ROWNUM<=10)
35 - access("FNAME">='P' AND "FNAME" IS NOT NULL)
36 - filter(ROWNUM<=10)
37 - access("FNAME">='Q' AND "FNAME" IS NOT NULL)
38 - filter(ROWNUM<=10)
39 - access("FNAME">='R' AND "FNAME" IS NOT NULL)
40 - filter(ROWNUM<=10)
41 - access("FNAME">='S' AND "FNAME" IS NOT NULL)
42 - filter(ROWNUM<=10)
43 - access("FNAME">='T' AND "FNAME" IS NOT NULL)
44 - filter(ROWNUM<=10)
45 - access("FNAME">='U' AND "FNAME" IS NOT NULL)
46 - filter(ROWNUM<=10)
47 - access("FNAME">='V' AND "FNAME" IS NOT NULL)
48 - filter(ROWNUM<=10)
49 - access("FNAME">='W' AND "FNAME" IS NOT NULL)
50 - filter(ROWNUM<=10)
51 - access("FNAME">='X' AND "FNAME" IS NOT NULL)
52 - filter(ROWNUM<=10)
53 - access("FNAME">='Y' AND "FNAME" IS NOT NULL)
54 - filter(ROWNUM<=10)
55 - access("FNAME">='Z' AND "FNAME" IS NOT NULL)
SQL>
[Updated on: Thu, 20 March 2008 18:01] Report message to a moderator |
|
|
|
| Re: Huge Query [message #308049 is a reply to message #308044] |
Thu, 20 March 2008 18:44 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
coleing,
The problem is that a single query like this:
select fname, sname, id from customer where fname >= 'A' and rownum <= 10
...does not select the smallest 10 "FNAME"s beginning with 'A', but random 10 rows greater than 'A' (which could as well be 'WALTER', 'YOUNG', 'ZYXEL', etc...)
The order must be preserved. (isn't it what Hitler said ? )
Regards,
George Robinson
|
|
|
|
| Re: Huge Query [message #308075 is a reply to message #308049] |
Fri, 21 March 2008 01:18 |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
|
I was wondering if somebody would say that. Actually it does. Since it is driving off of the index on fname, it will pull the first 10 from the index, which will be ordered. I have no idea if this is documented but I have never seen it not work. Try it and see.
|
|
|
|
| Re: Huge Query [message #308099 is a reply to message #307966] |
Fri, 21 March 2008 03:03 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
Yes, you are correct, it still needs an order by to be sure.
select fname, sname, id from customer where fname >= 'A' and rownum <= 10
union all
select fname, sname, id from customer where fname >= 'B' and rownum <= 10
just becomes:-
select * from (select fname, sname, id from customer where fname >= 'A' order by 1,2,3) where rownum <= 10
union all
select * from (select fname, sname, id from customer where fname >= 'B' order by 1,2,3) where rownum <= 10
Although scott is correct in it return the right results. As long as you have plan stability (stored outline), and can guarantee the index stays the same, the results would be correct. That would be too many "if's" for me though.
Especially as OEM can have a flid with your stats from time to time!
[Updated on: Fri, 21 March 2008 03:06] Report message to a moderator |
|
|
|
| Re: Huge Query [message #308129 is a reply to message #308099] |
Fri, 21 March 2008 06:48 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If you are prepared to use a little PL/SQL rather than pure SQL, you can put it in a PILELINED table function.
Place the following SQL in a cursor:
SELECT /*+ INDEX(CUSTOMER CUSTOMER(FNAME,SNAME,ID)) FIRST_ROWS */
fName,
sName,
Id
FROM Customer
WHERE fName >= p_letter
ORDER BY fName,
sName,
Id
then fetch the first 10 rows from the cursor before closing it. Pipe each row to the function output.
Place all this in a cursor loop on your Alphabet SQL and voila - a minimal version of what you requested using the index to do the heavy sorting.
Ross Leishman
|
|
|
|
| Re: Huge Query [message #308238 is a reply to message #307966] |
Sat, 22 March 2008 11:00 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
| Quote: | If you are prepared to use a little PL/SQL rather than pure SQL,
|
Unfortunately I cannot, due to client access rights.
Regards,
George Robinson
|
|
|
|
| Re: Huge Query [message #308380 is a reply to message #308238] |
Mon, 24 March 2008 03:14 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Sorry, are you saying your client (a person) will not allow you to save any PL/SQL on the database? Or that the SQL client (a software program) will not allow you to submit PL/SQL calls.
If it is the latter, a PIPELINED function hides the PL/SQL from the client, which submits only pure SQL.
Ross Leishman
|
|
|
|
|
|
|
|
| Re: Huge Query [message #308563 is a reply to message #308528] |
Mon, 24 March 2008 21:53 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Then I don't think your over-zealous administrator is going to like this one much either.
There's no PL/SQL, but there are XML functions. I have used one of my own tables rather than the model you specified, but you should get the idea.
select x.*
from (
select t.column_value
from (
SELECT
CHR((ROWNUM + 64)) AS Character
FROM DUAL
CONNECT BY LEVEL <= 26
) alphabet
, table(xmlsequence(cursor(
select NAMSPC_ID, LGCL_OBJ_NAM, PHYS_OBJ_NAM
from tmt_obj
where phys_obj_nam like alphabet.character || '%'
and namspc_id = 3
and rownum <= 10
order by NAMSPC_ID, PHYS_OBJ_NAM
))) t
) t1
, xmltable(
'/ROW'
PASSING t1.column_value
COLUMNS
"LGCL_OBJ_NAM" VARCHAR2(80) PATH '/ROW/LGCL_OBJ_NAM'
) x
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 291 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.02 0 0 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.05 0.04 0 291 0 10
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 66
Rows Row Source Operation
------- ---------------------------------------------------
10 NESTED LOOPS (cr=75 pr=0 pw=0 time=16269 us)
10 VIEW (cr=75 pr=0 pw=0 time=17906 us)
10 NESTED LOOPS (cr=75 pr=0 pw=0 time=17884 us)
26 VIEW (cr=0 pr=0 pw=0 time=385 us)
26 COUNT (cr=0 pr=0 pw=0 time=278 us)
26 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=25 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=2 us)
10 VIEW (cr=75 pr=0 pw=0 time=22834 us)
10 COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMREFCURSOR2 (cr=75 pr=0 pw=0 time=22732 us)
10 VIEW (cr=0 pr=0 pw=0 time=12457 us)
10 VIEW (cr=0 pr=0 pw=0 time=5665 us)
10 COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMXMLTYPE (cr=0 pr=0 pw=0 time=5611 us)
********************************************************************************
SELECT "A7"."NAMSPC_ID" "NAMSPC_ID","A7"."LGCL_OBJ_NAM" "LGCL_OBJ_NAM",
"A7"."PHYS_OBJ_NAM" "PHYS_OBJ_NAM"
FROM
"TMT_OBJ" "A7" WHERE "A7"."PHYS_OBJ_NAM" LIKE :CV1$||'%' AND
"A7"."NAMSPC_ID"=3 AND ROWNUM<=10 ORDER BY "A7"."NAMSPC_ID",
"A7"."PHYS_OBJ_NAM"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 26 0.00 0.00 0 0 0 0
Execute 26 0.00 0.00 0 0 0 0
Fetch 36 0.00 0.00 0 45 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 88 0.00 0.01 0 45 0 10
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 66 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=1 pr=0 pw=0 time=31 us)
0 TABLE ACCESS BY INDEX ROWID TMT_OBJ (cr=1 pr=0 pw=0 time=26 us)
0 INDEX RANGE SCAN SYS_C0010253 (cr=1 pr=0 pw=0 time=22 us)(object id 69548)
Ross Leishman
|
|
|
|
| Re: Huge Query [message #309900 is a reply to message #308563] |
Sat, 29 March 2008 13:18 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
Ross,
Thanks, but I do not understand this query. I have no experience with XML functions. The acronym XML alone gives me the jitters as I associate it with web development and bloatware. Brrr....
I understand that Extensible Markup Language is good for exchanging data between incompatible systems with emphasis on compatibility not efficiency, but what does it have to do with query within one database and especially with performance?
I cannot even verify that this query works and how well it works because I have no idea what the tmt_obj table is, and how to create it. (Error: Table or View does not exist).
Thanks for trying to help, but XML !$%^*%&^ me.
Regards,
George
[Updated on: Sat, 29 March 2008 13:19] Report message to a moderator |
|
|
|
| Re: Huge Query [message #309926 is a reply to message #309900] |
Sat, 29 March 2008 19:02 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
OK, I'll have a go at translating it into your table, but of course I cannot test it:
select x.*
from (
select t.column_value
from (
SELECT
CHR((ROWNUM + 64)) AS Character
FROM DUAL
CONNECT BY LEVEL <= 26
) alphabet
, table(xmlsequence(cursor(
select fname, sName, Id
from Customer
where fname like alphabet.character || '%'
and rownum <= 10
order by fName, sName, Id
))) t
) t1
, xmltable(
'/ROW'
PASSING t1.column_value
COLUMNS
"FNAME" VARCHAR2(80) PATH '/ROW/FNAME'
, "SNAME" VARCHAR2(80) PATH '/ROW/SNAME'
, "ID" VARCHAR2(80) PATH '/ROW/ID'
) x
What you are trying to do with your UNION ALL query is to run 26 separate queries and concatenate the results.
As you have discovered, when you try to do it in a single pass, you lose the ability of ROWNUM <= x to discard the unwanted rows. Reading those unwanted rows is time you want to eliminate. So it's back to the 26 separate queries.
Problem is, how to make a single template query that you can ITERATE 26 times. Easy in PL/SQL, but you cant use PL/SQL.
Bog-standard SQL has very limited iteration capabilities. To my knowledge, they are limited to CONNECT BY, MODEL, and unnesting of collections. CONNECT BY may be possible, but is too clunky for such precision requirements (nosort and what-not). MODEL (to my knowledge - I'm no expert) only acts on the RESULTS of a query, it cannot drive table access itself.
So that leaves collection unnesting. Collection unnesting is clearly going to involve a collection, and that is typically going to involve PL/SQL, which you cannot use. We could maybe troll through Oracle's included collection types to see if we could borrow one, but that's a bit nasty. XML gives us a way to cast queries as collections without PL/SQL, which we can then unnest.
Stepping through the query above:
- Run the Alphabet inline view. It produces 26 rows.
- TABLE() functions do something regular SQL cannot. They iterate! For each row in the Alphabet query, obtain a collection of rows from the TABLE() function. Unlike regular SQL, the innards of the TABLE() function may reference other tables in the FROM clause without using a JOIN.
- The query inside the CURSOR() function should be obvious. It is being run for each letter of the alphabet returning a collection of rows.
- The CURSOR is passed to XMLSEQUENCE(), which EXECUTES and FETCHES the cursor, converting each row returned to XML.
- The TABLE() function casts the collection of XML rows (strings) back into regular rows so that the SQL engine can deal with them like normal rows.
- Now we have the results of the 26 executed cursors in a single result-set. Only problem is that they are in XML format, not regular SQL columns.
- Now we decompose that result-set using the XMLTABLE function. Like TABLE(), it is special in that it is permitted to reference columns in other tables in the FROM clause. For each XML row passed in, it returns what looks like a SQL row source containing the columns defined in the COLUMNS clause; the PATH clause tells it where those columns will be found in the XML.
It's hard to describe; I guess it's hard to understand too. I leave it with you. But if you want to achieve iteration without PL/SQL, your options are very limited.
Ross Leishman
|
|
|
|
| Re: Huge Query [message #309955 is a reply to message #309926] |
Sun, 30 March 2008 10:13 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
Ross,
Thanks for taking the effort of translating & explaining it to me.
The query you posted works now, and you can see its plan below, however it takes 10x longer than the bog-standard huge query.
Maybe I am doing something wrong.
I can see that XML is not all evil like I predjudged it.
I'll make an effort to brush up on XML functions, as I can see I have a lot to learn.
Thanks,
George
SQL> select x.*
2 from (
3 select t.column_value
4 from (
5 SELECT
6 CHR((ROWNUM + 64)) AS Character
7 FROM DUAL
8 CONNECT BY LEVEL <= 26
9 ) alphabet
10 , table(xmlsequence(cursor(
11 select fname, sName, Id
12 from Customer
13 where fname like alphabet.character || '%'
14 and rownum <= 10
15 order by fName, sName, Id
16 ))) t
17 ) t1
18 , xmltable(
19 '/ROW'
20 PASSING t1.column_value
21 COLUMNS
22 "FNAME" VARCHAR2(80) PATH '/ROW/FNAME'
23 , "SNAME" VARCHAR2(80) PATH '/ROW/SNAME'
24 , "ID" VARCHAR2(80) PATH '/ROW/ID'
25 ) x
26 /
FNAME SNAME ID
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------
A2-07-18:08:04: SI_POSITIONALCOLOR 41185
A2-10-23:15:20: AQ$_JMS_STREAM_MESSAGES 4655
A6-11-19:10:27: AW_IND$ 521
<SNIP>
250 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3729520284
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 66M| 9416M| 198K (2)| 00:39:46 |
| 1 | NESTED LOOPS | | 66M| 9416M| 198K (2)| 00:39:46 |
| 2 | VIEW | | 8168 | 175K| 26 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 8168 | 191K| 26 (0)| 00:00:01 |
| 4 | VIEW | | 1 | 2 | 2 (0)| 00:00:01 |
| 5 | COUNT | | | | | |
|* 6 | CONNECT BY WITHOUT FILTERING | | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | VIEW | | 8168 | 175K| 24 (0)| 00:00:01 |
| 9 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMREFCURSOR2 | | | | |
| 10 | VIEW | | 8168 | 1005K| 24 (0)| 00:00:01 |
| 11 | VIEW | | 8168 | 175K| 24 (0)| 00:00:01 |
| 12 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(LEVEL<=26)
Statistics
----------------------------------------------------------
453 recursive calls
0 db block gets
1036 consistent gets
0 physical reads
0 redo size
9797 bytes sent via SQL*Net to client
572 bytes received via SQL*Net from client
18 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
250 rows processed
SQL> |
|
|
|
|
|
| Re: Huge Query [message #309980 is a reply to message #309977] |
Sun, 30 March 2008 18:38 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Trying to look at the whole picture and understand the business requirement, it looks like what you really need is a table that is partitioned by the first letter of fname. That would allow you to search quickly by any letter, as demonstrated below, searching on the C partition.
SCOTT@orcl_11g> DROP TABLE CUSTOMER
2 /
Table dropped.
SCOTT@orcl_11g> CREATE TABLE CUSTOMER
2 (
3 ID NUMBER(10),
4 SNAME VARCHAR2(80 BYTE) NOT NULL,
5 FNAME VARCHAR2(50 BYTE),
6 DOB DATE
7 )
8 PARTITION BY RANGE (FNAME)
9 (PARTITION a VALUES LESS THAN ('B'),
10 PARTITION b VALUES LESS THAN ('C'),
11 PARTITION c VALUES LESS THAN ('D'),
12 PARTITION d VALUES LESS THAN ('E'),
13 PARTITION e VALUES LESS THAN ('F'),
14 PARTITION f VALUES LESS THAN ('G'),
15 PARTITION g VALUES LESS THAN ('H'),
16 PARTITION h VALUES LESS THAN ('I'),
17 PARTITION i VALUES LESS THAN ('J'),
18 PARTITION j VALUES LESS THAN ('K'),
19 PARTITION k VALUES LESS THAN ('L'),
20 PARTITION l VALUES LESS THAN ('M'),
21 PARTITION m VALUES LESS THAN ('N'),
22 PARTITION n VALUES LESS THAN ('O'),
23 PARTITION o VALUES LESS THAN ('P'),
24 PARTITION p VALUES LESS THAN ('Q'),
25 PARTITION q VALUES LESS THAN ('R'),
26 PARTITION r VALUES LESS THAN ('S'),
27 PARTITION s VALUES LESS THAN ('T'),
28 PARTITION t VALUES LESS THAN ('U'),
29 PARTITION u VALUES LESS THAN ('V'),
30 PARTITION v VALUES LESS THAN ('W'),
31 PARTITION w VALUES LESS THAN ('X'),
32 PARTITION x VALUES LESS THAN ('Y'),
33 PARTITION y VALUES LESS THAN ('Z'),
34 PARTITION z VALUES LESS THAN (MAXVALUE))
35 /
Table created.
SCOTT@orcl_11g> INSERT INTO CUSTOMER (ID, FNAME, SNAME, DOB)
2 SELECT rownum, CHR(mod(rownum,26)+65) || substr(to_char(TIMESTAMP),4,14), to_char(OBJECT_NAME), LAST_DDL_TIME
3 FROM all_OBJECTS
4 /
68600 rows created.
SCOTT@orcl_11g> CREATE UNIQUE INDEX IDX_USE_ME ON CUSTOMER (FNAME, SNAME, ID)
2 /
Index created.
SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'CUSTOMER')
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> SET TIMING ON
SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> COLUMN fname FORMAT A15 WORD_WRAPPED
SCOTT@orcl_11g> COLUMN sname FORMAT A30 WORD_WRAPPED
SCOTT@orcl_11g> SELECT i, fname, sname, id
2 FROM (SELECT /*+ INDEX (CUSTOMER IDX_USE_ME) FIRST_ROWS */
3 ROW_NUMBER () OVER (PARTITION BY fname, sname ORDER BY id) i,
4 ROW_NUMBER () OVER (ORDER BY fname, sname, id) rnum,
5 fname, sname, id
6 FROM Customer PARTITION (A)
7 ORDER BY fname, sname, id)
8 WHERE rnum <= 10
9 /
I FNAME SNAME ID
---------- --------------- ------------------------------ ----------
1 A2-08-01:09:48: SDO_LIST_TYPE 58318
1 A7-04-09:14:17: ST_ANNOTATION_TEXT 58890
1 A7-10-15:10:09: ALL_IND_COLUMNS 2860
1 A7-10-15:10:09: ALL_TAB_COLS 2938
1 A7-10-15:10:09: ALL_TAB_PRIVS_MADE 2964
1 A7-10-15:10:09: APPLY$_DEST_OBJ 650
1 A7-10-15:10:09: CACHE_STATS_SEQ_1 390
1 A7-10-15:10:09: CAT 2808
1 A7-10-15:10:09: COMPARISON_ROW_DIF$ 728
1 A7-10-15:10:09: DBA_COL_PRIVS 2834
10 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3261231218
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2638 | 278K| 571 (1)| 00:00:07 |
|* 1 | VIEW | | 2638 | 278K| 571 (1)| 00:00:07 |
|* 2 | WINDOW NOSORT STOPKEY| | 2638 | 118K| 571 (1)| 00:00:07 |
|* 3 | INDEX FULL SCAN | IDX_USE_ME | 2638 | 118K| 571 (1)| 00:00:07 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "FNAME","SNAME","ID")<=10)
3 - filter(TBL$OR$IDX$PART$NUM("CUSTOMER",0,1,0,ROWID)=1)
SCOTT@orcl_11g> SELECT i, fname, sname, id
2 FROM (SELECT /*+ INDEX (CUSTOMER IDX_USE_ME) FIRST_ROWS */
3 ROW_NUMBER () OVER (PARTITION BY fname, sname ORDER BY id) i,
4 fname, sname, id
5 FROM Customer PARTITION (c)
6 ORDER BY fname, sname, id)
7 WHERE ROWNUM <= 10
8 /
I FNAME SNAME ID
---------- --------------- ------------------------------ ----------
1 C7-10-15:10:09: ALL_CATALOG 2810
1 C7-10-15:10:09: CMP_ROW_DIF_UNIQ_IDX_2 730
1 C7-10-15:10:09: DATABASE_COMPATIBLE_LEVEL 2576
1 C7-10-15:10:09: DBA_IND_COLUMNS 2862
1 C7-10-15:10:09: DBA_TAB_COLS 2940
1 C7-10-15:10:09: DIM$ 834
1 C7-10-15:10:09: DIR$ 444
1 C7-10-15:10:09: DIR$SERVICE_OPERATIONS 288
1 C7-10-15:10:09: EXPDEPACT$ 860
1 C7-10-15:10:09: GV$LOGMNR_PARAMETERS 2212
10 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3940838639
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 950 | 571 (1)| 00:00:07 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 2639 | 244K| 571 (1)| 00:00:07 |
| 3 | WINDOW NOSORT | | 2639 | 118K| 571 (1)| 00:00:07 |
|* 4 | INDEX FULL SCAN| IDX_USE_ME | 2639 | 118K| 571 (1)| 00:00:07 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
4 - filter(TBL$OR$IDX$PART$NUM("CUSTOMER",0,1,0,ROWID)=3)
SCOTT@orcl_11g> spool off
|
|
|
|
| Re: Huge Query [message #311552 is a reply to message #309980] |
Fri, 04 April 2008 14:47 |
verpies
Messages: 28
Registered: March 2008
Location: Seattle
|
Junior Member |
|
|
| "Ross" | Can you TKPROF it and show the CURSOR() query performance as well
|
I can't get the tkprof to work. Do you know of a good FAQ about it somewhere?
Don't waste time helping me with a basic issue such as tkprof, a reference to a good FAQ will be plenty.
Regards,
George
[Updated on: Fri, 04 April 2008 14:48] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Huge Query [message #311572 is a reply to message #311556] |
Fri, 04 April 2008 16:31 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- TKPROF:
********************************************************************************
select x.*
from (
select t.column_value
from (
SELECT
CHR((ROWNUM + 64)) AS Character
FROM DUAL
CONNECT BY LEVEL <= 26
) alphabet
, table(xmlsequence(cursor(
select fname, sName, Id
from Customer
where fname like alphabet.character || '%'
and rownum <= 10
order by fName, sName, Id
))) t
) t1
, xmltable(
'/ROW'
PASSING t1.column_value
COLUMNS
"FNAME" VARCHAR2(80) PATH '/ROW/FNAME'
, "SNAME" VARCHAR2(80) PATH '/ROW/SNAME'
, "ID" VARCHAR2(80) PATH '/ROW/ID'
) x
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.04 0 723 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 18 1.21 1.32 0 6 0 250
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 1.24 1.37 0 729 0 250
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81
Rows Row Source Operation
------- ---------------------------------------------------
250 NESTED LOOPS (cr=309 pr=1 pw=1 time=5060 us cost=237617 size=9874001152 card=66716224)
250 VIEW (cr=306 pr=1 pw=1 time=1332 us cost=31 size=179696 card=8168)
250 NESTED LOOPS (cr=306 pr=1 pw=1 time=1323 us cost=31 size=196032 card=8168)
26 VIEW (cr=0 pr=0 pw=0 time=20 us cost=2 size=2 card=1)
26 COUNT (cr=0 pr=0 pw=0 time=13 us)
26 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
250 VIEW (cr=306 pr=1 pw=1 time=12073 us cost=29 size=179696 card=8168)
250 COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMREFCURSOR2 (cr=306 pr=1 pw=1 time=11937 us)
250 VIEW (cr=3 pr=0 pw=0 time=0 us cost=29 size=1029168 card=8168)
250 VIEW (cr=3 pr=0 pw=0 time=0 us cost=29 size=179696 card=8168)
250 COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMXMLTYPE (cr=3 pr=0 pw=0 time=0 us)
********************************************************************************
SQL ID : 376148c67un91
SELECT "A7"."FNAME" "FNAME","A7"."SNAME" "SNAME","A7"."ID" "ID"
FROM
"CUSTOMER" "A7" WHERE "A7"."FNAME" LIKE :CV1$||'%' AND ROWNUM<=10 ORDER BY
"A7"."FNAME","A7"."SNAME","A7"."ID"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 26 0.00 0.00 0 0 0 0
Execute 26 0.00 0.00 0 0 0 0
Fetch 276 0.01 0.01 1 303 0 250
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 328 0.01 0.02 1 303 0 250
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
10 COUNT STOPKEY (cr=12 pr=0 pw=0 time=0 us)
10 INDEX RANGE SCAN IDX_USE_ME (cr=12 pr=0 pw=0 time=0 us cost=3 size=506 card=11)(object id 110118)
********************************************************************************
|
|
|
|
| Re: Huge Query [message #311587 is a reply to message #307966] |
Fri, 04 April 2008 21:09 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Yep, that's the plan I was after. It took only 1.37 seconds including the recursive SQL.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Sat Dec 07 00:12:44 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
in the client, because the users like to jump around the letters A LOT, and I try to minimize the network traffic, so I never ask for the same letter sample bucket twice. The table is pretty static (In the future, I might think about invalidating the cache when the table changes via some trigger notification mechanism)
