optimization select distinct query

How can i optimize this query, how to rewrite a request through exists:

select DISTINCT 
        p.SBJ_ID, 
    nvl(l.ATTR,c.CODE) as ATTR, 
        l.VALUE
from T_TRFPRMLST p,
       T_CMSATTR_LINK l, 
       C_SBJCONCEPT c
where l.SBJ_ID(+) = p.SBJ_ID
  and p.sbj_id = c.ID;

Answer

Please use ANSI style join syntax, first of all.

Now, Coming to your question, according to my knowledge NVL perform worse when working with large data sets.

So how can we achieve the same functionality? — We can use DECODE or CASE WHEN.

Among these two also, CASE WHEN will be better when it comes to performance.

Compare the execution plan of query mentioned in your question and the execution plan of the following query and you will definitely find the difference.

SELECT DISTINCT
    P.SBJ_ID,
    CASE
        WHEN L.ATTR IS NOT NULL THEN L.ATTR
        ELSE C.CODE
    END AS ATTR,
    L.VALUE
FROM
    T_TRFPRMLST P
    JOIN C_SBJCONCEPT C ON ( P.SBJ_ID = C.ID )
    LEFT JOIN T_CMSATTR_LINK L ON ( P.SBJ_ID = L.SBJ_ID);

Please make sure that PKs and FKs are properly created and proper indexes are also available as indexes are created mainly for performance.

Cheers!!