• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

Update statement is running very very slow

UPDATE store sd
     SET sd.UN_NBR = 'Y'
   WHERE sd.un_nbr = 'N'
     AND sd.DISTRO in (SELECT unique ref_field_3
                             FROM pix pt
                            WHERE pt.sku_id = sd.SKU_ID
                              AND pt.TRAN_TYPE = '620'
                              AND pt.TRAN_CODE = '01'
                              AND pt.ACTN_CODE = '02'
                              AND pt.proc_stat_code = 20)

Can I make any changes so that it runs faster.  There is index on distro , un_nbr  columns.

pix  table contains 12millions rows
store table contains 7 million rows
0
oraclescsa
Asked:
oraclescsa
2 Solutions
 
SharathData EngineerCommented:
You can change the IN to EXISTS clause and remove the UNIQUE keyword. Also If the datatype of TRAN_TYPE, TRAN_CODE and ACTN_CODE is INT, you can remove the quotes around 620, 01 and 02. Check the performance now.
UPDATE store sd
     SET sd.UN_NBR = 'Y'
   WHERE sd.un_nbr = 'N'
     AND exists  (SELECT 1
                             FROM pix pt
                            WHERE pt.sku_id = sd.SKU_ID
                              AND pt.TRAN_TYPE ='620'
                              AND pt.TRAN_CODE = '01'
                              AND pt.ACTN_CODE = '02'
                              AND pt.proc_stat_code = 20
							  And sd.DISTRO = pt.ref_field_3)

Open in new window

0
 
DavidSenior Oracle Database AdministratorCommented:
What kind of index do you have on sd.un_nbr (bitmap, leaf, etc.), and how do you update the tables' statistics?

Since you are updating the indexed column, I infer you are having overhead in updating the index when the change commits.  I might pursue modifying the index to defer validation.
0
 
Mark GeerlingsDatabase AdministratorCommented:
The index "on distro , un_nbr  columns" of the store table doesn't help much, if at all for this update.

An index that could help dramatically would be on the five columns of the pix table that your sub-query references.

Whether it helps or not to remove the single quotes around your bind variable values depends on the datatypes of those columns.  If they are CHAR ot VARCHAR2, you need the single quotes.  If they are NUBMER, then the quotes should be removed.
 
Whether it helps (or not) to use an "EXISTS" sub-query instead of "IN" depends on whether those columns are indexed or not.  If not, I would expect the "IN" query to be better.  Also, your server and storage hardware plus your init parameters also affect the performance of statements like this.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
What is your Oracle version?

How many of the 7 million rows are getting updated?

Can you post the execution plan of the inner select by itself?

I'm thinking an index on the joined column, sku_id on both tables might help.

Try a MERGE instead of an update (syntax is in the docs and all over the site if you look around).

Here's the first example I came across on this site:
http://www.experts-exchange.com/Database/Oracle/Q_28316481.html


How often is pix updated or inserted into?  Have you thought about a materialized view (MV) of just the select?  Then you can use the MV in the update.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
How often does this update occur/has to be done?! If it's just a "once in a month or quarter" work, you might want to set up a DBMS_JOB (or scheduler) for it. The job could process the whole update "with little chunks" (i.e. by adding ... and rownum <= 1000 to your query).

But, nevertheless, you should definetely check your indexes and table/index statistics and histograms.
You might also check the possibility of partitioning your table and/or your indexes...
0
 
oraclescsaAuthor Commented:
Sorry was off for 2 days so could not reply to your queries.

@sharath123: I have already used your query but no improvements.

@dvz : I am using bitmap index for the column un_nbr

@mark_geer : Oracle version 10g Rel 2. I am having index on 4 columns except actn_code. As the columns datatype is varchar2 so cannot remove the single quotes on the column.

@slightwv : Oracle 10g R2. This update job runs every 1 hour and it is taking almost 30 mins - 45 mins depending on the number of lines to be inserted.

I am attaching execution plan for the update statement.
Execution-Plan.xlsx
0
 
slightwv (䄆 Netminder) Commented:
I asked for the execution plan of the inner select not the update statement itself.

Not sure what generated that plan but I really don't like it.

Can you post the result from the following:
explain plan for 
SELECT ref_field_3
                             FROM pix pt
                            WHERE pt.sku_id = sd.SKU_ID
                              AND pt.TRAN_TYPE = '620'
                              AND pt.TRAN_CODE = '01'
                              AND pt.ACTN_CODE = '02'
                              AND pt.proc_stat_code = 20;

SELECT * FROM TABLE(dbms_xplan.display);

Open in new window


I also asked several additional questions that I do not see answers for.
0
 
oraclescsaAuthor Commented:
Attached is the explain plan for the inner query .

How many of the 7 million rows are getting updated?
between 40000-70000 rows

Can you post the execution plan of the inner select by itself?
Yes available in the attachment

I'm thinking an index on the joined column, sku_id on both tables might help.
Indexes are available on both tables at skuid columns

How often is pix updated or inserted into?  Have you thought about a materialized view (MV) of just the select?  Then you can use the MV in the update.
Pix is updated every 2 hours.
explain.xls
0
 
slightwv (䄆 Netminder) Commented:
>>Indexes are available on both tables at skuid columns

This contradicts what you said in your original post:
There is index on distro , un_nbr  columns.

We need all information.

>>Attached is the explain plan for the inner query .
>>between 40000-70000 rows

The inner query explain plan is only showing about 1300 rows and a cost of 2 with indexes being used.  Granted the cost number is made up and really doesn't mean anything but based on that execution plan, I don't see how we can make it faster.

Are you sure that was on the same database as the original row numbers posted?
0
 
oraclescsaAuthor Commented:
Solution Helped me
0
 
slightwv (䄆 Netminder) Commented:
Can I ask why the "B" penalty grade?

Please review:
http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-?b_id=44

B is the grade given for acceptable solutions, or a link to an acceptable solution. A B grade means the solution given lacked some information or required you to do a good amount of extra work to resolve the problem. When closing the question, the asker should explain why a B grade was awarded.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now