Re: How to get query to use an index
- From: "Vlad Sadilovskiy" <vlovsky@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 16 Apr 2006 14:04:16 -0400
You cannot substitute union/union all for "or". If both conditions satisfied
for a single tuple than union all would bring duplicates, that you wouldn't
see in the original results. Union on the other hand could eliminate
legitimate duplicates. Modified union all would indeed help. But what good
does it make from the performance prospective? The table would be scanned
twice if CBO chooses full table access path.
While using union in this case increases chances of getting indexed access,
it doesn't completely eliminate the opposite. I'm not sure if 10g has
different defaults but in 9i the target cardinality of a result set with
like predicate evaluates to 5% of the table cardinality. The other "OR"ed
like would simply adds another 5%. That's why CBO could chose full table
scan.
If you see 10053 trace, all the necessary info would be there.
To satisfy the query Oracle at least should full scan both indexes, then
concatenate the results and then access the table. For best results, use
concatenated index as proposed. Otherwise, I see few good solutions. You
might try to use sub query and tweak cardinality like this. But be advised
that this will screw the final cardinality of the query, and subsequently
the choice of the CBO. So, in cases when it would really be good to have FTS
you wouldn't get it.
FROM
cus_current_row cus
where
cushoph in
(select /*+ cardinality(1) */
cushoph
from
cus_current_row
where cushoph LIKE SYS_CONTEXT ('MY_CTX_wpaods', 'phone'))
or cus01wrph in (select /*+ cardinality(1) */
cus01wrph
from
cus_current_row
where cus01wrph LIKE SYS_CONTEXT ('MY_CTX_wpaods', 'phone'))
In any case, the fix would depend on the level of control over the
application you are using.
Thoughts?
- Vladimir
On 4/14/06, Gints Plivna <gints.plivna@xxxxxxxxx> wrote:
>
> 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:
