| SQL Query : Performance Isssue [message #59146] |
Fri, 31 October 2003 08:53  |
Vinny75
Messages: 44
Registered: October 2003
|
Member |
|
|
Folks,
I have a question regarding alternatives in writing a query. We have a view that does some complex DML. Application developers here build sql query based on this view and retrieve their data. Here are my options:
Option 1:
select * from my_view
where created between sysdate and (sysdate+90)
order by 1,2
Option 2:
select * from (
select * from my_view
where created between sysdate and (sysdate+90)
) a
order by a.1,a.2
Does option 2 perform better because we do the order by after fetching all valid data? Or do they perform the same?
Thanks
Vinny
|
|
|
|
| Re: SQL Query : Performance Isssue [message #59151 is a reply to message #59146] |
Fri, 31 October 2003 11:38  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
In both cases, the sort is performed after fetching the required data(ie the where clause is applied before the order by ) ..so the 2nd query does not perform better than the 1st one.
example :
SQL> drop table t;
Table dropped.
SQL> create table t as select * from all_objects where created > sysdate-350 and created < sysdate and rownum < 11;
Table created.
SQL> create index t_idx on t(created);
Index created.
SQL> analyze table t estimate statistics;
Table analyzed.
SQL> select object_name,created from t where created > sysdate-350 and created < sysdate order by 1,2;
OBJECT_NAME CREATED
------------------------------ ---------
/1001a851_ConstantDefImpl 15-JAN-03
/1005bd30_LnkdConstant 15-JAN-03
/10076b23_OraCustomDatumClosur 15-JAN-03
/1019cfe7_RdbmsProtectionMappi 15-JAN-03
/10297c91_SAXAttrList 15-JAN-03
/10322588_HandlerRegistryHelpe 15-JAN-03
/103a2e73_DefaultEditorKitEndP 15-JAN-03
/1048734f_DefaultFolder 15-JAN-03
/104b85c5_LogFileOutputStream 15-JAN-03
/10501902_BasicFileChooserUINe 15-JAN-03
10 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 SORT (ORDER BY) (Cost=4 Card=1 Bytes=34)
2 1 FILTER
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 By
tes=34)
4 3 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 C
ard=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1162 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select * from ( select object_name,created from t where created > sysdate-350 and created < sysdate) a
2 order by a.object_name,a.created;
OBJECT_NAME CREATED
------------------------------ ---------
/1001a851_ConstantDefImpl 15-JAN-03
/1005bd30_LnkdConstant 15-JAN-03
/10076b23_OraCustomDatumClosur 15-JAN-03
/1019cfe7_RdbmsProtectionMappi 15-JAN-03
/10297c91_SAXAttrList 15-JAN-03
/10322588_HandlerRegistryHelpe 15-JAN-03
/103a2e73_DefaultEditorKitEndP 15-JAN-03
/1048734f_DefaultFolder 15-JAN-03
/104b85c5_LogFileOutputStream 15-JAN-03
/10501902_BasicFileChooserUINe 15-JAN-03
10 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 SORT (ORDER BY) (Cost=4 Card=1 Bytes=34)
2 1 FILTER
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 By
tes=34)
4 3 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 C
ard=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1162 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
