Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Index is used if where conditions includes IS NULL or IS NOT NULL ?

teradata0802Jun 18 2015 — edited Jun 24 2015

We use Oracle 11gR2 on win2008R2.

We wrote query like below in where phrase.

(COALESCE(:p_site, NULL) IS NULL)

      OR (

        COALESCE(:p_site, NULL) IS NOT NULL

        AND EXISTS (

          SELECT

            *

          FROM

            MART.REPORTERS

            INNER JOIN MART.REPORTER_TYPE

              ON REPORTER_TYPE.RPTR_TYPE_ID = REPORTERS.REPORTER_TYPE

          WHERE

            REPORTERS.CASE_ID = RPT_CASE.CASE_ID

            AND REPORTER_TYPE.E2B_CODE IN (1, 2, 3)

            AND REPORTERS.INSTITUTION_J IN (:p_site)

        )

      )

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 22 2015
Added on Jun 18 2015
15 comments
2,687 views