-
Heavy Update
Hi,
I have a table which contains 46 million rows. I have added a new field in that table. Now i want that field should contain say 'Y' for every row. For that, if i go for update table set new_field='Y' it takes around 48 hrs. Please tell me how can i reduce the time taken by the query or suggest some other way for doing this.
Thanks N regards
Win
-
-
From your post there
/*************
You're not going to be much quicker like that. If you really want speed then you'll have to create a new table in nologging mode by selecting the current table, then truncate the old table, disable the indexes, add the new column, set it to nologging, insert /*+ append */ back into it (and include the 'Y' column), then rebuild indexes in nologging mode and analyze the table again.
******************/
The following steps are followed there.
1 Create a table with the by selecting from current table.
2 Truncate the old table
3 insert back to original table ( Takes more than half an hour??).
(after dropping indexes)
4 Rebuild indexes
How about
1. create a table with by selecting the fields and also the Y column replaced
2. drop the old table
3. rename the new table to the old table
4. create indexes
-
Could do, yes, but then you have to fiddle around with recreating any triggers, grants, indexes, recompiling views/procedures/MV's etc -- if you didn't have any of those then your approach would be a better one. Mine was a blend between performance and laziness, I guess!
-
Yes. Export the table without rows=n.
After rename the table
rebuild the indexes.
then import with the above export with ignore=y option.
Then compile the dependencies, if timestamp needs to be applied, do the same.
-
Pls read the first line as
Yes. Export the table with rows=n.
-
==
Hi,
I have a table which contains 46 million rows. I have added a new field in that table. Now i want that field should contain say 'Y' for every row. For that, if i go for update table set new_field='Y' it takes around 48 hrs. Please tell me how can i reduce the time taken by the query or suggest some other way for doing this.
Thanks N regards
Win
===
Hi Win,
What is your oracle version?
What is the PCTFREE value set for the table?
What is the average row length?
How many datafiles are allocated to the tablespace in which the table sits?
How many CPUs does the box have?
What is your SGA Size?
If you want to win, you must supply more above.
No exp/imp, no create table, nothing...
The simple solution I am thinking of is "parallel update".
PHP Code:
Example:
set time on timing on
spool upd_my_table_2.log
drop index scott.my_table_m3 ;
--- 1 option
alter table scott.my_table parallel 8;
alter index scott.my_table_p1 parallel 8;
alter table scott.tamil_temp_con_addr parallel 8;
alter index scott.tamil_temp_con_addr_idx parallel 8;
commit;
alter session enable parallel dml ;
alter session enable parallel query ;
update /*+ parallel(a,8) */ scott.my_table a
set a.pr_addr_id = ( select addr_per_id
from scott.tamil_temp_con_addr c
where c.accnt_id = a.row_id)
where exists ( select null
from scott.tamil_temp_con_addr c
where c.accnt_id = a.row_id) ;
commit;
alter session disable parallel dml ;
alter session disable parallel query ;
alter table scott.my_table noparallel ;
alter index scott.my_table_p1 noparallel ;
spool off
Tamil
Last edited by tamilselvan; 03-03-2005 at 08:17 PM.
-
I don't want Rowid to be changed. In this case Rowid will be different.
Thanks
Win
Vijay
-
very useful query from Tamil. But need to be tested for time (Though parallel.) and UNDO.
Where is the change of rowid occuring? Rowid will change in the
options given by Slimdave and me, not in Tamil's one
-
Following is my test table on which i'm doin the testing. Once it is done i'll test it on testing environment. Till now i'm testing it on my local Database.
TABLE NAME is A1 and INDEX NAME is Index_1, Which is created on Case_id and COL1
Name Null? Type
----------------------------------------- -------- ------------
CASE_ID NUMBER(15)
COL1 NOT NULL VARCHAR2(20)
COL2 NOT NULL VARCHAR2(20)
COL3 NOT NULL NUMBER(20)
NCOL VARCHAR2(50)
Now NCOL is newly added field n want to update it by 'Y'. This test table is having around 20 million rows.
GIRI n TAMIL, can u plz elaborate the solution provided by TAMIL.
Thanks n Regards
Win
Vijay
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|