CODEWITHSUNDEEP
oracle-databaseselectviewunion-all
Symonds
Symonds

Reputation: 194

Using Union All performance degrades in Oracle View

I have created below oracle view where i have added select query in union all which is working fine with expected result but suddenly the performance of the view got very slow. The table IS_ID contains 510000 records.

I really dont understand as by adding this UNION ALL select query just added 400 extra rows in the view but still why the performance got very slow now. The main cause of slow performance is the below statement in union all which is blocking the view. Can i distribute union all or the below statement of IS_TRE view into different view to improve performance or how can i rewrite the below statement to improve performance ?

WHERE
        FUND_ISIN NOT IN
                         (
                         SELECT DISTINCT
                             FUND_ISIN
                         FROM
                             IS_ID
                         WHERE
                             MEMBER_DESCR ='O')

Upvotes: 0

Views: 703

Answers (2)

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1606

you can try NOT EXISTS and see if that helps

FROM
    IS_ID OUT_Q
    WHERE
    FUND_ISIN NOT EXISTS
                     (
                     SELECT 'X' FROM IS_ID IN_Q
                     WHERE MEMBER_IN_Q.DESCR ='O'  AND OUT_Q.COLUMNS_MAKING_UP_THE_UNIQE_KEY
                          = IN_Q.COLUMNS_MAKING_UP_THE_UNIQE_KEY
)

Upvotes: 1

Andrew Sayer
Andrew Sayer

Reputation: 2336

I've given you a few comments about why there is so much more going on that you are sharing. But here's a freebee, in general

SELECT 
    FUND_ISIN,    
    MAX(FUND_QUOTE_CRNY),    
    'O' AS MEMBER_DESCR,
    100 - SUM(MEMBER_RATIO),
    'Other total'
FROM
    IS_ID 
    WHERE
    FUND_ISIN NOT IN
                     (
                     SELECT DISTINCT
                         FUND_ISIN
                     FROM
                         IS_ID
                     WHERE
                         MEMBER_DESCR ='O')
GROUP BY
    FUND_ISIN;

Can be rewritten to

SELECT 
    FUND_ISIN,    
    MAX(FUND_QUOTE_CRNY),    
    'O' AS MEMBER_DESCR,
    100 - SUM(MEMBER_RATIO),
    'Other total'
FROM
    IS_ID
GROUP BY
    FUND_ISIN
HAVING sum(case when MEMBER_DESCR ='O' then 1 else 0 end) = 0

And it will probably perform better.

Whether or not that makes a difference in your actual scenario is unknown.

Upvotes: 1

Related Questions