Skip to Main Content
  • Questions
  • Where clause with multiple arguments which can be null or populated

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, marc.

Asked: January 11, 2017 - 6:35 pm UTC

Last updated: January 13, 2017 - 6:38 am UTC

Version: 11g

Viewed 1000+ times

You Asked

I have a search feature in my application where a user can type filter on 3 different columns(can be more), each of these filters can be used or null. What is the best way to select a ref cursor for the data? I can think of 2 different ways. 1st way is a case statement for each combination of filters. Second is a where clause with many or's. Do you have any preferable way? I can see the case statement would be better performance, but can be ugly to code case would be exponential to the amount of filters.
CREATE TABLE test_tbl(data   VARCHAR2(10),
                      col1   VARCHAR2(10),
                      col2   VARCHAR2(10),
                      col3   VARCHAR2(10));
CREATE OR REPLACE PROCEDURE test(p1  VARCHAR2,
                                 p2  VARCHAR2,
                                 p3  VARCHAR2) AS
    rc   SYS_REFCURSOR;
BEGIN
    CASE
        WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL THEN
            OPEN rc FOR
                SELECT data
                  FROM test_tbl
                 WHERE col1 = p1 AND col2 IS NULL AND col3 IS NULL;
        WHEN p1 IS NULL AND p2 IS NOT NULL AND p3 IS NULL THEN
            OPEN rc FOR
                SELECT data
                  FROM test_tbl
                 WHERE col1 IS NULL AND col2 = p2 AND col3 IS NULL;
    --when the next 7 combinations...
    --when the next 7 combinations...
    --when the next 7 combinations...
    END CASE;
END;
/

OR

CREATE OR REPLACE PROCEDURE test(p1  VARCHAR2,
                                 p2  VARCHAR2,
                                 p3  VARCHAR2) AS
    rc   SYS_REFCURSOR;
BEGIN
    OPEN rc FOR
        SELECT data
          FROM test_tbl
         WHERE     ((p1 IS NOT NULL AND col1 = p1) OR (col1 IS NULL AND p1 IS NULL))
               AND ((p2 IS NOT NULL AND col2 = p2) OR (col2 IS NULL AND p1 IS NULL))
               AND ((p3 IS NOT NULL AND col3 = p3) OR (col3 IS NULL AND p1 IS NULL));
END;
/

and Connor said...

They dont have to be mutually exclusive. I'm a fan of having the "best" SQL for high high use cases, and fall through to a general one as a catch-all.

For example, lets say the most common usage you expect (and of course, you would capture usage metrics in your app to adjust this over time) is: p1,p2 provided, p3,p4 null.

Then I would have something like:

CASE
WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NULL and p4 is null THEN
     open rc for 
         ...
ELSE
OPEN rc FOR
        SELECT data
          FROM test_tbl
         WHERE     ((p1 IS NOT NULL AND col1 = p1) OR (col1 IS NULL AND p1 IS NULL))
               AND ((p2 IS NOT NULL AND col2 = p2) OR (col2 IS NULL AND p1 IS NULL))
               AND ((p3 IS NOT NULL AND col3 = p3) OR (col3 IS NULL AND p1 IS NULL));
   


So code up a specific SQL for the 'n' most common use cases, and then fall through to the general case if it doesnt match that. That strikes are balance between giving the optimizer the best chance, and not maintaining 100's of permtutations.

Another option would be to build the SQL dynmaically...but that's more complexity to negotiate (aka sql injection, variable number of binds etc)

Hope this helps.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Or

Gh, January 12, 2017 - 5:32 am UTC

In your procedure loop on the not null columns to build a union all query.
Initially qry = select data from T where 1=2
Then loop and concatenate Union All Select data from T where concat ('col', i) = concat p I .....


Other approaches

Praveen Ray, January 12, 2017 - 9:23 am UTC


APPROACH - 1
CREATE OR REPLACE PROCEDURE test(p1 VARCHAR2 DEFAULT 'XXX',
p2 VARCHAR2 DEFAULT 'YYY',
p3 VARCHAR2 DEFAULT 'ZZZ') AS
SELECT data FROM test_tbl WHERE NVL(col1, 'XXX') = p1 AND NVL(col2,'YYY') = p2 AND NVL(col3,'ZZZ') = p3;


APPROACH - 2
SELECT data FROM test_tbl WHERE NVL(col1, 'XXX') = NVL(p1,'XXX') AND NVL(col2,'YYY') = NVL(p2,'YYY') AND NVL(col3,'ZZZ') = NVL(p3,'ZZZ');

Connor McDonald
January 13, 2017 - 1:46 am UTC

Yes you could - but there is a strong likelihood you'll get the suboptimal performance with all those NVL's

A reader, January 12, 2017 - 12:17 pm UTC

Thank you, my team does it all 3 ways. Each way has a pro and con as you summarized it the same as I should have.

Thank you

Performancewise

Praveen Ray, January 13, 2017 - 5:42 am UTC

Performance impact? Agreed, but again it depends on the nature of the data and several other factors, say for example, table is very big and fetching just few records? still we have options to fix that by creating f-index and doing FFS etc.

whatif the table is very very small but the code looks ugly ;)
Connor McDonald
January 13, 2017 - 6:38 am UTC

My point was this:

if you have:

and col = nvl(:bind,col)

then we can do an optimization which is roughly equivalent to

col = :bind and :bind is not null
union all
col = col and :bind is null

so we might pick up indexes on COL. But if there's multiple ones in a query, then we'll only do that with ONE of them.

Whereas a custom SQL which is picked out certain columns of common usage etc, can take advantage of composite indexes etc. "Generic" can be a four-letter word when it comes to query performance :-)

100% True

Praveen Ray, January 13, 2017 - 11:03 am UTC

"Generic" can be a four-letter word when it comes to query performance :-)

that's what the first entered to my mind when performance came in to the picture; superb :-)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library