Repolulating the tables with multi-million rows for reduced redo logs

[daljitsb]

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);

[vnktummala]

1. Is there a better performing/handling way.

can improve little bit. you are truncating tables first then dropping indexes this need I/O on indexes also. first drop indexes then truncate.

2. I am avoiding dropping and recreating the below tables as it invalidates some views and also want to use no-logging.

Oracle says ...

When an existing tablespace logging attribute is changed by an ALTER TABLESPACE statement, all tables, indexes, and partitions created after the statement will have the new default logging attribute (which you can still subsequently override). The logging attribute of existing objects is not changed.

so, better use object level nologging option.

3. Is TRUNCATE TABLE REUSE STORAGE good option here

If you know that you are going to load almost same amount of data, the answer is yes.

4. Should I drop and recreate indexes.

as you are dealing with multi-million rows, yes you need to. drop index -> truncate -> load -> recreate index.

create the indexes with nologging option if they are not in nologging TS.

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 refer answer 2.

Thanks,

[daljitsb]

Thanks vnktummala.

I was doing the table population earlier by dropping and recreating the table as 'create table myTable as select * from' - that was generating lot of redo.
Then I changed the table and index to nologging and populated it as
INSERT /*+ APPEND */ into myTable Select * from...

I was expecting reduced redo but it actually went up. See numbers below.
Why did redo went up??

------------------------

select value OLD_VALUE from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

46772

create table myTable as Select * from myTable_v;
Create index myTable_IX1 on myTable (col1);
Create index myTable_IX2 on myTable (col2);


select value OLD_VALUE from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

460488


--------------


alter table myTable nologging;

drop index myTable_IX1;
drop index myTable_IX2;
TRUNCATE TABLE myTable REUSE STORAGE;

select value OLD_VALUE from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

520446

INSERT /*+ APPEND */ into myTable Select * from myTable;
commit;
Create index myTable_IX1 on myTable (col1) NOLOGGING;
Create index myTable_IX2 on myTable (col2) NOLOGGING;

select value OLD_VALUE from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

1002608

[PAVB]

Two questions...

1- Are you positive the ONLY process running on the database was this one? if any other process was running that will affect redo size.

2- As far as I can see table was created from two different sources - myTable_v on the first run and PCPA_PLAN_PTEVENT on the second run.

Also... if you are planning to do insert APPEND do not use REUSE STORAGE during truncation, APPEND hint causes Oracle to insert above high watermark.

[daljitsb]

Thanks PAVB.

Yes, there is nothing else running.

2 table sources were typo. I edited it now.

Don't we need APPEND to realize the affect of nologging.

Are you saying Reuse Storage is not benefiting the performance here. With Truncate the HWM should come down and append will insert data from lowered HWM but will not have to expand the TS storage as it fills multi-million rows.

[vnktummala]

Thanks PAVB.

Don't we need APPEND to realize the affect of nologging.

Are you saying Reuse Storage is not benefiting the performance here. With Truncate the HWM should come down and append will insert data from lowered HWM but will not have to expand the TS storage as it fills multi-million rows.

APPEND use direct path and appends the data after the HWM.

Truncate with reuse storage would not bring down HWM. So not recommended to use when you are using APPEND hint.

Thanks,

[daljitsb]

Did not get any performance gains or even reduced logging by switching to nologging mode of table and indexes.
Why. Am I missing a step or parameter.

The process (covering 10 tables) is actually now taking more time to complete earlier was 14 minutes, now 16 minutes

--logging mode

drop table a
create table a as select * from a_view
create indexes
set grants

select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';
before run and after run 'value' = 10254896


-------------------

--nologging mode

truncate table a
alter table a nologging;
insert /*+ APPEND */ into table a as select * from a_view
create indexes parallel 2 NOLOGGING

select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';
before run and after run 'value' = 11657796

[daljitsb]

Any suggestions

[BeefStu]

Any suggestions

interesting query, what exactly does this measure, redo for a specific session?

select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

[daljitsb]

Yes