Re: sql/plsql query question

Daniel Morgan <damorgan_at_x.washington.edu> wrote in news:1068147637.184014 @yasure:

> It seems strange to say this but I agree with nobody.
>
> Use native dynamic SQL and be sure to utilize the USING CLAUSE and bind
> variables
> or you will make a horrible mess.
>

Thanks for your thoughts, though I must say, I'm having a hard time understanding why dynamic SQL would be better. Can you explain why that is?

Did you see my other followup post, where I noted that it appears to me that the unused sub-select queries are not executed when the tested parameter value is null. eg

  (p_target_value is null or

     t1.some_column in
        (select t2.some_column from some_other_table t2
           where some_other_column = log_access(p_target_value)))

My observation is that if p_target_value is null, then all of the rows are selected, which is correct, and the function log_access is not executed, which is also correct.

My other observation is that the query executes in about the same amount of time with whether I have several such predicates and all null parameter values, or if I just omit the conditional predicates from the query.

These two observations lead me to believe this approach is OK, but I'm certainly willing to be convinced why that is wrong. But, please explain the *why* part.

Frankly, I'd be disappointed in the SQL optimizer if it didn't recognize that given the logic (A or B) that if A is true there is no need to evaluate B.
Could be my traditional views of how compilers generally behave getting in the way again...

Thanks. Received on Thu Nov 06 2003 - 14:58:51 CST