Hi guys,
I have a SP having input parameters and the execution plan should depend on the parameters provided to the procedure.
Ex : PROCEDURE GetData( DataType int, DataValue int )
I want this procedure to search DataValue in column1 if DataType = 1 and to search DataValue in column2 if DataType = 2.
I wrote something like this :
SELECT * FROM Data
WHERE ( DataType = 1 AND column1 = DataValue )
OR ( DataType = 2 AND column2 = DataValue )
Column1 and Column2 are indexed but it seems that Oracle does not choose the right execution plan.
Does oracle keep the SP execution plan in the cache ?
If this is the case, is there any way to force Oracle to use the right execution plan.
I know that I could write my SP as
if ( DataType = 1 ) THEN
ELSE ....
but the procedure in reality is much more complex and this would require a lot of code duplication which I would like to avoid.
Thanks for your help
The execution plan does depend on the input variables!
But if you have many OR conditions like:
SELECT * FROM Data
WHERE ( DataType = 1 AND column1 = DataValue )
OR ( DataType = 2 AND column2 = DataValue )
OR ( DataType = 3 AND column3 = DataValue )
OR ( DataType = 4 AND column4 = DataValue )
...
The optimizer struggles to come up with a good plan. You can read more about this at:
https://blogs.oracle.com/sql/optimizing-the-plsql-challenge-iv:-more-or-condition-woes If there are too many optional parameters to make a static SQL query for each, look into dynamic SQL. Here you construct the where clause at runtime:
sql_where := ' ... ';
if ( DataType = 1 ) THEN
sql_where := sql_where || ' AND column1 = :DataValue ';
elsif ( DataType = 2 ) THEN
sql_where := sql_where || ' AND column2 = :DataValue ';
elsif ... etc.
Read more about dynamic SQL at:
https://dzone.com/articles/a-quick-guide-to-writing-dynamic-sql-in-plsql