SQL Performance (MOSC)

MOSC Banner

SQL Query is very slow due to "or exists" clause

edited Nov 12, 2015 9:42AM in SQL Performance (MOSC) 3 commentsAnswered

The following query is a very slow performing and I believe it's due to the "or exists" clause.  How can I rewrite the query to get rid of the "or exists" clause so the CBO picks a better explain plan?

Oracle 11.2.0.4

SELECT H.ID

FROM    OKC_K_HEADERS_ALL_B H ,

        OKC_STATUSES_V S

WHERE   S.CODE          = H.STS_CODE

        AND S.STE_CODE IN ('ACTIVE', 'SIGNED', 'EXPIRED', 'CANCELLED', 'TERMINATED')

        AND

        (

                H.LAST_UPDATE_DATE BETWEEN NVL(:B2 , H.LAST_UPDATE_DATE) AND NVL(:B1 , H.LAST_UPDATE_DATE)

                OR EXISTS

                (

                        SELECT 'x'

                        FROM    OKC_K_LINES_B L

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