"Select case" performance issue

Question

I am facing performance issue in below mentioned SQL query :

select column1, 
   (SELECT CASE fl.ISDELETED WHEN 'T' THEN 'T' ELSE 'F' END  FROM FILE fl
     WHERE lower(fl.FILE_TYPE) LIKE 'doc' AND fl.FILE_ID=doc.FILE_ID   ) AS alias1 ,
   (SELECT CASE fl.ISDELETED  WHEN 'T'  THEN 'T' ELSE 'F'  END FROM FILE fl
     WHERE lower(fl.FILE_TYPE) LIKE 'xls'   AND fl.FILE_ID=doc.FILE_ID ) AS alias2

FROM DOC doc

Is there any alternative(JOIN or any other option) of select case statement so that it will take less time to execute? I am calling this query from java code

Any kind of help would be appreciated

Answer 1

You can write this with an explicit join:

select d.column1,
       (case when f.isdeleted = 'T' and lower(f.file_type) = 'doc' then 'T'
             when lower(f.file_type) = 'doc' then 'F'
        end) as alias1,
       (case when f.isdeleted = 'T' and lower(f.file_type) = 'xls' then 'T'
             when lower(f.file_type) = 'doc' then 'F'
        end) as alias2             
from doc d left join
     file f
     on f.file_id = d.file_id;

For performance, you want indexes on file(file_id, file_type, isdeleted).