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

Quote:

---

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.

Quote:

---

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

---

Oracle says ...

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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,

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

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.

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.

Quote:

---

Originally Posted by daljitsb

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,

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

Any suggestions

Quote:

---

Originally Posted by daljitsb

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

Yes

Quote:

---

Originally Posted by vnktummala

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,

---

Truncate allways brings the HWM down.

Hi Mike,

Please see the REUSE STORAGE section from this link.

Regards,

Quote:

---

Originally Posted by vnktummala

Hi Mike,

Please see the REUSE STORAGE section from this link.

Regards,

---

Where is it written that the HWM is not reset?

How should a truncate work without reseting the HWM?

The REUSE STORAGE option is related to the allocated exetens not to the HWM.

Regards

If the extents are not de-allocated then the HWM will at the end correct? In this case if we are using APPEND hint then oracle start allocating the space for new records after HWM which wastes some storage.

Please correct me if I am wrong.

Regards,

Hi,

There is no direct relation between the HWM and the number of extents.

An extent is just a logical space allocated to a table/segment.
The HWM informs from which point on all blocks are empty and unformatted.

The reason why a truncate is so fast is because Oracle just resets the HWM.
With the REUSE STORAGE option the extent allocated to the table are not removed. The main advantage of it is that the extents don't need to be reallocated to the table when data are inserted again. However I don't think that you got a real performance advantage when using a locally managed tablespace.

Regards