Re: sql question

Bill Hails writes:- > graeme@pyra.co.uk writes: > > In <27o4ejINNdfs@emory.mathcs.emory.edu> inf_bb@hermes1.sps.mot.com (Bob Baskett) writes: > > >can someone explain why the optimizer doesnt short circuit on the following > > >query: > > > > > > select * > > > from book_bill_back > > > where 1=2> > > > > > [..] > > > In some ways this behaviour is reasonable, after all *you* should know > > whether 1 = 2, the optimiser doesn't expect you to be daft enough to say > > WHERE <constant> = <constant> !!! :-) > > This is a perfect example (IMHO) of possibly *the* major failing of > Informix and other Relational database products in that they make the > fallacious assumption that the end user always generates the query > directly, and always reads the results directly. The query may have > been generated by another program (I write them all the time) and that > program may not know that 1 does not equal 2, but should reasonably > expect the optimiser to notice. (for example a template query which > requires a filter, because it's easier to write that way, might just > have the identity filter: `where 1 = 1' slotted in if no other filter > is appropriate, or `where 1 = 2' when no output is desired). I'm not sure I understand this. What is the complaint. Is it that it allows the constructs 1=1 and 1=2 or that the engine should not bother to scan an entire table when it has a permanently false filter. The above constructs are legal in ANSI SQL as far as I am aware so must be supported. They can also be useful as described above and in my previous email. If we are talking about whether the optimiser should be more intelligent than it is, that is an argument that one can have forever, all I will say is that it does seem to get somewhat better with each release even if there are still many times I want to kick it because it refuses to choose the path "I" want it to!! For instance it sometimes ignores an index that would suit the order by statement and a join in preference to an index that is a direct match for the join but doesn't suit the order by. This bugs the hell out of me but I have put requests in to have it fixed. What are people out there expecting 1=2 and 1=1 to do? Consider the case where 1=2 is one filter amongst many, part of a complex where clause which has many OR's and AND's. > In the same vein, I've wasted many hours writing programs to parse the > output of tbcheck, tbstat etc, to produce more digestable information > or to mail me when the logical log tape appears to be full. I do not > want to continually have to inspect the status of the database > manually. I fully agree with this. Working out what is happening in Informix's internals is a @#!%$^&&. But I have just finished reading a write up of the facilities in V6 and this should solve many of these complaints as it will provide a set of internal views of the shared memory and set up information accessable via SQL. This should allow you to make any kind of cross query you want. Of course we will have to wait for copies of V6 to be available!! > =========================================================================== > | Bill Hails <bill@tardis.co.uk> | | Cheers - Jim My opinions are my own. They may vary with time but they remain MINE! ---------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 375-5222 (Work) Address: 700 Airport Blvd. #300 (415) 882-9728 (Home) Burlingame, CA 94010-1937 Fax: (415) 375-5019 ----------------------------------------------------------------------