Re: rtrim() performance nightmare???

From: Kevin P. Fleming <kfleming_at_access-laserpress.com>
Date: Thu, 17 Sep 1998 23:46:27 GMT
Message-ID: <nbhM1.44421$435.21148783@news.rdc1.az.home.com>

Sure it is, because there's no way to use an index to perform the query. You'd be much better off with the following type of query:

SELECT b.field1, b.field2
FROM (
  SELECT a.field1, a.field2, a.field4
  FROM table_a a
  WHERE a.field3 = 'value' and a.field4 LIKE 'value%'   ) b
WHERE rtrim(b.field4) = 'value';

This will allow Oracle to use indexes to find all the possible-match rows (satisfying the LIKE predicate), then it can apply the RTRIM function to only the candidate rows, instead of all the rows in the table.

InfoTech Consulting, Inc. wrote in message <6truq7$mf5$1_at_news.iquest.net>...
>I was testing the performance of a query today and noticed the following
>query took a LONG TIME to run (when using rtrim() function):
>
>SELECT field1, field2
>FROM table_a
>WHERE field3 = 'value' and
>rtrim(field4) = 'value'
>
>... this one returns data almost immediately:
>SELECT field1, field2
>FROM table_a
>WHERE field3 = 'value' and
>field4 LIKE 'value%'
>
>Is the rtrim() function a performance hog?
>
>Thanks,
>
>Dave Morse
>Consultant - InfoTech Consulting, Inc.
>mailto:dmorse_at_iquest.net
>
>
Received on Thu Sep 17 1998 - 18:46:27 CDT