Re: UNION or OR in query

On May 2, 5:06 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> 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.

You may want to consider normalizing the data and creating a separate table to store the claim diagnoses. This would likely improve any performance issues you may be having in regards to this type of query. claim_id, diag_type (diag_i vs diag_dtl), rank, diag_code. then it would be a straight query:

    SELECT DISTINCT claim.mbr_key,
                    claim.mbr_sys_key,
                    claim.person_id,
                    claim.srv_dt_from
               FROM claim.v_claim claim, claim_diagnoses diag
              WHERE claim.srv_dt_from >= p_date
                AND claim.person_id > 0
                AND claim.claim_id = diag.claim_id
                AND (diag_type = 'DIAG_DTL' or (diag_type = 'DIAG_I'
and rank <= 2 ))
                AND substr(diag_code,1,3) IN ('491', '492', '496')