UNION or OR in query

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Thu, 03 May 2007 00:06:25 GMT
Message-ID: <5I9_h.20723$Um6.19226@newssvr12.news.prodigy.net>

In the book "Oracle SQL Tuning" by Mark Gurry there is a section on pages 63-64 about using a UNION instead of an OR. The example given differs from some code I've been looking at but I was wondering if the principle still applied.

Would the second query below be more efficient like the examples in the book above implied?

Example in book:

select ...

   from ps_jrnl_header a
 where jrnl_hdr_status = 'E'
 OR exists

    ( select ... )

select ...

   from ps_jrnl_header a
 where jrnl_hdr_status = 'E'
UNION
  select ...

Existing query in my code:

    SELECT DISTINCT mbr_key,

                    mbr_sys_key,
                    person_id,
                    srv_dt_from
               FROM claim.v_claim
              WHERE srv_dt_from >= p_date
                AND person_id > 0
                AND (
                         diag_dtl LIKE '491%' OR diag_dtl LIKE '492%' OR
diag_dtl LIKE '496%'
                      OR diag_i_1 LIKE '491%' OR diag_i_1 LIKE '492%' OR
diag_i_1 LIKE '496%'
                      OR diag_i_2 LIKE '491%' OR diag_i_2 LIKE '492%' OR
diag_i_2 LIKE '496%'
                     );

Modified query using UNION:

    SELECT DISTINCT mbr_key,

                    mbr_sys_key,
                    person_id,
                    srv_dt_from
               FROM claim.v_claim
              WHERE srv_dt_from >= p_date
                AND person_id > 0
                AND diag_dtl LIKE '491%' OR diag_dtl LIKE '492%' OR diag_dtl

LIKE '496%'
    UNION

      SELECT DISTINCT mbr_key,
                      mbr_sys_key,
                      person_id,
                      srv_dt_from
                 FROM claim.v_claim
                WHERE srv_dt_from >= p_date
                  AND person_id > 0
                  AND diag_i_1 LIKE '491%' OR diag_i_1 LIKE '492%' OR

diag_i_1 LIKE '496%'

    UNION

      SELECT DISTINCT mbr_key,
                      mbr_sys_key,
                      person_id,
                      srv_dt_from
                 FROM claim.v_claim
                WHERE srv_dt_from >= p_date
                  AND person_id > 0
                  AND diag_i_2 LIKE '491%' OR diag_i_2 LIKE '492%' OR

diag_i_2 LIKE '496%';

Thanks. Received on Wed May 02 2007 - 19:06:25 CDT