Re: Puzzling execution plan

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 12 Jun 2004 09:28:32 +0100
Message-ID: <08ef01c45057$3f4cef80$7102a8c0@Primary>

For the purposes of calculating selectivity, Oracle treats sysdate as a constant,
and will be treating your to_date(literal) as a constant. Until 10g, Oracle will treat sysdate - 1 as a bind variable.

This makes the selectivity of the last example 5%, but the selectivity of the first two:

(high_value - sysdate) /
(high_value - low_value)

It would appear that Oracle disagrees with you about the 0.07%, though - or the first two examples would also give you a range scan.

One possibility (that doesn't seem to tally with the name 'insert_date' for the column) is that some of the data has a date in the far future. This is a trick that is often used in 3PAs (viz: don't use nulls, use 31st-dec-4000) to make an application "database independent".

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sat Jun 12 2004 - 03:25:17 CDT