index for case statements

Database
Highlighted
Teradata Employee

index for case statements

Hi All 

I have a query somewhat as below 

 

 

sel distinct ref.val,
    case

            when ref.val = 'A' then stg.col1
            when ref.val = 'B' then stg.col2
    end
from

     databsae.stg_table stg
join database.ref_table ref
on ref.val in ('A','B')

;

 

As my stg_table has more than 100 million records , how can I improve the performance of such case statements.

I have given example of 2 values ('A','B') but it will go as far as 'J' around for around 10 values each will fetch different columns from STG_TABLE.

Also more join conditions and columns will get added in it as query will develop.

Please suggest what kind of index i can create to improve performance in such case.