Optimizer Results

Since we're on a "Fun With the Optimizer" thread, I was wondering if anyone has ever tried to get anything meaningful out of the optimizer before processing the query. I believe that when you prepare a cursor, you can get the estimated cost and estimated rows back after the prepare. (I've never done, but I think this is possible). Now, we've written code that checks for "1=1" from the construct statement to keep users from "selecting everything", but that doesn't prevent them from putting a '*' on something like "cust_name" and thereby passing the "1=1" test. Next thing you know, the user is stuck staring at the screen while the engine runs off to collect a billion rows. Nowadays, the user can actually kill the query in 4GL, but it would be really nice if you could look at what the optimizer returns and try to make some value judgement as to whether its a reasonable query. Of course, when I've looked at sqexplain.out from various queries, the numbers seem to swing with little regard to actual query time. And there have been times when a "cheap" query has taken longer than an "expensive" query. So I'm just trying to see if anyone has actually used those numbers in a situation to judge whether a query is something that the user may not want to do. Will (la4gen!villy@uunet.uu.net)