Re: sql question

Bob Baskett writes:- > can someone explain why the optimizer doesnt short circuit on the following > query: > > QUERY: > ------ > select * > from book_bill_back > where 1=2> > Estimated Cost: 370832 > Estimated # of Rows Returned: 4330952 > > 1) book_bill_back: SEQUENTIAL SCAN > > Filters: 1 = 2 I would answer why should it. You have asked it to scan the table and compare the rows against a filter which will always be false. This will return no rows. Much the same as saying 1=1 where all rows are returned. I use the technique quite a lot when I lift Select statements out of programs and put them in dbaccess/isql to run set explain against them. I will replace things like "field is null and program variable is null" with "field is null and 1=1" or "field is null and 1=2" depending on the type of input data I am trying to replicate. 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 ----------------------------------------------------------------------