Re: is LIKE statement limited?

<dbrah_at_my-deja.com> wrote in message news:85rc1q$m3b$1_at_nnrp1.deja.com...
> I have a big SELECT statement with many LIKE commands and it works fine
> if I only use 5 LIKE statements). Any more than 5 and it doesn't return
> anything. The SELECT is built dynamically from a web form using Perl and
> DBI/DBD::Oracle. I'm using Oracle 8i.
>
> SELECT * FROM filearchive WHERE (company LIKE '%%') AND (source LIKE
> '%%') AND (region LIKE '%%') AND (solution LIKE '%%') AND (technology
> LIKE '%%') AND (subject LIKE '%%') AND (summary LIKE '%%') AND (author
> LIKE '%%') AND (filetype LIKE '%%')
>
> All these fields (except author & filetype) can contain multiple terms
> separated by spaces so this was the only way I can figure out to create
> one SELECT to work for all cases. The web form I have allows users to
> select keywords from lists of categories and return a list of matching
> files. If a category is not chosen, LIKE '%%' is used because it acts
> as a simple placeholder or "don't care" which works great.

No. Never use this method.
If you don't care the column values, the only efficient and correct way is just to remove the conditions.

> This statement works fine (it has just 5 LIKE calls):
>
> SELECT * FROM filearchive WHERE (region LIKE '%%') AND (solution LIKE
> '%%') AND (summary LIKE '%%') AND (author LIKE '%%') AND (filetype LIKE
> '%Excel%')
>
> Is this a limitation of Oracle 8i? Is there a better way to do what I
> need to do?

No.
It's cause of your misconceptions.
If you put an unneeded "column like '%'" condition, it implies that Oracle will ONLY return the rows with non-null column values. And more, if the datatype is not string, Oracle will convert to string implicitly, therefore, it increases system overhead. Received on Sun Jan 16 2000 - 08:32:18 CST