SQL Performance (MOSC)

MOSC Banner

Below query runs for 10 mins, is there any index to be added

edited May 12, 2016 5:03AM in SQL Performance (MOSC) 3 commentsAnswered

SELECT * FROM (

                        SELECT a.*, ROWNUM rnum

                        FROM (SELECT   DISTINCT A1.OBJECT_ID as REF_OBJECT_ID FROM LNP_OBJECT_3203614968 A1

LEFT JOIN UPS_OBJECT_LINK X ON A1.OBJECT_ID = X.REF_OBJECT_ID  WHERE  EXISTS (

                                SELECT 1 FROM LNP_ACTIVITY_ATTR_VALUE_NUM ZZ3 WHERE ZZ3.OBJECT_ID = X.OBJECT_ID AND ZZ3.PROJECT_ID = 1000015817 AND ZZ3.ATTR_ID = 3000765159 AND ZZ3.LATEST=1 AND  ZZ3.ATTR_VALUE = 3002473529    )  AND   EXISTS (

                               SELECT 1 FROM UPS_OBJECT_ATTR_VALUE_NUM YY3 WHERE  YY3.OBJECT_ID = A1.OBJECT_ID AND YY3.ATTR_ID = 1000001014  AND  YY3.ATTR_VALUE = 1000030437    )  AND   (

EXISTS  (SELECT 1 FROM UPS_LIST_OBJECT B1 WHERE B1.OBJECT_ID = A1.OBJECT_ID AND B1.LIST_OBJECT_ID IN ( 3203622034,3203686296,3203735360,3203763306,3203763310,3203795633,3203796481,3203796601,3203806691,3203852971,3203869182,3203889203 ) )  )    AND NOT EXISTS  (SELECT 1 FROM UPS_OBJECT_INACTIVE B2 WHERE B2.OBJECT_ID = A1.OBJECT_ID) AND NOT EXISTS(

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center