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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
