| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: how to tune up this query? (Long post with example)
This is as good as I got with normal trad methods
replacing with my own table 1E6 records
put a count on top to make it actualize the result without having to read
it.
I'll do the same using the analytic func to get equivalent (hopefully
answers)
Note it has not used the (bitmap) index on Gender. Wrote file afiedt.buf
1 select count (*) from
2 (SELECT customer_urn, surname,income FROM
3 (SELECT customer_urn, surname, income FROM indiv
4 WHERE title = 'Mr' and Gender= 'M' ORDER BY income ASC)
5* WHERE rownum <= 1000)
SQL> /
COUNT(*)
1000
Elapsed: 00:00:03.85
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3677 Card=1) 1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=3677 Card=1000)
3 2 COUNT (STOPKEY)
4 3 VIEW (Cost=3677 Card=47619)
5 4 SORT (ORDER BY STOPKEY) (Cost=3677 Card=47619 Byte
s=904761)
6 5 TABLE ACCESS (FULL) OF 'INDIV' (Cost=3388 Card=4
7619 Bytes=904761)
Statistics
0 recursive calls
0 db block gets
12179 consistent gets
4040 physical reads
0 redo size
371 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed.
Using the analytics ....
1* select count(*) from (select
customer_urn,substr(surname,0,10),income,rank
() over(order by income asc) rank_1 from indiv where gender = 'M' and
title='Mr'
) where rank_1 < 1000
SQL> /
COUNT(*)
1000
Elapsed: 00:00:04.46
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3577 Card=1 Bytes=13
)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=3577 Card=47619 Bytes=619047)
3 2 WINDOW (SORT PUSHED RANK) (Cost=3577 Card=47619 Bytes=
428571)
4 3 TABLE ACCESS (FULL) OF 'INDIV' (Cost=3388 Card=47619
Bytes=428571)
Statistics
7 recursive calls
0 db block gets
12181 consistent gets
4041 physical reads
0 redo size
371 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> / COUNT(*)
1000
Elapsed: 00:00:04.67
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3577 Card=1 Bytes=13
)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=3577 Card=47619 Bytes=619047)
3 2 WINDOW (SORT PUSHED RANK) (Cost=3577 Card=47619 Bytes=
428571)
4 3 TABLE ACCESS (FULL) OF 'INDIV' (Cost=3388 Card=47619
Bytes=428571)
Statistics
0 recursive calls
0 db block gets
12179 consistent gets
4041 physical reads
0 redo size
371 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> / Trad method better in this case presumably since analytics is a big hammer for this walnut. YMMV.
HTH Telemachus.
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
news:3e396173$0$247$ed9e5944_at_reading.news.pipex.net...
> "Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message
> news:X7d_9.6451$V6.8484_at_news.indigo.ie...
> > Which was my point previous on the thread,,, that Susan had to use the
> outer
> > select for the rownum pull ... this was covered in depth on one of the
> > boards last year....
> >
> > She was right. Improvement I can possibly see is analytic but as I said
a
> > FTS is on the cards . I'll have a look.
>
> Certainly analytics if available are worth a try (if you can remember
where
> to put all the brackets :(). Sorry if I was repeating you.
> >
 |
 |