CODEWITHSUNDEEP
sqloracle-databasedistinct
RealMan
RealMan

Reputation: 129

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;

Upvotes: 0

Views: 46

Answers (2)

Popeye
Popeye

Reputation: 35920

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!!

Upvotes: 1

Arif Sher Khan
Arif Sher Khan

Reputation: 585

You can't use exists here, because you are using more than 1 table columns in select. And I understand you should use standard join keyword when joining tables.

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

I might be wrong in left/right as I am not much frequent with (+).

Hope this helps.

Upvotes: 0

Related Questions