Re: How to get query to use an index

2006/4/13, Michael Garfield Sørensen, CeDeT <mgs@xxxxxxxx>:
>
> SQL> REM To the best of my knowledge, the
> SQL> REM modified UNION-ALL-version is
> SQL> REM equivalent to the OR-version if
> SQL> REM (and only if) you rule out NULLs

Third statement below probably is useful even if c1 and/or c2 is null.
Additionally to test nulls I'll insert another two rows:
insert into mgsx values ('x', null);
insert into mgsx values (null, 'x');
-- original statement ith OR
SQL> select * from mgsx where c1 like 'x%' or c2 like 'x%';

C1         C2
---------- ----------
x          y
x          x
y          x
x          x
x
           x

6 rows selected.

-- modified UNION ALL by Michael
SQL> select * from mgsx where c1 like 'x%'
  2    union all
  3   select * from mgsx where c2 like 'x%'
  4  and c1 not like 'x%';

C1         C2
---------- ----------
x          y
x          x
x          x
x
y          x

5 rows selected.

-- another modified UNION ALL version that works with nulls at least
-- that far as above inserted and can easily use indexes on c1, c2.
SQL> select c1, c2 from (
  2  select mgsx.*, case when c2 like 'x%' then 0 else 1 end flag from
mgsx where c1 like 'x%'
  3   union all
  4  select mgsx.*, 1 flag from mgsx where c2 like 'x%')
  5  where flag = 1
  6  /

C1         C2
---------- ----------
x          y
x
x          x
y          x
x          x
           x

6 rows selected.

Gints
--
http://www.freelists.org/webpage/oracle-l


Other related posts: