CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Laks
Laks

Reputation: 85

oracle query : Same filter condition on different column

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.

Upvotes: 0

Views: 417

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 1

Related Questions