RE: Inline View Query Versus Ordinary Query - Comparison

Stephane, List

Qs If you mean in a LOOP (as a Cursor maybe) the Real Performance would be Visible ? Qs What are the General Advantages of Using Inline View Queries Over Ordinary Ones ? Qs Any Limitations ?

Any Links / Docs ?

Thanks

-----Original Message-----
Sent: Wednesday, May 22, 2002 4:37 AM
To: Multiple recipients of list ORACLE-L

VIVEK_SHARMA wrote:
>
> Query using Inline View Query Versus an Ordinary one ?
> Which is Better w.r.t. Performance & Why ?
>
> EXAMPLE - Following 2 SAMPLE Queries give the SAME Output .
> But which would be better ?
>

Second is better because it accesses half the number of blocks. In this order of magnitude it matters if you run the query every 10s, not if it's part of this quarterly report.

-- 
Regards,

Stephane Faroult
Oriole Software


Query using Inline View Query an Ordinary one ?
Which is Better w.r.t. Performance & Why ?

EXAMPLE - Following 2 SAMPLE Queries give the SAME Output . 
But which would be better ?

SQL> SELECT INT_TBL_CODE, TO_CHAR(LCHG_TIME, 'DD-MM-YYYY HH24:MI:SS')
  2  FROM ITC
  3  WHERE ENTITY_ID = 'D6935' AND
  4  ENTITY_TYPE = 'ACCNT' AND
  5  START_DATE >= TO_DATE('01-01-1900' ,'DD-MM-YYYY HH24:MI:SS')
  6  AND END_DATE <= TO_DATE('31-12-2099' ,'DD-MM-YYYY HH24:MI:SS')
  7  AND ENTITY_CRE_FLG = 'Y'
  8  AND DEL_FLG = 'N'
  9  AND TO_CHAR(LCHG_TIME,'DD-MM-YYYY HH24:MI:SS') = (
 10          SELECT TO_CHAR(MAX(LCHG_TIME),'DD-MM-YYYY HH24:MI:SS')
 11          FROM TBA_INT_TBL_CODE_TBL
 12          WHERE START_DATE >= TO_DATE('01-01-1900' ,'DD-MM-YYYY HH24:MI:SS')
 13          AND END_DATE <= TO_DATE('31-12-2099' ,'DD-MM-YYYY HH24:MI:SS')
 14          AND ENTITY_ID = 'D6935' AND ENTITY_TYPE = 'ACCNT'
 15          AND ENTITY_CRE_FLG = 'Y'
 16          AND DEL_FLG = 'N' )
 17  /

INT_T TO_CHAR(LCHG_TIME,'
----- -------------------
CLOD  01-06-2000 18:14:45

Elapsed: 00:00:00.29

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=49)
   1    0   FILTER
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'INT_TBL_CODE_TABLE' (C
          ost=3 Card=1 Bytes=49)

   3    2       INDEX (RANGE SCAN) OF 'IDX_INT_TBL_CODE_TABLE' (UNIQUE
          ) (Cost=2 Card=1)

   4    1     SORT (AGGREGATE)
   5    4       TABLE ACCESS (BY INDEX ROWID) OF 'INT_TBL_CODE_TABLE'
          (Cost=3 Card=1 Bytes=45)

   6    5         INDEX (RANGE SCAN) OF 'IDX_INT_TBL_CODE_TABLE' (UNIQ
          UE) (Cost=2 Card=1)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        482  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>
SQL> SELECT INT_TBL_CODE, lcg FROM
  2  (SELECT  INT_TBL_CODE,
  3  TO_CHAR(LCHG_TIME, 'DD-MM-YYYY HH24:MI:SS') lcg
  4  FROM ITC
  5  WHERE ENTITY_ID = 'D6935' AND
  6  ENTITY_TYPE = 'ACCNT'
  7  AND START_DATE >= TO_DATE('01-01-1900' ,'DD-MM-YYYY HH24:MI:SS')
  8  AND END_DATE <= TO_DATE('31-12-2099' ,'DD-MM-YYYY HH24:MI:SS')
  9  AND ENTITY_CRE_FLG = 'Y'
 10  AND DEL_FLG = 'N'
 11  order by LCHG_TIME DESC )
 12  where rownum =1
 13  /

INT_T LCG
----- -------------------
CLOD  01-06-2000 18:14:45

Elapsed: 00:00:00.30
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=15)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=10 Card=1 Bytes=15)
   3    2       SORT (ORDER BY STOPKEY) (Cost=10 Card=1 Bytes=49)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'INT_TBL_CODE_TABLE
          ' (Cost=3 Card=1 Bytes=49)

   5    4           INDEX (RANGE SCAN) OF 'IDX_INT_TBL_CODE_TABLE' (UN
          IQUE) (Cost=2 Card=1)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        444  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA_at_infosys.com

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).