Oracle-L: Inline View Query Versus Ordinary Query

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Tue, 21 May 2002 13:23:42 -0800
Message-ID: <F001.00466AA9.20020521132342@fatcity.com>

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

Received on Tue May 21 2002 - 16:23:42 CDT

Inline View Query Versus Ordinary Query - Comparison