Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update statement is running very very slow

Posted on 2013-12-26
11
Medium Priority
?
544 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
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 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 14

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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1200 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 78

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 78

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses

971 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