Update statement is running very very slow

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)

Select allOpen in new window

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.

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.

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...

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

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

>>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?

Solution Helped me

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.