Re: UNION or OR in query

"Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message news:5I9_h.20723$Um6.19226_at_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.
>
>

It is not possible to give a generic answer to your question; it depends a great deal on the features available to your version of the optimizer (for example: the level to which it can do complex view merging and predicate pushing).

It is, however, possible that the separation of the three clauses

> diag_dtl LIKE '491%' OR diag_dtl LIKE '492%' OR diag_dtl LIKE '496%'
> diag_i_1 LIKE '491%' OR diag_i_1 LIKE '492%' OR diag_i_1 LIKE '496%'
> diag_i_2 LIKE '491%' OR diag_i_2 LIKE '492%' OR diag_i_2 LIKE '496%'

will allow the optimizer to find an efficient access path for each column (say INLIST ITERATOR) where it couldn't find a concatenation when you leave all nine clauses in the same query block.

The DISTINCT's are redundant, by the way. The presence of the UNION will make Oracle do a distinct automatically. Depending on your version of Oracle, you may find that the present of the DISTINCT's actually adds an extra sort unique to the workload.

I haven't read the book you mention, but given the example, it's possbly more interested in the case of allowing subqueries to unnest - as in

    http://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or/

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html