Indexing for query using CASE for join

indexoptimizationoracleoracle-10g

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?

Best Answer

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
Related Question