Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Expense of 'over ... partition by'

Expense of 'over ... partition by'

From: <Jared.Still_at_radisys.com>
Date: Thu, 04 Dec 2003 16:24:29 -0800
Message-ID: <F001.005D8D0F.20031204162429@fatcity.com> 





Content-Type: text/plain; charset="us-ascii"



While working on some scripts to monitor PGA usage on 9i, I came across 
something interesting while experimenting with different forms of SQL.



I have recently been forcing myself to make use of 'OVER..PARTITION BY' in
SQL so as to be more comfortable in using it.  Can't add new tools to the 
box until I


know how to use them.  :)  Yes, I know I should have been using them long 
ago.



Anyway, I thought it might be interesting to compare the forms of SQL with 
and


without the use of OVER...PARTITION BY.



This SQL can be run on any instance that has PGA_AGGREGATE_TARGET set.



Here is the SQL using OVER: 



select

        low_optimal_size_kb
        , high_optimal_size_kb
        , optimal_executions
        , onepass_executions
        , multipasses_executions
        , total_executions
        , optimal_executions / sum_optimal_executions * 100 


pct_optimal_executions


from (

        select
                low_optimal_size/1024 low_optimal_size_kb


                , (high_optimal_size+1)/1024 high_optimal_size_kb

                , optimal_executions

                , onepass_executions

                , multipasses_executions

                , total_executions

                , sum(optimal_executions) over ( partition by 1 ) 

sum_optimal_executions
        from v$sql_workarea_histogram
        where total_executions != 0




) a


order by low_optimal_size_kb


/



and here is the SQL using good old GROUP BY



select

        low_optimal_size_kb
        , high_optimal_size_kb
        , optimal_executions
        , onepass_executions
        , multipasses_executions
        , total_executions
        , optimal_executions / sum_optimal_executions * 100 


pct_optimal_executions


from (

        select
                h.low_optimal_size/1024 low_optimal_size_kb


                , (h.high_optimal_size+1)/1024 high_optimal_size_kb

                , h.optimal_executions

                , h.onepass_executions

                , h.multipasses_executions

                , h.total_executions

                , hs.sum_optimal_executions

        from v$sql_workarea_histogram h,
                (
                        select sum(optimal_executions) 
sum_optimal_executions
                        from v$sql_workarea_histogram
                ) hs
        where h.total_executions != 0
        group by h.low_optimal_size/1024
                ,(h.high_optimal_size+1)/1024


                , h.optimal_executions

                , h.onepass_executions

                , h.multipasses_executions

                , h.total_executions

                , hs.sum_optimal_executions



) a


order by low_optimal_size_kb


/




The new version is significantly simpler.



It then seemed that it might be interesting to compare the performance and 
scalability of the two methods.



This is where it gets interesting.




16:10:47 rsysdevdb.radisys.com - jkstill_at_dv03 SQL> @run_stats


NAME                                           RUN1       RUN2       DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.lgwr LWN SCN                                1          0         -1
LATCH.mostly latch-free SCN                       1          0         -1
LATCH.undo global data                            1          0         -1
STAT...active txn count during cleanout           1          0         -1
STAT...consistent gets                            5          4         -1
STAT...db block gets                             28         29          1
STAT...enqueue requests                           1          0         -1
STAT...redo entries                              17         18          1
STAT...deferred (CURRENT) block cleanout          4          3         -1


 applications


STAT...consistent gets - examination              1          0         -1
STAT...cleanout - number of ktugct calls          1          0         -1
STAT...calls to kcmgcs                            7          6         -1
STAT...calls to get snapshot scn: kcmgss       1006       1005         -1
LATCH.Consistent RBA                              2          0         -2
STAT...recursive cpu usage                       29         31          2
LATCH.redo allocation                            20         18         -2
LATCH.cache buffers chains                      102        105          3
LATCH.redo writing                                4          0         -4
LATCH.library cache                            2014       2008         -6
LATCH.library cache pin                        2012       2006         -6
LATCH.messages                                    8          0         -8
STAT...redo size                              27096      27508        412
STAT...sorts (memory)                          1004       2004       1000
LATCH.SQL memory manager workarea list l          0       2000       2000


atch


STAT...workarea executions - optimal           2008       4008       2000
STAT...sorts (rows)                            6112      10112       4000





26 rows selected.



RUN1 is the the GROUP BY SQL


RUN2 is the OVER...PARTITION BY SQL



The OVER version of the SQL is significantly more expensive in terms of 
sorting and latching.



Has anyone else noticed this?



Or perhaps my use of OVER..PARTITION BY needs some optimization, which is 
clearly


in the realm of possibility.  :)



Jared





--=_alternative 0002067388256DF3_=


Content-Type: text/html; charset="us-ascii"



<br><font size=2 face="sans-serif">While working on some scripts to monitor PGA usage on 9i, I came across </font>
<br><font size=2 face="sans-serif">something interesting while experimenting with different forms of SQL.</font>
<br>
<br><font size=2 face="sans-serif">I have recently been forcing myself to make use of 'OVER..PARTITION BY' in</font>
<br><font size=2 face="sans-serif">SQL so as to be more comfortable in using it. &nbsp;Can't add new tools to the box until I</font>
<br><font size=2 face="sans-serif">know how to use them. &nbsp;:) &nbsp;Yes, I know I should have been using them long ago.</font>
<br>
<br><font size=2 face="sans-serif">Anyway, I thought it might be interesting to compare the forms of SQL with and</font>
<br><font size=2 face="sans-serif">without the use of OVER...PARTITION BY.</font>
<br>
<br><font size=2 face="sans-serif">This SQL can be run on any instance that has PGA_AGGREGATE_TARGET set.</font>
<br>
<br><font size=2 face="sans-serif">Here is the SQL using OVER: </font>
<br>
<br><font size=2 face="sans-serif">select</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; low_optimal_size_kb</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; , high_optimal_size_kb</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; , optimal_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; , onepass_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; , multipasses_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; , total_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; , optimal_executions / sum_optimal_executions * 100 pct_optimal_executions</font>
<br><font size=2 face="sans-serif">from (</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; select</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; low_optimal_size/1024 low_optimal_size_kb</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , (high_optimal_size+1)/1024 high_optimal_size_kb</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , optimal_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , onepass_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , multipasses_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , total_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , sum(optimal_executions) over ( partition by 1 ) sum_optimal_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; from v$sql_workarea_histogram</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; where total_executions != 0</font>
<br><font size=2 face="sans-serif">) a</font>
<br><font size=2 face="sans-serif">order by low_optimal_size_kb</font>
<br><font size=2 face="sans-serif">/</font>
<br>
<br><font size=2 face="sans-serif">and here is the SQL using good old GROUP BY</font>
<br>
<br><font size=2 face="sans-serif">select</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; low_optimal_size_kb</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; , high_optimal_size_kb</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; , optimal_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; , onepass_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; , multipasses_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; , total_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; , optimal_executions / sum_optimal_executions * 100 pct_optimal_executions</font>
<br><font size=2 face="sans-serif">from (</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; select</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; h.low_optimal_size/1024 low_optimal_size_kb</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , (h.high_optimal_size+1)/1024 high_optimal_size_kb</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.optimal_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.onepass_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.multipasses_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.total_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , hs.sum_optimal_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; from v$sql_workarea_histogram h,</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; select sum(optimal_executions) sum_optimal_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from v$sql_workarea_histogram</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) hs</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; where h.total_executions != 0</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; group by h.low_optimal_size/1024</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,(h.high_optimal_size+1)/1024</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.optimal_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.onepass_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.multipasses_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , h.total_executions</font>
<br><font size=2 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , hs.sum_optimal_executions</font>
<br><font size=2 face="sans-serif">) a</font>
<br><font size=2 face="sans-serif">order by low_optimal_size_kb</font>
<br><font size=2 face="sans-serif">/</font>
<br>
<br>
<br><font size=2 face="sans-serif">The new version is significantly simpler.</font>
<br>
<br><font size=2 face="sans-serif">It then seemed that it might be interesting to compare the performance and scalability of the two methods.</font>
<br>
<br><font size=2 face="sans-serif">This is where it gets interesting.</font>
<br>
<br>
<br><font size=2 face="Courier">16:10:47 rsysdevdb.radisys.com - jkstill_at_dv03 SQL&gt; @run_stats</font>
<br>
<br><font size=2 face="Courier">NAME &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RUN1 &nbsp; &nbsp; &nbsp; RUN2 &nbsp; &nbsp; &nbsp; DIFF</font>
<br><font size=2 face="Courier">---------------------------------------- ---------- ---------- ----------</font>
<br><font size=2 face="Courier">LATCH.lgwr LWN SCN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">LATCH.mostly latch-free SCN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">LATCH.undo global data &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">STAT...active txn count during cleanout &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">STAT...consistent gets &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">STAT...db block gets &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 28 &nbsp; &nbsp; &nbsp; &nbsp; 29 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1</font>
<br><font size=2 face="Courier">STAT...enqueue requests &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">STAT...redo entries &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;17 &nbsp; &nbsp; &nbsp; &nbsp; 18 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1</font>
<br><font size=2 face="Courier">STAT...deferred (CURRENT) block cleanout &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">&nbsp;applications</font>
<br>
<br><font size=2 face="Courier">STAT...consistent gets - examination &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">STAT...cleanout - number of ktugct calls &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">STAT...calls to kcmgcs &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">STAT...calls to get snapshot scn: kcmgss &nbsp; &nbsp; &nbsp; 1006 &nbsp; &nbsp; &nbsp; 1005 &nbsp; &nbsp; &nbsp; &nbsp; -1</font>
<br><font size=2 face="Courier">LATCH.Consistent RBA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -2</font>
<br><font size=2 face="Courier">STAT...recursive cpu usage &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 29 &nbsp; &nbsp; &nbsp; &nbsp; 31 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2</font>
<br><font size=2 face="Courier">LATCH.redo allocation &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; 18 &nbsp; &nbsp; &nbsp; &nbsp; -2</font>
<br><font size=2 face="Courier">LATCH.cache buffers chains &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;102 &nbsp; &nbsp; &nbsp; &nbsp;105 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3</font>
<br><font size=2 face="Courier">LATCH.redo writing &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -4</font>
<br><font size=2 face="Courier">LATCH.library cache &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2014 &nbsp; &nbsp; &nbsp; 2008 &nbsp; &nbsp; &nbsp; &nbsp; -6</font>
<br><font size=2 face="Courier">LATCH.library cache pin &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2012 &nbsp; &nbsp; &nbsp; 2006 &nbsp; &nbsp; &nbsp; &nbsp; -6</font>
<br><font size=2 face="Courier">LATCH.messages &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; -8</font>
<br><font size=2 face="Courier">STAT...redo size &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;27096 &nbsp; &nbsp; &nbsp;27508 &nbsp; &nbsp; &nbsp; &nbsp;412</font>
<br><font size=2 face="Courier">STAT...sorts (memory) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1004 &nbsp; &nbsp; &nbsp; 2004 &nbsp; &nbsp; &nbsp; 1000</font>
<br><font size=2 face="Courier">LATCH.SQL memory manager workarea list l &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; 2000 &nbsp; &nbsp; &nbsp; 2000</font>
<br><font size=2 face="Courier">atch</font>
<br>
<br><font size=2 face="Courier">STAT...workarea executions - optimal &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2008 &nbsp; &nbsp; &nbsp; 4008 &nbsp; &nbsp; &nbsp; 2000</font>
<br><font size=2 face="Courier">STAT...sorts (rows) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6112 &nbsp; &nbsp; &nbsp;10112 &nbsp; &nbsp; &nbsp; 4000</font>
<br>
<br><font size=2 face="Courier">26 rows selected.</font>
<br>
<br><font size=2 face="sans-serif">RUN1 is the the GROUP BY SQL</font>
<br><font size=2 face="sans-serif">RUN2 is the OVER...PARTITION BY SQL</font>
<br>
<br><font size=2 face="sans-serif">The OVER version of the SQL is significantly more expensive in terms of sorting and latching.</font>
<br>
<br><font size=2 face="sans-serif">Has anyone else noticed this?</font>
<br>
<br><font size=2 face="sans-serif">Or perhaps my use of OVER..PARTITION BY needs some optimization, which is clearly</font>
<br><font size=2 face="sans-serif">in the realm of possibility. &nbsp;:)</font>
<br>
<br><font size=2 face="sans-serif">Jared</font>
<br>
<br>
<br>




--=_alternative 0002067388256DF3_=--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Thu Dec 04 2003 - 18:24:29 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US