LOOPS=1 (DBA response)

[Jason Penney <jasonpenney@YAHOO.COM>]

For a definitive answer on the effiency question when using LOOPS=n, I
asked our local DBA.  His response follows. (For questions, see his
contact info at bottom.)

Jason Penney
jasonpenney@yahoo.com
-----
The second statement has a lower cost for the optimizer (4), which
usually indicates a better performing statement.  In this case, it is
the better statement.  Generally, the lower the cost, the better.
However, if you look at the execution plan, you'll notice two SORT
AGGREGATE's.  This turns the question into a data volume question.

If you run these two statements, exactly as defined below, I
determined the following :

Query 1:
        TIME : 1.83 seconds
        BLOCK GETS : 7
        NETWORK DATA TRANSFERRED :  3656 bytes

Query 2:
        TIME : 1.74 seconds
        BLOCK GETS : 19
        NETWORK DATA TRANSFERRED :  2560 bytes

The second query accessed more data blocks (19), to process, but took
less time.  Sounds strange huh !,  BUT ....
Look at the data transferred across the network.  It is much lower
(about 30% less).  This is where you are making up the time.

However, if the statement is changed, and the number of GETS,
increases, then this statement could become slower than the first query.

---------------------------------------------------------------------
Query 1:

select *
from ps_job
where emplid = '123456789'

1       SELECT STATEMENT         Cost = 8
2         TABLE ACCESS   PS_JOB   BY ROWID   Cost = 8
3           INDEX   PS#JOB   RANGE SCAN

---------------------------------------------------------------------
Query 2:

select *
from ps_job job
where job.emplid = '123456789'
  and job.effdt  = (select max(job2.effdt)
                    from ps_job job2
                    where job2.emplid    = job.emplid
                      and job2.empl_rcd# = job.empl_rcd#
                      and job2.effdt    <= sysdate)
  and job.effseq = (select max(job3.effseq)
                    from ps_job job3
                    where job3.emplid    = job.emplid
                      and job3.empl_rcd# = job.empl_rcd#
                      and job3.effdt     = job.effdt)

1       SELECT STATEMENT         Cost = 4
2         TABLE ACCESS   PS_JOB   BY ROWID   Cost = 4
3           INDEX   PSAJOB   RANGE SCAN
4             SORT      AGGREGATE
5               INDEX   PSAJOB   RANGE SCAN   Cost = 3
4             SORT      AGGREGATE
5               INDEX   PSAJOB   RANGE SCAN   Cost = 3

Roger Neal
rneal@granitesys.com
Oracle DBA / Peoplesoft DBA
Granite Systems Inc.




_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com