explain a sql statement

From: Mike F <u518615722_at_spawnkill.ip-mobilphone.net>
Date: Fri, 25 Jan 2002 20:34:28 GMT
Message-ID: <l.1011990869.1490966796@[64.94.198.252]>

I can not explain the following sql statement. SQL> create table test (start_time date,end_time date); Table created.

SQL> explain plan for
  2 select (end_time - :b1) * 60*24 minutes_remaining   3 from test
  4 where start_time < :b1
  5 and end_time > :b1;
select (end_time - :b1) * 60*24 minutes_remaining

                 *

ERROR at line 2:
ORA-00932: inconsistent datatypes

This is the most high disk reading sql statement (simplified here for easy understanding) in our system, issued by java programs.

If I hard-coded the (end_time - :b1) to (end_time - to_date('2002-01-23  23:59:59'), then I can explain in, and create an outline for it, but an outline created is useless, is it?

The problem is, for statement

select (end_time - to_date('2002-01-23 23:59:59'))*60*24

    from test
    where start_time < :b1
    and end_time > :b1;

the cost is only 50 with no full table scan, when I change it to

select (end_time - to_date('2002-01-23 23:59:59'))*60*24

     from test
    where start_time < to_date('2002-01-23 23:59:59')     and end_time > to_date('2002-01-23 23:59:59');

there will be a full table scan with cost rising up to 500. And our oracle IS USING this path to execute the sql. And I need to fix this problem.

What is going on here? Why does to_date function cause a full table scan? How should I advice our developper to improve the sql, or how could I just create an outline for it to bypass the problem?

Thanks for your help.  

-- 
Sent  by dbadba62 from hotmail in field  com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new

Received on Fri Jan 25 2002 - 14:34:28 CST