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.

score 2 · Accepted Answer · answered Feb 03 '17 at 13:34

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:

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.
Use dynamic SQL.

thanks. Issue resolved via dbms_sql. I could not find a way to have optional bind variables via execute immediate. — MrM, Mar 07 '17 at 14:23

score 0 · Answer 2 · answered Feb 03 '17 at 13:14

0

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 )

answered Feb 03 '17 at 13:14

MT0

143,790
11
59
117

Using functions in the `WHERE` clause can mean that a function-based index (or no index) is used instead of the column index. With this version there are no functions and the column indexes should be used (if present) - the OP can run an `EXPLAIN PLAN` on the two queries and see if there is a difference in the indexes used. – MT0 Feb 03 '17 at 13:20
I would think the Oracle optimizer would look through `coalesce`/`nvl` and expand it before handing it over to the optimizer. – Klas Lindbäck Feb 03 '17 at 13:31
this will take a huge performance hit. Dynamic SQL or table functions seems to be the only way for multiple parameters – Tejas Dec 12 '18 at 16:02

score 0 · Answer 3 · edited May 23 '17 at 12:00

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.

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

resolved:

3 Answers3