Re: NVL2 in PL/SQl ?

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3CCEBDB8.EEEDB0AC_at_exesolutions.com...
> Chris Weiss wrote:
>
> Native dynamic SQL is a "significant" performance hit?
>
> Daniel Morgan
>

On a first run parse, native dynamic SQL is a minimum of 2x slower compared to a stored procedure or package. I have benchmarked this. With bind variables and repeated execution the hit goes down to around 5%, while on repeated runs for pure dynamic, the hit is no less than 50%, and it is worse if the statement varies on each execution.

In general, it is good to avoid dynamic SQL where possible since schema changes and other configuration items will not knock the supporting code invalid, meaning errors do not appear until run time.

If the user needs an if/then/else structure Connor McDonald's post is the better answer. If the user needs this as part of a SQL statement such as in a query, then decode or SIGN is a better alternative.

--

~~~~~~~~~~~~~~~~
Chris Weiss
www.hpdbe.com
High Performance Database Engineering
~~~~~~~~~~~~~~~~