oracle query : Same filter condition on different column

Question

Im working on the query to improve the performance.

i see query having same filter condition on different column. just think what is the better way of writing the query.

select *
from table 1 
where 
col1 in (select filter_value from filtertable where id=1)
or 
col2 in (select filter_value from filtertable where id=1)
or 
col3 in (select filter_value from filtertable where id=1)
or 
col4 in (select filter_value from filtertable where id=1)
or 
col5 in (select filter_value from filtertable where id=1)
or 
col6 in (select filter_value from filtertable where id=1)
or 
col7 in (select filter_value from filtertable where id=1)
or 
col8 in (select filter_value from filtertable where id=1)
....... Same condition till col15

i tried replacing filtertable using WITH CLAUSE but not much help

with filter_temp
(select /*+ materialize  */ filter_value from filtertable where id=1)
select *from table 1 , filter_temp
where col1 in (filtertable.filter_value)
or 
col2 in (filtertable.filter_value)
or 
col3 in (filtertable.filter_value)
or 
col4 in (filtertable.filter_value)
or 
col5 in (filtertable.filter_value)
or 
col6 in (filtertable.filter_value)
or 
col7 in (filtertable.filter_value)
or 
col8 in (filtertable.filter_value)
....... Same condition till col15

is there any different way of writing this query.

Probably no way to fix the query. You can fix the data model, so you don't have 15 columns containing an "array" of values. Instead, you want a junction/association table with one row per value. — Gordon Linoff
– Gordon Linoff, Commented Mar 18, 2018 at 15:35
What is the current performance of your query and what improvements might you expect? I’ll second the opinion that based on what I see, you probably can’t. Your table / data model may prohibit it. — unleashed
– unleashed, Commented Mar 19, 2018 at 14:39

Gordon Linoff · Accepted Answer · 2018-03-18 15:43:32Z

1

A shorter way of writing the query uses exists:

select t1.*
from table1 t1
where exists (select 1
              from filtertable ft
              where ft.id = 1 and
                    ft.filter_value in (t1.col1, t1.col2, . . ., t1.col15)
             );

The performance should be pretty similar to your longer version, but this is at least more concise. I consider that "better" in a way.

The real solution is to have a junction table, so you don't have the column values in a single column on multiple rows rather than stored across different columns in one row.

answered Mar 18, 2018 at 15:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Laks Over a year ago

Thanks Gordon Linoff. i was assuming that if i use WITH CLAUSE, it eliminate multiple reading of Filtertable but it did't help.

Collectives™ on Stack Overflow

oracle query : Same filter condition on different column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related