Explain Plan of a query

[nik_flash]

I have a query regarding explain plan.
In the following query

Select customer_name from customer
where
customerid=1234
OR :s_status = 1

In case the value being passed in s_status is not 1.
Will the index on customerid will still be suppressed i.e whether the explain plan will be generated with the bind_variable taking OR condition in consideration (i.e doing a full scan on Customer) or
with the actual values at the run time.
In short is the explain plan is generated during parsing only or while
executing it with the values being passed in the bind variables.

[chrisrlong]

It is generated at parse time.

Basically, you don't want to do what you are doing here.

If this is a real language(and not, say Oracle Forms), then what you want to do is first test the variable or variables, then build the exact SQL you want to execute (which can still use binds, mind you), and run that exact SQL.

So, in your case, something like:

CREATE OR REPLACE PROCEDURE Garbage
(
i_Status IN NUMBER ,
ioRefCur IN OUT T_RefCur
)
AS
l_SQL VARCHAR2(32767)
BEGIN
l_SQL := '
SELECT
*
FROM
CUSTOMER '
IF (i_Status <> 1) THEN
l_SQL := l_SQL || '
WHERE
CUSTOMERID = 1234 '
END IF;

OPEN
io_RefCur
FOR
l_SQL ;
END;

- Chris

[nik_flash]

Thanx Chris,
I was asking this in context of Pro*C++.Actually I cannot check what value is getting passed in the bind variable.Since it is also a generated code so I dont waana patch it.So I just wanted to know whether the explain plan is generated by the sql with bind variables or after getting the exact value of the bind variable.So in case the query has some performance issue I can look for an alternate way of writing it.But in case the explain plan is generated with the actual values then the query will work fine since it will never go in the OR clause in my case.

Originally posted by chrisrlong
It is generated at parse time.

Basically, you don't want to do what you are doing here.

If this is a real language(and not, say Oracle Forms), then what you want to do is first test the variable or variables, then build the exact SQL you want to execute (which can still use binds, mind you), and run that exact SQL.

So, in your case, something like:

CREATE OR REPLACE PROCEDURE Garbage
(
i_Status IN NUMBER ,
ioRefCur IN OUT T_RefCur
)
AS
l_SQL VARCHAR2(32767)
BEGIN
l_SQL := '
SELECT
*
FROM
CUSTOMER '
IF (i_Status <> 1) THEN
l_SQL := l_SQL || '
WHERE
CUSTOMERID = 1234 '
END IF;

OPEN
io_RefCur
FOR
l_SQL ;
END;

- Chris

[chrisrlong]

Fair enough, but you did notice that I did answer your question, right?

Secondly, there's no need to quote my entire response - I already know what I said :D

- Chris