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');
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 ;)
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 :-)