Dynamic cursor using variables with null values in PL\SQL

Question

Can anyone could help me with this issue, please? I have a cursor defined like this:

 CURSOR c_cursorName (
      paramA   IN VARCHAR2,
      paramB   IN VARCHAR2)
   IS
      SELECT tableA.id, tableA.name
        FROM tableA
       WHERE     tableA.fieldA = paramA
             AND (paramB IS NULL OR tableA.fieldB = paramB);

Why I'm doing this? Because I want to reuse this cursor in two different cases:

when I pass a paramB value to return all the rows in tableA where fieldA is equal to paramA value and fieldB is equal to paramB;
When I don't pass any value in paramB (null value), and I want to return all the rows in tableA where fieldA is simply equal to paramA (and I don't care about fieldB);

The problem: When I pass null in paramB, my explain plan increase dramatically and I have performance issues.

The easy way to do this is creating two different cursors, one for each case. But I'm trying to find a good and clever solution without the need of creating a new index or a new cursor.

Any suggestions?

Boneist · Accepted Answer · 2016-05-18 15:36:05Z

3

You could try:

cursor c_cursorname (parama in varchar2,
                     paramb in varchar2)
is
with results as (select tablea.id,
                        tablea.name,
                        tablea.fieldb
                 from   tablea
                 where  tablea.fielda = parama)
select id, name
from   results
where  paramb is null
union all
select id, name
from   results
where  fieldb = paramb;

Oracle should be able to short-circuit which of the two queries in the union all is run, based on the value of paramb.

edited May 18, 2016 at 15:36

answered May 18, 2016 at 15:21

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marmite Bomber · Accepted Answer · 2016-05-18 16:47:27Z

A slightly modified solution based on the "exclusive union all" approach from @Boniest:

I do not use a subquery to avoid possible materialization of it and I add explicit check on the NULL / NOT NULL of the paramb to perform always only one part of the UNION ALL.

 SELECT tableA.id, tableA.name
        FROM tableA
       WHERE     tableA.fieldA = :parama
             AND  :paramb IS NULL 
UNION ALL
 SELECT tableA.id, tableA.name
        FROM tableA
       WHERE     tableA.fieldA = :parama
             AND :paramb IS NOT NULL 
             AND fieldb = :paramb

I assume an index on tableA (fieldA, fieldB).

This leads to the execution plan below.

For paramb is NULL index range scan on fieldA is done,

for paramb is NOT NULL index range scan on fieldA, fieldB is done.

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |   101 |  4962 |     5  (40)| 00:00:01 |
|   1 |  UNION-ALL                    |             |       |       |            |          |
|*  2 |   FILTER                      |             |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| TABLEA      |   100 |  4900 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | TABLEA_IDX1 |    40 |       |     2   (0)| 00:00:01 |
|*  5 |   FILTER                      |             |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| TABLEA      |     1 |    62 |     2   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | TABLEA_IDX1 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1
   2 - SEL$1
   3 - SEL$1 / TABLEA@SEL$1
   4 - SEL$1 / TABLEA@SEL$1
   5 - SEL$2
   6 - SEL$2 / TABLEA@SEL$2
   7 - SEL$2 / TABLEA@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:PARAMB IS NULL)
   4 - access("TABLEA"."FIELDA"=TO_NUMBER(:PARAMA))
   5 - filter(:PARAMB IS NOT NULL)
   7 - access("TABLEA"."FIELDA"=TO_NUMBER(:PARAMA) AND "FIELDB"=TO_NUMBER(:PARAMB))

Both solutions are great! But I have one performance issue because I need to play with indexes in the REAL tables here (tableA don't exists, of course). Thanks!

Collectives™ on Stack Overflow

Dynamic cursor using variables with null values in PL\SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related