Re: sql/plsql query question

roger wrote:

>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.
>
>

It is better because it is designed to do this. Your assumption about what may or may
not be run and under what conditions may be flawed. NDS solves the problem.

Why the apparent resistance to the suggestion everyone has made?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)