Re: SQL Performance Help

Brian Kelly wrote:
> I'm working on an auto population program and this program has to
> execute thousands of SQL statements. Each SQL statement really dumbs
> down the comparing field using Oracle's built in functions lower() and
> about 6 replace()'s. These REALLY slow down the execution of each of
> these statements ALOT. A statement, for example might be like this.
>
> UPDATE blah
> SET field1='whatever',field2='whatever'
> WHERE replace(replace(replace(lower(comparison_field),'-'),'.'),'\')
> LIKE '%keyword%';
>
> However, I've also grouped similar statements together like this
>
> UPDATE blah
> SET field1='whatever',field2='whatever'
> WHERE replace(replace(replace(lower(comparison_field),'-'),'.'),'\')
> LIKE '%keyword1%'
> OR replace(replace(replace(lower(comparison_field),'-'),'.'),'\') LIKE
> '%keyword2%'
> OR replace(replace(replace(lower(comparison_field),'-'),'.'),'\') LIKE
> '%keyword3%'
> OR ...;
>
> The grouped statements take [insert # of LIKE's comparisons here] times
> longer then the first statement due to the fact that it has to
> re-execute these functions for every comparison. Is there any way to
> execute the functions once and then reference that result later in the
> same SQL statement? Any help would be appreciated.

Your problems are NOT being caused by those functions!!!!!!!!!! They are being caused by you
- NOT using BIND VARIABLES, forcing Oracle to parse every statement - using FULL TABLE SCANS all over the place, by virtue of NOT using Function Based Indexes and predicates which CAN actually use an index.

Built-in functions are not time consuming at all, and obviously they need to be, by virtue of your statements, to be applied on every individual record!

-- 
Sybrand Bakker
Senior Oracle DBA