I thought I'd try using some of the features introduced in 8i to calculate the median
value. I'm not sure
if it performs better or not. I hope others will be able to improve the code.
Unfortunately I misplaced Jared's posting
and cannot compare the two. The query took 8 seconds to compute the median on a
70,000 record table which I think is quite
slow. The field had an index.
select
case
when mod(number_salaried,2) = 0 then
(select sum(sal)/2 from(select sal, row_number()
over ( order by sal) as salrank
from scott.emp)
where salrank = number_salaried/2
or salrank = number_salaried/2 +1)
else
(select sal from(select sal, row_number()
over ( order by sal) as salrank
from scott.emp)
where salrank = ceil(number_salaried/2))
end median
from (select sal,rank() over (order by sal) as rk from scott.emp),
(select count(sal) number_salaried from scott.emp)
where rk = 1
/
The results
MEDIAN
---------
1550
real: 2109
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=14 Bytes=364)
1 0 MERGE JOIN (CARTESIAN) (Cost=4 Card=14 Bytes=364)
2 1 VIEW (Cost=1 Card=1 Bytes=13)
3 2 SORT (AGGREGATE)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=4
2)
5 1 VIEW
6 5 WINDOW (SORT PUSHED RANK)
7 6 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=4
2)
Statistics
----------------------------------------------------------
9 recursive calls
12 db block gets
6 consistent gets
1 physical reads
0 redo size
401 bytes sent via SQL*Net to client
1031 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
1 rows processed
----------------------------------------------------------------------------------------------------------------
If sal were indexed the first full table scan would be an index full scan.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
