Menu
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)
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
(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.))
929
Cost is the estimated amount of work the plan will do. A higher cardinality => you're going to fetch more rows => you're going to do more work => the query will take longer. Thus the cost is (usually) higher. All other things being equal, a query with a higher cost will use more resources and thus take longer to run.
The cost of the plan is reduced by rewriting the SQL statement to use an EXISTS . This plan is more effective, because two indexes are used to satisfy the predicates in the parent query, thus returning only a few employee_ids . The employee_ids are then used to access the orders table through an index.
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.
190
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.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
