Calculating TYhe Median Via SQL*Plus Revisited

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Fri, 29 Jun 2001 18:16:41 -0700
Message-ID: <F001.0033E4A4.20010629181022@fatcity.com>

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
ian_at_slac.stanford.edu

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

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: ListGuru_at_fatcity.com (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). Received on Fri Jun 29 2001 - 20:16:41 CDT