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?