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 endfrom
databsae.stg_table stgjoin 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.