Home » SQL & PL/SQL » SQL & PL/SQL » Huge Query
Huge Query [message #307966] Thu, 20 March 2008 11:00 Go to next message
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 #307972 is a reply to message #307966] Thu, 20 March 2008 11:21 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
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.

Regards

Raj

[Updated on: Thu, 20 March 2008 11:43]

Report message to a moderator

Re: Huge Query [message #307995 is a reply to message #307966] Thu, 20 March 2008 13:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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 #308026 is a reply to message #308012] Thu, 20 March 2008 16:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
verpies wrote on Thu, 20 March 2008 13:20

The query should always return 260 rows if (SELECT COUNT(*) FROM CUSTOMER WHERE FNAME>='A') >= 260[/color]



My tests are returning 250, instead of 260, because there are none beginning with 'Z', so the criteria >= 'Z' for the last 10 is not met.
Re: Huge Query [message #308029 is a reply to message #308012] Thu, 20 March 2008 16:25 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Just out of curiosity, are you ever displaying all letters simultaneously or are you just displaying 10 of any given letter?
Re: Huge Query [message #308032 is a reply to message #308029] Thu, 20 March 2008 16:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Scott,

That's an excellent question. If just displaying for one character at a time, he could just query for one character passing the value using a bind variable and that would be a lot faster.
Re: Huge Query [message #308036 is a reply to message #308026] Thu, 20 March 2008 17:03 Go to previous messageGo to next message
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 #308037 is a reply to message #308029] Thu, 20 March 2008 17:11 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Actually, I cache the 260 rows (almost always 260 Wink 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)

So I guess the answer to your question is that I need the 26 letter sample buckets all at once (when the client starts).

scottwmackey wrote:
Just out of curiosity, are you ever displaying all letters simultaneously or are you just displaying 10 of any given letter?


Regards,
George Robinson
Re: Huge Query [message #308042 is a reply to message #308037] Thu, 20 March 2008 17:44 Go to previous messageGo to next message
scottwmackey
Messages: 505
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
scottwmackey
Messages: 505
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #308497 is a reply to message #308380] Mon, 24 March 2008 09:51 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
Unfortunately it is an illogical person who enforces it with overzealous database permissions.

So I guess, I am stuck with my huge but quick query Sad



Re: Huge Query [message #308528 is a reply to message #307966] Mon, 24 March 2008 13:08 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
just convert it to a bog standard view to reduce the query size.
Re: Huge Query [message #308563 is a reply to message #308528] Mon, 24 March 2008 21:53 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #309977 is a reply to message #309955] Sun, 30 March 2008 18:14 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Can you TKPROF it and show the CURSOR() query performance as well?

Ross Leishman
Re: Huge Query [message #309980 is a reply to message #309977] Sun, 30 March 2008 18:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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 Go to previous messageGo to next message
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 #311554 is a reply to message #307966] Fri, 04 April 2008 14:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It appears you require remedial training of the use of SEARCH on this forum & how to use GOOGLE.

http://www.orafaq.com/wiki/TKProf
Re: Huge Query [message #311556 is a reply to message #311554] Fri, 04 April 2008 15:12 Go to previous messageGo to next message
verpies
Messages: 28
Registered: March 2008
Location: Seattle
Junior Member
I was asking for your recomendation of a FAQ, not just any one of the hundreds that Goofle finds Wink
Re: Huge Query [message #311558 is a reply to message #307966] Fri, 04 April 2008 15:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
as opposed to
 tkprof 
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor

Re: Huge Query [message #311559 is a reply to message #311556] Fri, 04 April 2008 15:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The following is my favorite. If you follow Tom Kyte's code to set up the "tklast" you get "tkprof for the masses". Then any developer can easily get the tkprof the easy way.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7115831027951

To find the user_dump_dest directory on your system:

SCOTT@orcl_11g> select value from v$parameter where name = 'user_dump_dest';

VALUE
--------------------------------------------------------------------------------
c:\app\barbara\diag\rdbms\orcl\orcl\trace

SCOTT@orcl_11g>

[Updated on: Fri, 04 April 2008 16:45]

Report message to a moderator

Re: Huge Query [message #311572 is a reply to message #311556] Fri, 04 April 2008 16:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: replace the column value
Next Topic: sql script error output
Goto Forum:
  


Current Time: Sat Dec 10 20:17:50 CST 2016

Total time taken to generate the page: 0.18522 seconds