Thanks for the question, Manikandan.
Asked: October 17, 2017 - 3:12 pm UTC
Last updated: October 18, 2017 - 12:14 am UTC
Version: 11g
Viewed 1000+ times
You Asked
The below 2 queries returns the same results.
The first one takes very long time and the cost of fetching the records from the custdata is around 82000. I cancelled the query since it takes longer.
But the second query takes the cost of only 2 and returns records faster. whats the difference in sysdate between two methods.
select nvl(pos.qty,0) nqty,pos.*,
sysdate as current_timestamp
from custdata.pos_data pos
where create_date >= (select sysdate -1 from dual)
and src = 'WAL852'
and feed_type ='INV'
and (qty >= 0 or qty is not null)
Query 2:
select nvl(pos.qty,0) nqty,pos.*,
sysdate as current_timestamp
from custdata.pos_data pos
where create_date >= (sysdate -1)
and src = 'WAL852'
and feed_type ='INV'
and (qty >= 0 or qty is not null)
and Connor said...
For simple queries, the optimizer can treat them as being synonymous, but queries can be transformed etc before plans are created, so we will need to see all of the execution plan details for each.
SQL> create table t as select * From dba_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats('','T');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select * from t where created > sysdate;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75 | 7350 | 347 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 75 | 7350 | 347 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CREATED">SYSDATE@!)
SQL> select * from t where created > ( select sysdate-1 from dual );
Execution Plan
----------------------------------------------------------
Plan hash value: 3529032321
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75 | 7350 | 349 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 75 | 7350 | 347 (1)| 00:00:05 |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CREATED"> (SELECT SYSDATE@!-1 FROM "SYS"."DUAL" "DUAL"))