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:

    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?

  • 3
    Have you tried OR? :(setting = 'A' and value = column1) or (setting = 'B' and value = column2) Or maybe union all – vercelli Aug 17 '16 at 16:26
  • 1
    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. – a1ex07 Aug 17 '16 at 17:29
  • Wait, you want the optimiser to guess in advance which indexes it will need based on the outcome of your case statement? – Andrew Brennan Aug 18 '16 at 15:39
  • Can you provide execution plan for this SQL? – JSapkota Aug 18 '16 at 15:41
  • 1
    @Vercelli, I ended up going with your suggestion. Add an answer with the same info, please. – Jaaz Cole Aug 19 '16 at 15:44

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.