Re: How to avoid full table access

On 22.12.2007 18:03, Thomas Koester wrote:
> is it possible to avoid a full table access when selecting records with Like
> and or.
>
> Example:
> select * from customers where nr like '1%' or name like 'A%'
>
> both colums are indexes and analyzed. Oracle takes the indexes when
> searching for each value alone

Did you try this without the star? You should generally always query columns explicitly.

Btw, you also did not mention any version and OS.

How about a union?

select c1, c2...

   from customers
  where nr like '1%'

union all

select c1, c2...

   from customers
  where name like 'A%'

Note: if some columns fall in both categories you should rather use "union" instead of "union all". Alternatively you could experiment with a three tiered "union all" but I doubt whether this is efficient.

select c1, c2...

   from customers
  where nr like '1%'
    and name not like 'A%'

union all

select c1, c2...

   from customers
  where name like 'A%'
    and nr not like '1%'

union all

select c1, c2...

   from customers
  where name like 'A%'
    and nr like '1%'

Generally the question is whether these approaches are faster.

Kind regards

        robert Received on Sat Dec 22 2007 - 11:44:56 CST