-
Repolulating the tables with multi-million rows for reduced redo logs
Environment - Oracle 11g on linux
I need to drop and recreate 10 kind-of temp tables (with some of the tables having multi-million rows) by application call.
Below script (takes 10 minutes) is what I am doing.
Questions:
1. Is there a better performing/handling way.
2. I am avoiding dropping and recreating the below tables as it invalidates some views and also want to use no-logging.
3. Is TRUNCATE TABLE REUSE STORAGE good option here
4. Should I drop and recreate indexes.
5. There is separate locally managed tablespace holding these 10 tables. Is it better to do nologging at tablespace level or on individual tables level - purpose is to reduce red log creation for the process
Please advice with answers to above questions. Thanks
--------------------------------------------------------
ALTER TABLESPACE tbs_10_tables NOLOGGING;
alter table T1 nologging;
alter table T2 nologging;
...
...
alter table T10 nologging;
--the script below will run everytime
---1
TRUNCATE TABLE T1 REUSE STORAGE;
drop index IDX_T1_1
drop index IDX_T1_2
drop index IDX_T1_3
---2
TRUNCATE TABLE T2 REUSE STORAGE;
drop index IDX_T2_1
drop index IDX_T2_2
drop index IDX_T2_3
...
...
---10
TRUNCATE TABLE T10 REUSE STORAGE;
drop index IDX_T10_1
drop index IDX_T10_2
drop index IDX_T10_3
----------- re-polulation of data
Insert into T1 Select * from T1v;
create index IDX_T1_1 on T1 (col1);
create index IDX_T1_2 on T1 (col2);
create index IDX_T1_3 on T1 (col3);
Insert into T2 Select * from T2v;
create index IDX_T2_1 on T2 (col1);
create index IDX_T2_2 on T2 (col2);
create index IDX_T2_3 on T2 (col3);
...
...
Insert into T10 Select * from T10v;
create index IDX_T10_1 on T10 (col1);
create index IDX_T10_2 on T10 (col2);
create index IDX_T10_3 on T10 (col3);
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
|