Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Performance Help

Re: SQL Performance Help

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 15 Dec 2006 04:40:55 -0800
Message-ID: <1166186455.513416.168870@16g2000cwy.googlegroups.com> 





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.




Sybrand's answer is absolutely correct.



A short experiement to find out why:


CREATE TABLE TESTING1 (



  FIELD1 VARCHAR2(40),



  FIELD2 VARCHAR2(40),



  COMPARISON_FIELD VARCHAR2(40));


CREATE INDEX IND_TESTING_COMP ON TESTING1 (COMPARISON_FIELD);


Since we will be updating based on the contents of COMPARISON_FIELD, we
will wisely index that column.  Of course, the update statement will
not use the index, but I will assume that you have an index on that
column.



Insert 10,000 random text values into the table:
INSERT INTO



  TESTING1



SELECT


  DBMS_RANDOM.STRING('A',40),
  DBMS_RANDOM.STRING('A',40),
  DBMS_RANDOM.STRING('A',40)




FROM



  DUAL



CONNECT BY LEVEL<=10000;



COMMIT;


EXEC DBMS_STATS.GATHER_TABLE_STATS('schema', 'TESTING1', CASCADE=>
TRUE);


SELECT



  INDEX_NAME,



  DISTINCT_KEYS,



  NUM_ROWS,



  SAMPLE_SIZE,



  LAST_ANALYZED



FROM



  DBA_INDEXES



WHERE



  INDEX_NAME='IND_TESTING_COMP';



INDEX_NAME  DISTINCT_KEYS  NUM_ROWS  SAMPLE_SIZE  LAST_ANALYZED

IND_TESTING_COMP  10000  10000  10000  15-DEC-2006 07:01:18


Using a technique posted on Jonathan Lewis' blog to gather than plan
statistics while executing a SQL statement:
UPDATE /*+ GATHER_PLAN_STATISTICS */



  TESTING1



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 REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),'-'),'.'),'\')

LIKE '%KEYWORD4%'



  OR REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),'-'),'.'),'\')

LIKE '%KEYWORD5%'



  OR REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),'-'),'.'),'\')

LIKE '%KEYWORD6%'



  OR REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),'-'),'.'),'\')

LIKE '%KEYWORD7%'



  OR REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),'-'),'.'),'\')

LIKE '%KEYWORD8%'



  OR REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),'-'),'.'),'\')

LIKE '%KEYWORD9%'



  OR REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),'-'),'.'),'\')

LIKE '%KEYWORD10%';


0 ROWS UPDATED


SELECT



  *


FROM



  TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));


PLAN_TABLE_OUTPUT





SQL_ID	c8qdj6yv5gw7k, child number 0



PLAN_TABLE_OUTPUT








PLAN_TABLE_OUTPUT





 UPDATE /*+ GATHER_PLAN_STATISTICS */	TESTING1 SET

FIELD1=:"SYS_B_00",



FIELD2=:"SYS_B_01" WHERE



REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),:"SYS_

B_02"),:"SYS_B_03"),:"SYS_B_04") LIKE :"SYS_B_05"   OR

REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),:"SYS_B_06"),:"SYS_B_07"),:"SYS_B

_08") LIKE :"SYS_B_09"	 OR


REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),:"SYS
_B_10"),:"SYS_B_11"),:"SYS_B_12") LIKE :"SYS_B_13"   OR
REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),:"SYS_B_14"),:"SYS_B_15"),:"SYS_B
_16") LIKE :"SYS_B_17"	 OR
REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),:"SYS


_B_18"),:"SYS_B_19"),:"SYS_B_20") LIKE :"SYS_B_21"   OR

REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),:"SYS_B_22"),:"SYS_B_23"),:"SYS_B

_24") LIKE :"SYS_B_25"	 OR


REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),:"SYS
_B_26"),:"SYS_B_27"),:"SYS_B_28") LIKE :"SYS_B_29"   OR
REPLACE(REPLACE(REPLACE(LOWER(COMPARISON_FIELD),:"SYS_B_30"),:"SYS_B_31"),:"SYS_B
_32") LIKE :"SYS_B_33"	 OR REPLACE(REPLACE(REPLACE(


=================
| Id  | Operation	   | Name     | Starts | E-Rows | A-Rows |   A-Time
| Buffers |
|   1 |  UPDATE 	   | TESTING1 |      1 |	|      0 |00:00:00.08 |
184 |
|*  2 |   TABLE ACCESS FULL| TESTING1 |      1 |   4013 |      0
|00:00:00.08 |     184 |





Predicate Information (identified by operation id):


   2 -


filter((REPLACE(REPLACE(REPLACE(LOWER("COMPARISON_FIELD"),:SYS_B_02),:SYS_


              B_03),:SYS_B_04) LIKE :SYS_B_05 OR

REPLACE(REPLACE(REPLACE(LOWER("COMPARISON_FIEL

              D"),:SYS_B_06),:SYS_B_07),:SYS_B_08) LIKE :SYS_B_09 OR


REPLACE(REPLACE(REPLACE(LOWER("COMPARISON_FIELD"),:SYS_B_10),:SYS_B_11),:SYS_B_12

              ) LIKE :SYS_B_13 OR



REPLACE(REPLACE(REPLACE(LOWER("COMPARISON_FIELD"),:SYS_B_14),

              :SYS_B_15),:SYS_B_16) LIKE :SYS_B_17 OR

REPLACE(REPLACE(REPLACE(LOWER("COMPARISON

              _FIELD"),:SYS_B_18),:SYS_B_19),:SYS_B_20) LIKE :SYS_B_21 OR


REPLACE(REPLACE(REPLACE(LOWER("COMPARISON_FIELD"),:SYS_B_22),:SYS_B_23),:SYS_B_24

              ) LIKE :SYS_B_25 OR



REPLACE(REPLACE(REPLACE(LOWER("COMPARISON_FIELD"),:SYS_B_26),

              :SYS_B_27),:SYS_B_28) LIKE :SYS_B_29 OR

REPLACE(REPLACE(REPLACE(LOWER("COMPARISON

              _FIELD"),:SYS_B_30),:SYS_B_31),:SYS_B_32) LIKE :SYS_B_33 OR


REPLACE(REPLACE(REPLACE(LOWER("COMPARISON_FIELD"),:SYS_B_34),:SYS_B_35),:SYS_B_36

              ) LIKE :SYS_B_37 OR



REPLACE(REPLACE(REPLACE(LOWER("COMPARISON_FIELD"),:SYS_B_38),

              :SYS_B_39),:SYS_B_40) LIKE :SYS_B_41))


Note in the above that I have CURSOR_SHARING=FORCE specified for this
database.  The constants that I supplied have been automatically
replaced with bind variables.  Executing the same statement with
different constants will not require another hard parse of the SQL
statement.  Ideally, I would have used bind variables, rather than
relying on Oracle's CURSOR_SHARING=FORCE  (Side note: CURSOR_SHARING is
currently set to EXACT in my production database due to an unresolved
issue with 10.2.0.2 patch 9).  If you do not use bind variables, or
have CURSOR_SHARING=FORCE specified, Oracle will have to hard parse the
UPDATE statement every time it is submitted with different constant
values.



In the plan you will see TABLE ACCESS FULL, even though we were
thoughtful to include an index on the COMPARISON_FIELD column.  Why?
Placing a column in a function prevents Oracle from using an index to
access specific table rows, thus the suggestion to use function based
indexes.  But there is still a problem.  The first character in the
LIKE string is a % - Oracle still will not use an index, even a
function based index for the update.



Charles Hooper


PC Support Specialist


K&M Machine-Fabricating, Inc.
Received on Fri Dec 15 2006 - 06:40:55 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US