Re: How to get query to use an index

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_at_gmail.com> wrote:
>
> 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
>
>
>

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