Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> REPOST: explain a sql statement
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
*
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 ========= WAS CANCELLED BY =======: From: u518615722_at_spawnkill.ip-mobilphone.net (Mike F) Control: cancel <l.1011990869.1490966796@[64.94.198.252]> Subject: cmsg cancel <l.1011990869.1490966796@[64.94.198.252]> Date: Sun, 27 Jan 2002 23:35:24 GMT Message-ID: <cancel.l.1011990869.1490966796@[64.94.198.252]> X-No-Archive: yes Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.misc NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88 Lines: 1 Path: news.uni-stuttgart.de!dns.phoenix-ag.de!newsfeed01.sul.t-online.de!newsfeed00.sul.t-online.de!t-online.de!newsfeed.gamma.ru!Gamma.RU!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp03!u&n&a&c&anceller Xref: news.uni-stuttgart.de control:40716293 This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers.Received on Fri Jan 25 2002 - 14:34:28 CST