-
Hi all,
I posted this question to usenet last week but no response so far :-(.
I'm sure somebody here tells me what's wrong.
From Oracle doco8.1.7:
TUNING PARALLEL EXECUTION:
"When a table or index has NOLOGGING set, neither parallel nor serial
direct-load INSERT operations generate undo or redo logs."
ALTER INDEX NOLOGGING:
"Use LOGGING or NOLOGGING to specify whether subsequent
Direct Loader (SQL*Loader) and direct-load INSERT operations
against a nonpartitioned index, a range or hash index partition, or
all partitions or subpartitions of a composite-partitioned index
will be logged (LOGGING) or not logged (NOLOGGING) in the redo
log file."
BUT ...
when I create a table and set it as nologging, subsequent direct-load INSERT
makes reasonable amount of redo.
After truncating the table I create an ordinary index on that table, set it
nologging as well and make direct-load INSERT again. The redo is huge.
It seems that NOLOGGING status applies to tables only despite of manual.
Is that bug or feature?
My Oracle is 8.1.7 EE on AIX
Thanks in advance,
Ales
***********************************************************************
Testing report follows:
***********************************************************************
SQL>create table t nologging as select * from all_objects where 1=0;
Table created.
SQL>set autotrace on statistics
SQL>insert /*+ APPEND */ into t select * from all_objects;
21734 rows created.
Statistics
----------------------------------------------------------
786 recursive calls
154 db block gets
83190 consistent gets
0 physical reads
19616 redo size
493 bytes sent via SQL*Net to client
466 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
21734 rows processed
SQL>commit;
Commit complete.
SQL>truncate table t;
Table truncated.
SQL>create index t_ind on t (object_id) nologging;
Index created.
SQL>select table_name, logging from tabs where table_name='T'
2 union
3 select index_name, logging from ind where table_name='T';
TABLE_NAME LOG
------------------------------ ---
T NO
T_IND NO
--(Statistics omitted)
SQL>insert /*+ APPEND */ into t select * from all_objects;
21735 rows created.
Statistics
----------------------------------------------------------
1972 recursive calls
1437 db block gets
83613 consistent gets
319 physical reads
1484268 redo size
494 bytes sent via SQL*Net to client
466 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
1 sorts (disk)
21735 rows processed
SQL>commit;
Commit complete.
***********************************************************************
-
redo are always generated, nologging minimize the amount but not to zero
Code:
opsc@MOVE>alter table obj nologging;
Table altered.
opsc@MOVE>set autotrace on
opsc@MOVE>insert /*+ append */ into obj select * from dba_objects where rownum < 100
99 rows created.
Statistics
----------------------------------------------------------
221 recursive calls
12 db block gets
98 consistent gets
10 physical reads
296 redo size
633 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
99 rows processed
opsc@MOVE>alter table obj logging;
Table altered.
opsc@MOVE>insert into obj select * from dba_objects where rownum < 100;
99 rows created.
Statistics
----------------------------------------------------------
151 recursive calls
26 db block gets
78 consistent gets
0 physical reads
10456 redo size
647 bytes sent via SQL*Net to client
489 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
99 rows processed
296 redo size with nologging vs 10456 redo size with logging
-
Pando, thanks for reply.
Yes, I understand there must be some redo.
Just see again my original post.
Populating the table with over 20,000 rows makes about 20 K redo. That's great and works as I'd expect.
But the same count of rows inserted into the same table with NOLOGGING index makes about 1,5M redo. That is the problem. It seems that subsequent maintenance of nologgingg index after direct-load insert is fully loged in redo. But (if I understand it) it shouldn't.
-
you mean that if you create an index with nologging then susquent insert shouldnt generate redo with that index...?
AFIAK if you reverse your process, first insert then create the index with nologging then you get minimum redo but if you insert with an index then it´s pretty normal?
-
Originally posted by pando
you mean that if you create an index with nologging then susquent insert shouldnt generate redo with that index...?
No. Subsequent direct-load insert shouldn't.
If I create an index with nologging clause on populated table, then redo is small.
If I create an index with nologging clause on empty table and then populate the table with direct-load insert, then redo is big.
-
you mean that if you have an index with nologging attribute then the inserts afterwards should not generate redo?
direct load works if you use insert append or create index (and a few more) that doesnt mean that after you create an index with nologging then the redo would be small later
If I create an index with nologging clause on populated table, then redo is small.
this is normal because the index creation process is generating minimum redo
If I create an index with nologging clause on empty table and then populate the table with direct-load insert, then redo is big
this is normal too because the redo generated is for both table and index
-
Hi, I'm sorry for the delay.
I don't want to annoy with full report of testing,
so have a look on the following summary table:Operation Bytes of redo
create table nologging ........................ 39172
direct-load insert of 22,000 rows ............. 19613
create index nologging on populated table ..... 33456
truncate table ................................ 22556
direct-load insert of 22,000 rows
with both table and index set to nologging .... 1487556
I hope it's clear what I mean: the redo generated with the last operation should be something about 55K, not 1.5 M.
That's why I mean something is wrong with NOLOGGING.
-
you cannot set an index to nologging, I see it as a normal behaviour, a table with an index obviously generates more redo than one table without index even the table is set to nologging
you can create an index with no logging but not alter it´s attribute
-
Originally posted by pando
you cannot set an index to nologging
Yes, I see now and that's I'm worrying about :-), but doesn't manual say the exact opposite? (see my original post)
ALTER INDEX NOLOGGING really sets the NOLOGGING column in TABS to "NO".
But subsequent direct-load INSERT does not care and manual says it does.
Please kick me if I misunderstand it.
-
well I make a pardon you can set to nologging an index
so basically you are saying that an table with nologging and its indexes with nologging is generating more redo than a table without indexes with nologging ?
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
|