1

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?

7
  • 3
    Have you tried OR? :(setting = 'A' and value = column1) or (setting = 'B' and value = column2) Or maybe union all
    – vercelli
    Commented Aug 17, 2016 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
    Commented Aug 17, 2016 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? Commented Aug 18, 2016 at 15:39
  • Can you provide execution plan for this SQL?
    – atokpas
    Commented Aug 18, 2016 at 15:41
  • 1
    @Vercelli, I ended up going with your suggestion. Add an answer with the same info, please.
    – Jaaz Cole
    Commented Aug 19, 2016 at 15:44

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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