Indexing for query using CASE for join

Question

I'm trying to optimize a query on Oracle 10g (I know!), and I have a 4 column table which contains varchar2 values similar to this structure:

CREATE TABLE Settings (
 Client VARCAR2(50)
 , Class VARCHAR2(50)
 , Setting VARCHAR2(50)
 , Setting_Value VARCHAR(20)
)

The query is meant to use static values for Client and Class, these have been hardcoded, while Setting varies in two sections, and Setting_Value should join to different columns in another table, depending on the value of Setting.

I've been setting up the join using this structure:

Edit: It appears there's been confusion. The Setting column effectively points to the column name of the other_table. As such, Settings records act as filters for a given Client/Class operation. As such, adding Setting to the other_table is counterproductive.

WHERE Settings.SettingValue =
 CASE Settings.Setting
 WHEN 'Column1' THEN other_table.Column1
 WHEN 'Column2' THEN other_table.Column2
 WHEN 'Column3' THEN other_table.Column3
 ...

The problem is that I can't get Oracle 10g to hit any indexes on other_table for the values in the CASE statement. Is there a strategy I'm missing or a structure that would work for Oracle 10g to use an index as opposed to a full table scan?

• vercelli: Have you tried OR? :(setting = 'A' and value = column1) or (setting = 'B' and value = column2) Or maybe union all
• a1ex07: If you can add Setting column to other_table and copy its value there then you will be able to create a function based index which will be used efficiently.
• Andrew Brennan: Wait, you want the optimiser to guess in advance which indexes it will need based on the outcome of your case statement?
• atokpas: Can you provide execution plan for this SQL?
• Jaaz Cole: @Vercelli, I ended up going with your suggestion. Add an answer with the same info, please.

Answer 1

You should probably try with OR like:

select * 
 from myTable join otherTable
on (setting = 'A' and value = column1) or 
 (setting = 'B' and value = column2)

Also UNION ALL may work for you

select * from myTable join otherTable on setting = 'A' and value = column1
union all
select * from myTable join otherTable on setting = 'B' and value = column2