Solved

Update statement is running very very slow

Posted on 2013-12-26
11
516 Views
Last Modified: 2014-01-19
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
Comment
Question by:oraclescsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39740463
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
 
LVL 23

Expert Comment

by:David
ID: 39740625
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39740638
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
Industry Leaders: 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!

 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 400 total points
ID: 39740853
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39741641
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
 

Author Comment

by:oraclescsa
ID: 39744392
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 39745359
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
 

Author Comment

by:oraclescsa
ID: 39747566
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39749199
>>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
 

Author Closing Comment

by:oraclescsa
ID: 39791847
Solution Helped me
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39792973
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question