Re: How to avoid full table access

Hello Robert,
thank you for your answer.

It does not make a difference, when trying it without a star. select c1,c2 from customers where nr like '1%' or name like 'A%'.

Unions work and are pretty fast (10 times I think), but they are difficult to use in our application, especially because the order by clause only excepts alias names.
Our database is 10.2.0.3.0 on Windows.

Any proposal with one Select?

Thanks
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 - 13:34:21 CST