Re: How to get query to use an index

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Fri, 14 Apr 2006 18:53:45 +0300
Message-ID: <6e49b6d00604140853g12fc42edh@mail.gmail.com>

2006/4/13, Michael Garfield Sørensen, CeDeT <mgs_at_cedet.dk>:

>


> 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

Received on Fri Apr 14 2006 - 10:53:45 CDT