Why my ACCESS PATH changes if I use the parameters in the query?

[Lavr]

Hello,

I have such view:

CREATE OR REPLACE VIEW V_TEST
AS
SELECT
A.PARENT_ID,
B.*
FROM A, B
WHERE
A.A_ID = B.B_CUR_ID


When I use this SELECT:

SELECT * FROM v_test WHERE parent_id = 10

I have no problem, the Explain Plan shows me that the Index for the table B is used.

But when I use the parameter:

SELECT * FROM v_test WHERE parent_id = :PARENT_ID

it runs very slow and the Explain Plan shows me that for both tables A and B ACCESS FULL is used. But this SELECT must return only 3-5 records!

Why the Acess Path changes when I use the parameter instead of value in my query?
How can I avoid this problem?

I know that I can use the optimizer hints, but maybe I can make the view itself better?

[Philippe]

Hi Lavr,

As per your description I guess that you are using the Cost Based Optimizer an it is likely that the the optimizer is not using the index because it assumes that with the bind variables the percentage of records to return is high and thus it choose to use FTS.

I would advise first to run ANALYZE TABLE ... {COMPUTE|ESTIMATE} STATISTICS FOR ALL INDEXED COLUMNS on both tables.
In the second step we must consider two cases

1) If you are using 9i

Starting with 9i Oracle replace the bind variable with value in the statement before choosing the exec plan. So it is likely that after analyzing as mentionned above the tables the optimizer will use indexes.

2) with 8i or prior version

Oracle is not aware of the bind variable values when choosing exec plan so it assumes a low selectivity of the bind variable so it uses FTS.
In this case you may consider using dynamic SQL (e.g execute immediate) to hardcode literals or drop statistics to force the use off rule based.
The rule based optimizer will use indexes whenever there’s index to use.

If it doesn’t help, please provide exec plan of your query

Hope this help.

Philippe

[Lavr]

Thank you Philippe!
After ANALYZE TABLE it works good, the index is used.
But does it mean that I only must regularly perform ANALYZE TABLE?
And how often it must be?

[Philippe]

To make accurate exec plan choice, the Cost Based Optimizer needs accurate satatistics.
I can’t give you a precise scheduling for table analysis. The frequency of analysis depends of the use of the tables.
If there’s high DML activity on these tables then “analyze” should be frequent too. If it’s read only table, then tables (and indexes) need to be analyzed only once…
This is the kind of maintenance operation that should be performed in nightly batch job. For instance if you regularly perform massive batch load on your tables, you should perform analyze right after the bulk load.
Alternatively there’s way to make the exec plan choice stable. You can use hint on views as you mentioned or stored outlines…

[Lavr]

Philippe, thank you very much for your advices!