REPOST: Re: Explain plan problem

Mike

I assume the bind variable in the first query is a date. If this is the case then I assume the problem is that you are subtracting 2 dates from each other and the using number functions on them. Have you tried adding to_number around the date subtraction function?

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

Hope thins works (can't try it currently - having fun installing 9i @ Home)

Nathan

"Mike F" <u518615722_at_spawnkill.ip-mobilphone.net> wrote in message news:l.1012051105.1249572753_at_pool-151-197-235-121.phil.east.verizon.net...
> I have a table test
> SQL> desc test
> Name Type
> --------------- ----------------------------
> START_TIME DATE
> END_TIME DATE
>
> SQL> explain plan for
> 2 select (end_time - :b1)*60*24
> 3 from test
> 4 where end_time < :b1
> 5 and start_time > :b1;
> select (end_time - :b1)*60*24
> *
> ERROR at line 2:
> ORA-00932: inconsistent datatypes
>
> but if I remove *60*24, I can explain it.
> 1 explain plan for
> 2 select (end_time - :b1)
> 3 from test
> 4 where end_time < :b1
> 5* and start_time > :b1
> SQL> /
>
> Explained.
>
> But the first statement does not seem to use the same plan as the
> second one, instead, the first statement use a much more expensive
> one, why?
>
> The second question, for the 1st statement, if oracle can not explain
> it, why oracle can execute it without giving error message?
>
> Thanks for you help.
>
>
>
>
>
> --
> Sent by dbadba62 from hotmail within 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: "Nathan Carney" <ncarney_at_btinternet.com> Control: cancel <a30msj$r80$1_at_paris.btinternet.com> Subject: cmsg cancel <a30msj$r80$1_at_paris.btinternet.com> Date: Mon, 28 Jan 2002 00:18:18 GMT Message-ID: <cancel.a30msj$r80$1_at_paris.btinternet.com> 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!news.fh-hannover.de!news-han1.dfn.de!news-nue1.dfn.de!newsfeed.r-kom.de!newsfeed.freenet.de!peer.news.opaltelecom.net!out.nntp.be!propagator-SanJose!in.nntp.be!news-in-sanjose!sjc-feed.news.verio.net!sea-feed.news.verio.net!news.verio.net!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp03!u&n&a&c&anceller Xref: news.uni-stuttgart.de control:40715914

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Sun Jan 27 2002 - 05:04:52 CST