Performance Tuning on Update query

Question

My simple update query takes too long to execute. There're around 10m records- out of those I'm executing for 1k records only.

Statement is like this:

UPDATE tab
   SET col1= 'yes',
       col2 = 'yes'
 WHERE col7 ||'_'|| col8 = 'VAL_0'
   AND col10 = 'NA'

This statement takes around 70mins to execute.

I've also created the index on col7 ||'_'|| col8 this. Although it takes this amount of time.

EDIT: Here 'VAL_0' is coming from other resultset(data-table)

Is there any suggestions so I can make my query faster to run. I tried using indexes on (col10) and on (col7 ||'_'|| col8), but it takes hell lot of time to execute. other tuning techniques you suggest?

Thanks

I'm not an oracle man, but: It should be better to check for `col7='VAL' AND col8=0`... Is there an index on col10? Are other indexes affected by setting col1 and col2 to "yes"? — Shnugo, Apr 12 '16 at 12:56
@jarlh, Joe Taras, sorry for typing mistake I updated my question and 'VAL_0' is data which is coming from other table — inityk, Apr 12 '16 at 14:39

score 0 · Answer 1 · answered Apr 12 '16 at 12:59

0

This is the query:

UPDATE tab
   SET col1= 'yes',
       col2 = 'yes',
 WHERE col7 ||'_'|| col8 = 'VAL_0' AND col10 = 'NA'

Start with an index on tab(col10). That might help.

If you can, change the query to:

UPDATE tab
   SET col1= 'yes',
       col2 = 'yes',
 WHERE col7 = 'VAL'  AND col8 = '0' AND col10 = 'NA';

(Note: Use 0 instead of '0' if col8 is actually a number.)

Then create an index on tab(col10, col7, col8)

answered Apr 12 '16 at 12:59

Gordon Linoff

1,242,037
58
646
786

2

Can you describe why you chose the column order col10, col7, col8 when you create the index? – jarlh Apr 12 '16 at 13:02
@jarlh does it matter which order the columns of the index are in, based on the information the OP has provided? Any permutation would be fine, imo. (Obviously, if there are other queries using one or more of these 3 columns, then that would change things - maybe. But the OP hasn't mentioned anything else other than this one query, so...) – Boneist Apr 12 '16 at 13:27
@jarlh . . . For equality conditions it doesn't matter. I was just choosing an index that would work on both versions of the query. – Gordon Linoff Apr 13 '16 at 01:55

Performance Tuning on Update query

1 Answers1