CODEWITHSUNDEEP
oracle-databaseselectjoinplsql
fejesjoco
fejesjoco

Reputation: 11903

Oracle conditional join

TL;DR: I want to eliminate an entire join+subselect if a parameter says so.

Let's say I have a query in a stored procedure like this:

open cur_result for
select t1.* from table1 t1
join (select key, value, row_number() over (partition by key order by whatever) rn from table2) t2
on t1.key = t2.key and t2.rn = 1
where [...lots of things...]
and t2.value = 'something'

You see, I don't just join table1 to table2, I need to join only the first records of table2 based on some criteria, hence the row_number calculation in the subquery and rn=1 additional join condition. Anyway, the point is that this is an expensive subquery, and I want to filter based on this subquery.

My goal is that this subquery should be conditional, based on an additional parameter. If I want to repeat everything, here's how it looks:

if should_filter_table2 = 1 then
  [the above query is copied here completely]
else
  open cur_result for
  select t1.* from table1 t1
  -- no table2 join
  where [...lots of things...]
  -- no table2 condition
end if;

The problem is that there can be many such parameters and so many branches of almost the same SQL which looks ugly and is hard to maintain. I could also do this:

open cur_result for
select t1.* from table1 t1
join (select key, value, row_number() over (partition by key order by whatever) rn from table2) t2
on t1.key = t2.key and t2.rn = 1
where [...lots of things...]
and (should_filter_table2 = 0 or t2.value = 'something')

This is easy to maintain, but if the parameter says that the subquery doesn't matter, it is still executed unconditionally. Based on my experiences, Oracle can't optimize this and this is a huge performance hit.

So the question: can you do this in 1 query AND with good performance? Something like this:

open cur_result for
select t1.* from table1 t1
join {case when should_filter_table2 = 1 then (select key, value, row_number() over (partition by key order by whatever) rn from table2) else [empty table] end} t2
on t1.key = t2.key and t2.rn = 1
where [...lots of things...]
and (should_filter_table2 = 0 or t2.value = 'something')

So if should_filter_table2 is 0, the subquery shouldn't be calculated and the filter shouldn't be applied at all.

Dynamic SQL should be avoided. I suspect how to do this in dynamic SQL, but it raises the same maintainability issue.

Upvotes: 2

Views: 2595

Answers (1)

René Nyffenegger
René Nyffenegger

Reputation: 40603

I am not 100% sure if the optimizer does what I think it should but I'd probably start with something like the following. Unfortunately, I don't have test data at hand to simulate long running queries.

select t1.* from table1 t1
  where 
    (should_filter_table2 = 0 or (
        (t1.key, 'something', 1) in (
             select key, value, row_number() over 
                               (partition by key order by whatever) rn 
               from table2) 
        )
    )
  and [...lots of things...]

Upvotes: 1

Related Questions