Re: How to get query to use an index
- From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- To: "Michael Garfield Sørensen, CeDeT" <mgs@xxxxxxxx>
- Date: Fri, 14 Apr 2006 18:53:45 +0300
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
