Optional parameter in where condition - How to increase performance - PL/SQL

Question

I have a stored procedure that takes optional parameters from a Json structure. If the json values is provided, the parameters will be used as a conditions for the cursors. If json value is not provided I don't want that condition. I have solved this by using Coalesce/Nvl in the sql condition. The problem is that the procedure is running for a very long time using Nvl/Coalesce. Is there a different method I can use which is more efficent? Perhaps dynamic sql?

My procedure:

Create or Replace Procedure findaccounts


(jsonIn IN clob,
 jsonOut OUT varchar2)
 As

obj json := json(jsonIn);
json_obj_out json;
json_lst json_list := json_list();

--Getstring is a function that sets variable to null if json value is not found
istatus varchar2(10) := GetString(obj json, 'status');
icreatedate := GetString(obj json, 'daysold'); 
iage int := GetString(obj json, 'age');
irownum int := GetString(obj json, 'rownum');

Begin
For rec in (Select A.accountnumber 
From Accounts A 
Inner Join Accountowner Ao On A.ownerId = Ao.Id
Where A.Status = iStatus
And A.daysold >= Coalesce(idaysold,A.daysold)
And Ao.Age = Coalesce(iAge,Ao.Age)
And rownum <= Coalesce(iRownum,5))

    loop

      obj := json();

      obj.put('accountnumber',Rec.accountnumber);
      json_lst.append(obj.to_json_value);

    end loop;

    json_lst.print;
    jsonOut := json_lst.to_char();  

End;    

resolved:

Performance boosted with dynamic sql. The option to have dynamic bind variables was resolved via dbms_sql package as execute immediate does not have this option.

Answer 1

Functionally, Coalesce is a great way to handle optional search criteria. Performancewise it is not good. The optimiser will not be able to optimize efficiently since it decides on the execution plan only once, optimising for the search criteria provided the first time the SQL is run.

If different executions plans would be required to get acceptable performance then you need to use different SQL statements for those different search patterns.

Here are two different solutions that I have used in the past:

1. Create several different SQL:s where each handles a subset of the possible search criteria that will share the same execution plan. In your case you could have one you use when iAge is null, another when idaysold is null and a third when neither is null.

2. Use dynamic SQL.

Answer 2

You can eliminate the COALESCE() function like this:

And ( idaysold IS NULL OR A.daysold >= idaysold )
And ( iAge     IS NULL OR Ao.Age    = iAge )
And ( ( iRownum  IS NULL AND ROWNUM <= 5 ) OR rownum <= iRownum )

Answer 3

NVL is usually the most efficient method for filtering by optional parameters. It will generally perform better than COALESCE, OR, CASE, DECODE, and other similar solutions.

Switch back to that method, and if it's not working well there might be some other underlying problem. Find the explain plan or the SQL monitoring report for the statement and post it here for further advice.

NVL usually works best because it is most likely to create CONCATENATION and FILTER operations. (Note that a FILTER operation is different than the Filter section at the bottom of the explain plan.) Those operations allow Oracle to create two different execution plans and choose one at run time depending on the bind variables.

Ideally the explain plan will look something like this:

----------------------------------------------------
| Id  | Operation                     | Name       |
----------------------------------------------------
|   0 | SELECT STATEMENT              |            |
|   1 |  CONCATENATION                |            |
|   2 |   FILTER                      |            |
|   3 |    TABLE ACCESS FULL          | MYTABLE    |
|   4 |   FILTER                      |            |
|   5 |    TABLE ACCESS BY INDEX ROWID| MYTABLE    |
|   6 |     INDEX UNIQUE SCAN         | MYTABLE_PK |
----------------------------------------------------

See my answer here for a test script that demonstrates how NVL can work.

I think there's a chance your code has two unlucky problems. Without the NVL it won't generate separate execution plans. With the NVL it may generate two plans, but perhaps both of them are bad. Ff even the NVL won't generate separate plans then I suggest you look into Klas Lindbäck's answer.