Re: How to avoid full table access

Hello Robert,

thank you for your help.
I've done it with unions

Thomas

"Robert Klemme" <shortcutter_at_googlemail.com> schrieb im Newsbeitrag news:5t50otF1ccj9kU1_at_mid.individual.net...
> 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 - 15:56:37 CST