Re: Question about Queries

<mrdjmagnet_at_aol.com> wrote in message
news:506da0b8-7c19-452a-90cb-5afbed98e546_at_c36g2000prc.googlegroups.com...
>
> Hi,
>
> 2 questions here:
>
> First, in terms of minimizing the number of parse/execute calls, it is
> suggested to use bind variables. However, why would a statement
> like: "SELECT address FROM emp WHERE name = 'John Doe';" get re-
> parsed? The statement is always the same, and never changes.
>
> Or, "SELECT product_name FROM product WHERE product_id = v_id AND
> status = 'Active';"
>
> There, v_id is a variable and 'Active' is a literal, never
> changing.........so, why get re-parsed when it can re-use the
> statement?
>
> Second, is using EXECUTE IMMEDIATE for doing a SELECT faster than a
> normal coded SELECT?
>
> Much Thanks!

SELECT address FROM emp WHERE name = 'John Doe';" won't get reparsed IF that is exactly what you send each and every time. (soft parse, which with some languages you could further avoid) But SELECT address FROM emp WHERE name = 'John Doe';" is NOT the same as
SELECT address FROM emp WHERE name = 'Susan Smith';" Oracle matches on the whole string. so those 2 are different. Yes, in your 2nd example it would not get a hard reparsed. if you can use normally coded sql select use it instead of execute immediate. Execute immediate requires extra processing and is more prone ot error.
Jim Received on Wed Dec 24 2008 - 12:52:24 CST