Why a subsample query costs less than a constant query in Oracle

Question

I have an SQL table with several million records, and I tried to query how many records are older than 60 days (Oracle 11.2.0.1.0).

For this experiment, I used 3 different versions of select-statement:
(Cost value is indicated by TOAD for Oracle V. 9.7.2.5)

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

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

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

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

I don't have exact time values ​​for all requests, but the first one was really the fastest.

So, I tried some more select queries with other subqueries (e.g. (Select 1000 from dual)), and they were (sometimes WAY) faster than others with constant values. It seems that this "WHATEVER" (Bug / Feature) also happens in MySQL.

So can someone tell me why the first request (path) is faster than the others?

Greetz

PS: This is not about sidate! Question: WHY IS CHANGE WITH (SELECT) QUICKLY THAN OTHERS? (with a slight focus on Select-Variation (1.) versus constant variation (4.))

Answer 1

Found some hints of my copy of Jonathan Lewis's Oracle Cost-Based Fundamentals in Chapter 6, The Amazing Sysdate. This seems to apply to 9i, possibly to later versions.

The optimizer considers sysdate (and trunc (sysdate) and several other sysdate functions) as known constants during parsing, but sysdate + N becomes unknown and receives the same processing as the binding variable, which means a fixed 5% selectivity. (Note in particular that sysdate + 0 will give different power from sysdate.)

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

Answer 2

Tom Kyte :

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

Answer 3

Have you repeated the number 2-4 s () around the calculation after> = - it seems to me that the first operator is the only one where it calculates this value once - for all the others, it recounts each line. 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 - I don't know the syntax for converting to datetime in Oracle, but you get the idea.

Answer 4

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

A few links to installing and using an explanation plan:

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

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

Answer 5

instead of using (select sysdate - 60 from dual) I would rather recommend that you use a bind variable whose value is calculated before the request is executed