SQL & PL/SQL

Hi,

My database is 10.2.0.3.0. I have an update statement that runs for over 10 hours, please see the following explain and the statement. Table1 has over 21 million rows. It has only one PK index on (col1, col2, col3, col4, col5, col6, col7);

SQL> explain plan for
2 UPDATE table1
3 SET col9 = 0
4 WHERE (col1, col2, col3, col4, col5) IN
5 ( SELECT col1,
6 col2,
7 col3,
8 MIN (col4),
9 MIN (col5)
10 FROM table1
11 GROUP BY col1, col2, col3);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 88 | | 521K (1)|
| 1 | UPDATE | table1 | | | | |
| 2 | HASH JOIN SEMI | | 1 | 88 | 936M| 521K (1)|
| 3 | TABLE ACCESS FULL| table1 | 21M| 692M| | 223K (1)|
| 4 | VIEW | VW_NSO_1 | 21M| 1099M| | 183K (1)|
| 5 | SORT GROUP BY | | 21M| 631M| 1962M| 183K (1)|
| 6 | INDEX FULL SCAN| table1_PK | 21M| 631M| | 183K (1)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------

From above plan, I think it's the full table scan on tabl1 that might have caused the long hours, how can I speed it up? Can CTAS work in this case? Please shed some light. Thank you in advance!!