Why does a query with a sub-select cost less than query with a constant in Oracle

Question

I've got an SQL-table with some million entries and I tried to query how much entries are older than 60 days (Oracle 11.2.0.1.0).

For this experiment I used 3 different versions of the select-statement:
(The cost-value is given by TOAD for Oracle V. 9.7.2.5)

1. select count(*) from fman_file
where dateadded >= (select sysdate - 60 from dual)
   Cost: 65

2. select count(*) from fman_file
where dateadded >= sysdate - 60
   Cost: 1909

3. select count(*) from fman_file
where dateadded >= sysdate - numtodsinterval(60,'day')
   Cost: 1884

4. select count(*) from fman_file where dateadded >= '10.10.2009'
   Cost: 1823
   (The 10.10.2009 is just an example-date!!!)

I don't have the accurate time-values for all queries in mind, but the first one really was the fastest.

So I tried some more select-queries with other subselects (like (Select 1000 from dual)) and they were (sometimes WAY) faster than the others with constant-values. It even seems that this "WHATEVER" (Bug/Feature) is happening in MySQL too.

So can anyone tell me why the first query is (way) faster that the others?

Greetz

P.S.: This ain't about the sydate! The question is WHY IS THE VARIATION WITH THE (Select) FASTER THAN THE OTHERS? (with a little focus on Select-Variation(1.) vs. Constant-Variation (4.))

Simson

Can't tell you why, but if you're trying to determine the counts of records OLDER than 60 days, isn't your relational operator reversed? – Serrulate

I can reproduce this effect also on 10.2.0.2.0, but the difference is much smaller: 4723 with subselect, 4736 with simple comparison. – Frick

A lower "cost" doesn't necessarily mean "faster". Although it might have some value in a very simple query like yours, normally you cannot compare cost values from one query to another. Their purpose is to compare different plans for the same query. – Trusteeship

@ Jeffrey Kemp: u r right, but i've executed the queries serval times and it REALY was faster! (like i wrote above: Qoute: "I don't have the accurate time-values for all queries in mind, but the first one really was the fastest.") @DCookie: Typing mistake. :) – Simson

Question still not answered... could someone push it pls? – Simson

Answer 1

Found some hints in my copy of "Cost-Based Oracle Fundamentals" by Jonathan Lewis in chapter 6 "surprising sysdate". This seems to apply to 9i, probably also later versions.

The optimizer treats sysdate (and trunc(sysdate) and a few other functions of sysdate) as known constants at parse time, but sysdate + N becomes an unknown, and gets the same treatment as a bind variable - which means a fixed 5% selectivity. (Note in particular that sysdate + 0 will give a different cardinality from sysdate.)

Apparently the optimizer also recognizes the select sysdate from dual as a known constant.

Mehala

Answer 2

Tom Kyte:

The advantage to dual is the optimizer understands dual is a special one row, one column table -- when you use it in queries, it uses this knowledge when developing the plan.

Spiv

Answer 3

Did you re-try numbers 2-4 with () around the calculation after the >= -- it seems to me the first statement is the only one where it calculates that value once -- for all the others it recalculates on every row. eg:

select count(*) from fman_file where dateadded >= (SELECT sysdate - 60) 

select count(*) from fman_file where dateadded >= (SELECT (sysdate - numtodsinterval(60,'day'))

select count(*) from fman_file where dateadded >= (SELECT CONVERT(datetime,'10.10.2009')) 

NB -- don't know the syntax to convert to a datetime in Oracle -- but you get the idea.

Novelty

Answer 4

You could try using Explain Plan. This will show you what the queries are doing and the differences between them.

A couple of links to setup and use explain plan:

http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/ex_plan.htm

http://www.adp-gmbh.ch/ora/explainplan.html

Visionary

Answer 5

instead of using (select sysdate - 60 from dual) i'd rather recommend you using a bind variable, which value is calculated before the query become executed

Streaky