ONLINE V7.20 : Query Optimizer :BIG PROBLEM With LIKE 'AA%'
No indexes ?
sqexplain.out:
QUERY:
------
select number,name
from persons
where name like 'Alex%'
Estimated Cost: 2
Estimated # of Rows Returned: 4
1) persons: INDEX PATH
(1) Index Keys: name person_type number (Key-Only)
Lower Index Filter: persons.name LIKE 'Alex%'
> -----Original Message-----
> From: Incotec [SMTP:incotec@incotec.fr]
> Posted At: Friday, October 17, 1997 6:50 PM
> Posted To: informix
> Conversation: ONLINE V7.20 : Query Optimizer :BIG PROBLEM With LIKE
> 'AA%'
> Subject: ONLINE V7.20 : Query Optimizer :BIG PROBLEM With LIKE
> 'AA%'
>
> We have a big problem of performance with a select like this one:
>
> SELECT * FROM artid WHERE artid_reference LIKE 'AA%'>
> The search is sequential (see with sqexplain.out)!!!!!! But there is a
> index on the field
> artid_reference.
> The table have 200 000 records and the query can be most complex (join
> with
> other table...).
>
> I have try value 0 or 2 for OPTCOMPIND.
> I have use UPDATE STATISTICS ......
>
> To test this just type under DBaccess:
> create database test ;
> create table x (y integer,x char(20));
> create index x on x(x);
> set explain on;
> select * from x where x like "aa%";> you will see the result.
> Nota: if there is only one field in the table, it works correctly!
>
> Is there Anyone who can help me?
> thanks
>
> --
> Georges SCHNEIDER
> Incotec Inc.
>
> incotec@incotec.fr