| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Exclude Tables from Archive log
In article <89ocg9$brl$1_at_nnrp1.deja.com>,
efflerch_at_my-deja.com wrote:
> Hallo,
> is there a way to exclude tables from ARCHIVELOG.
No, not when doing normal INSERTS/UPDATES and DELETES.
If you direct path load, insert /*+ APPEND */, and truncate -- yes, you can avoid redo -- otherwise no.
> Of course this Tables have no triggers or constraits to other tables .
>
> Background is: I load nightly data from a ASCII-file via SQL*Loader,
> which are a datapool for the "real" Application. (I did SQL Command
> "Alter table xxx nologging")
> But unfortunately I have after the sqlload a script running which is
> doing lots of formating stuff.
>
> Today I have approx 80% Archiv-Log files due to of UPDATE commands on
> the datapool Tables executed by formating scripts.
>
> I'm using Oracle 8.1.5 on WinNT
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Since 8.1.5 has temporary tables and:
...
DML statements on temporary tables do not generate redo logs for the
data
changes. However, undo logs for the data and redo logs for the undo
logs are
generated. Data from the temporary table is automatically dropped in
the case of
session termination, either when the user logs off or when the session
terminates
abnormally such as during a session or instance crash.
....
That means that redo should effectively be cut in half (see test below). So, what you could do is:
o direct path load into your table (no logging)
o insert /*+ append */ into a session based global temporary table (no
logging)
o mod this table instead (~1/2 the redo)
o truncate the table you loaded (no logging)
o insert /*+ append */ into that table from the temp table (no logging)
To test this approach, you could
o sqlldr as normal
o make the first line in your update script be "rename T to T_HOLD"
o create global temporary table T on commit preserve rows as select *
from T_HOLD where 1=0;
o insert /*+ append */ into t select * from t_hold
o -- run the existing script ---
o truncate t_hold
o insert /*+ append */ into t_hold select * from t;
o drop table t
o rename T_HOLD to T
to see how it goes (only need to modify the script a little to test)
Here is the test of redo generated on a temp table:
scott_at_dev8i> create global temporary table tmp_tab on commit preserve rows as select * from all_objects where 1=0; Table created.
scott_at_dev8i> create table t nologging as select * from all_objects
where 1=0;
Table created.
scott_at_dev8i> insert /*+ append */ into tmp_tab select * from
all_objects;
21785 rows created.
Statistics
839 recursive calls
133 db block gets
186380 consistent gets
0 physical reads
15828 redo size
996 bytes sent via SQL*Net to client
832 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
21785 rows processed
scott_at_dev8i> commit;
Commit complete.
REM -- 15k of redo to log the data dictionary changes made only. append is like a direct path insert.
scott_at_dev8i> update tmp_tab set object_name = initcap(object_name); 21785 rows updated.
Statistics
0 recursive calls
22397 db block gets
286 consistent gets
0 physical reads
3693968 redo size
1011 bytes sent via SQL*Net to client
826 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
21785 rows processed
3.6 meg of redo more or less....
scott_at_dev8i> insert /*+ append */ into t select * from all_objects; 21785 rows created.
Statistics
97 recursive calls
71 db block gets
186187 consistent gets
0 physical reads
7192 redo size
998 bytes sent via SQL*Net to client
826 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
21785 rows processed
about 7k of redo generated for this insert...
scott_at_dev8i> commit;
Commit complete.
scott_at_dev8i> update t set object_name = initcap(object_name); 21785 rows updated.
Statistics
0 recursive calls
22399 db block gets
286 consistent gets
286 physical reads
6067764 redo size
1014 bytes sent via SQL*Net to client
820 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
21785 rows processed
6meg of redo generated for the same update. --
Thomas Kyte tkyte_at_us.oracle.comOracle Service Industries
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sat Mar 04 2000 - 08:07:04 CST
 |
 |