Clear and Concise! Thanks.
Robert, September 05, 2002 - 11:03 am UTC
But what if...?
Adrian Billington, September 05, 2002 - 1:22 pm UTC
Tom
Good table. Agree so far, but back to my question about leaving NOLOGGING set to "on".
1) as we are a production instance, we are in archivelog mode of course;
2) this means that only developers writing direct path SQL*loader scripts and using /*+ append */ hints in their inserts run the risk of having unrecoverable work sent to the database;
3) so my plunge into seemingly dangerous territory is only borne out of the situation whereby, despite having NOLOGGING tables/indexes, everything is logged anyway unless you specifically ask it not to be - and asking it not to be would be controlled and managed anyway.
(Let's face it, I'm only trying to get around a situation where if I want a nologged action I have to alter the table to nologging, direct load insert, alter table back to logging again).
Which is why I also asked about any table moves or index rebuilds...
Thanks
Adrian
September 05, 2002 - 9:39 pm UTC
table moves and rebuilds will be done like APPEND would be.
In a production system -- you don't want objects to be left in "nologging" mode. You should turn it on -- do whatever -- turn it off and back it up.
Su Baba, August 06, 2003 - 6:32 pm UTC
Hi Tom,
In an archive log mode database instance, under what condition would redo NOT be generated for a CTAS operation? Is it enough to do just the following?
CREATE TABLE x AS
SELECT * FROM y
NOLOGGING;
or do I need to set the table to nologging as in the case of INSERT /*+ APPEND */?
thanks
August 09, 2003 - 11:55 am UTC
that doesn't create a nologging table. that is no different then:
CREATE TABLE x AS
SELECT * FROM y
HELLO_WORLD_HOW_ARE_YOU;
ops$tkyte@ORA9I> @mystat "redo size"
old 4: and lower(a.name) like '%' || lower('&1')||'%'
new 4: and lower(a.name) like '%' || lower('redo size')||'%'
NAME VALUE
------------------------------ ----------
redo size 560
ops$tkyte@ORA9I> create table t<b> NOLOGGING</b> as select * from all_objects;
Table created.
ops$tkyte@ORA9I> @mystat "redo size"
old 4: and lower(a.name) like '%' || lower('&1')||'%'
new 4: and lower(a.name) like '%' || lower('redo size')||'%'
NAME VALUE
------------------------------ ----------
redo size 73712
ops$tkyte@ORA9I> create table tt as select * from all_objects;
Table created.
ops$tkyte@ORA9I> @mystat "redo size"
old 4: and lower(a.name) like '%' || lower('&1')||'%'
new 4: and lower(a.name) like '%' || lower('redo size')||'%'
NAME VALUE
------------------------------ ----------
redo size 5071308
NOLOGGING
Deepak Gupta, August 30, 2003 - 3:00 am UTC
Dear Mr. Tom,
Hi,
CREATE TABLE x AS
SELECT * FROM y
HELLO_WORLD_HOW_ARE_YOU;
and
CREATE TABLE x
nologging
AS
SELECT * FROM y
HELLO_WORLD_HOW_ARE_YOU;
Q1 - what is difference between above two ? or what advantage using a nologging option ( as in last followup you said in both cases redo log will generate ) ?
database in archive mode
Q2- If I put index tablespace (that conatain b-tree indexes) in nologging mode what advantage or disavantage from this ?
( because at a time recovery index only need to rebuild )
August 30, 2003 - 10:46 am UTC
q1) i did not say thing -- in fact i demonstrated exactly the difference between
create table x as
and
create table x NOLOGGING as
one generated lots of redo.
one did not.
your first CTAS will generate much redo
your second CTAS will not
q2) only an "advantage" if you create/rebuild indexes in there frequently. make sure to BACK IT UP afterwards else you will have unrecoverable changes in that tablespace after a recovery.
unrecoverable columns on v$datafile not being updated
Robert Hayden, October 24, 2003 - 1:10 pm UTC
I have found all of the threads on the "nologging" option very useful. During testing, I have found that if I rebuild an index with the nologging option, then the columns unrecoverable_change# and unrecoverable_time on v$datafile are not updated to reflect the nologging operation. This would seem to have a disasterous affect on our ability to monitor such activity for a standby database. Hopefully, I am just missing something....
Here is an example, file# 7 is the i_tst tablespace. Note that the nologging operation did not change the v$datafile columns, especially on file#7. I was able to reproduce on Oracle 8.1.7.4 and Oracle 9.2.0.3 on AIX.
SQL> create table rwh (col1 number) tablespace d_tst;
Table created.
SQL> insert into rwh values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> create index xie1rwh on rwh (col1) tablespace i_tst;
Index created.
SQL> select index_name, logging from user_indexes;
INDEX_NAME LOG
------------------------------ ---
XIE1RWH YES
SQL> select file#, unrecoverable_change#, unrecoverable_time from v$datafile;
FILE# UNRECOVERABLE_CHANGE# UNRECOVER
---------- --------------------- ---------
1 132534 06-JUN-03
2 0
3 0
4 0
5 0
6 0
7 0
8 132534 06-JUN-03
8 rows selected.
SQL> alter index xie1rwh rebuild tablespace i_tst nologging;
Index altered.
SQL> select index_name, logging from user_indexes;
INDEX_NAME LOG
------------------------------ ---
XIE1RWH NO
SQL> select file#, unrecoverable_change#, unrecoverable_time from v$datafile;
FILE# UNRECOVERABLE_CHANGE# UNRECOVER
---------- --------------------- ---------
1 132534 06-JUN-03
2 0
3 0
4 0
5 0
6 0
7 0
8 132534 06-JUN-03
8 rows selected.
Thanks
Robert
October 24, 2003 - 3:40 pm UTC
not to worry -- fact is, if the object is really really small like that, we cheat.
we generated the redo, silently.
ops$tkyte@ORA9I> create tablespace test
2 datafile '/tmp/test.dbf' size 25m
3 /
Tablespace created.
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> drop table t;
drop table t
*
ERROR at line 1:
ORA-00942: table or view does not exist
ops$tkyte@ORA9I> create table t tablespace test
2 as
3 select * from all_objects ;
Table created.
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> create index t_idx on t(object_name,object_type,owner,object_id) tablespace test;
Index created.
ops$tkyte@ORA9I> pause
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> select file#, unrecoverable_change#, unrecoverable_time
2 from v$datafile
3 where file# in ( select file_id from dba_data_files where tablespace_name = 'TEST' )
4 /
FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
-------------------- --------------------- --------------------
65 0
ops$tkyte@ORA9I>
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> select index_name, logging from user_indexes where table_name = 'T';
INDEX_NAME LOG
------------------------------ ---
T_IDX YES
ops$tkyte@ORA9I> alter index t_idx rebuild tablespace test NOLOGGING;
Index altered.
ops$tkyte@ORA9I> select file#, unrecoverable_change#, unrecoverable_time
2 from v$datafile
3 where file# in ( select file_id from dba_data_files where tablespace_name = 'TEST' )
4 /
FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
-------------------- --------------------- --------------------
65 7725934409672 24-oct-2003 15:48:25
ops$tkyte@ORA9I>
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> select index_name, logging from user_indexes where table_name = 'T';
INDEX_NAME LOG
------------------------------ ---
T_IDX NO
ops$tkyte@ORA9I> alter index t_idx rebuild tablespace test;
Index altered.
ops$tkyte@ORA9I> select file#, unrecoverable_change#, unrecoverable_time
2 select index_name, logging from user_indexes where table_name = 'T';
select index_name, logging from user_indexes where table_name = 'T'
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
ops$tkyte@ORA9I> select file#, unrecoverable_change#, unrecoverable_time
2 from v$datafile
3 where file# in ( select file_id from dba_data_files where tablespace_name = 'TEST' )
4 /
FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
-------------------- --------------------- --------------------
65 7725934409716 24-oct-2003 15:48:25
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> drop tablespace test including contents and datafiles
2 /
Tablespace dropped.
That shows we do it "right" but look:
ops$tkyte@ORA9I> create tablespace test
2 datafile '/tmp/test.dbf' size 25m
3 /
Tablespace created.
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> drop table t;
drop table t
*
ERROR at line 1:
ORA-00942: table or view does not exist
ops$tkyte@ORA9I> create table t tablespace test
2 as
3 select * from all_objects where rownum=1;
Table created.
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> create index t_idx on t(object_name,object_type,owner,object_id) tablespace test;
Index created.
ops$tkyte@ORA9I> pause
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> select file#, unrecoverable_change#, unrecoverable_time
2 from v$datafile
3 where file# in ( select file_id from dba_data_files where tablespace_name = 'TEST' )
4 /
FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
-------------------- --------------------- --------------------
65 0
ops$tkyte@ORA9I>
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> select index_name, logging from user_indexes where table_name = 'T';
INDEX_NAME LOG
------------------------------ ---
T_IDX YES
ops$tkyte@ORA9I> alter index t_idx rebuild tablespace test NOLOGGING;
Index altered.
ops$tkyte@ORA9I> select file#, unrecoverable_change#, unrecoverable_time
2 from v$datafile
3 where file# in ( select file_id from dba_data_files where tablespace_name = 'TEST' )
4 /
FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
-------------------- --------------------- --------------------
65 0
ops$tkyte@ORA9I>
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> select index_name, logging from user_indexes where table_name = 'T';
INDEX_NAME LOG
------------------------------ ---
T_IDX NO
ops$tkyte@ORA9I> alter index t_idx rebuild tablespace test;
Index altered.
ops$tkyte@ORA9I> select file#, unrecoverable_change#, unrecoverable_time
2 select index_name, logging from user_indexes where table_name = 'T';
select index_name, logging from user_indexes where table_name = 'T'
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
ops$tkyte@ORA9I> select file#, unrecoverable_change#, unrecoverable_time
2 from v$datafile
3 where file# in ( select file_id from dba_data_files where tablespace_name = 'TEST' )
4 /
FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
-------------------- --------------------- --------------------
65 0
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> drop tablespace test including contents and datafiles
2 /
Tablespace dropped.
<b>do it for a tiny object and it was actually logged....</b>
so, you just have too small of a test case.
Tablespace and Table Logging
Richard, May 26, 2004 - 6:23 am UTC
Hi,
Using OEM (9.2.0.1.0), I recently viewed details for a table. The table is in NOLOGGING mode, however the tablespace that the table is in is in LOGGING mode. Will redo be generated for the table? i.e. does the tablespace's mode override the table's, vice versa or what?
Many thanks in advance.
May 26, 2004 - 8:20 am UTC
tablespace attributes are simply attributes that objects created in that tablespace will inherit.
tablespace attributes do not affect already existing objects (eg: alter tablespace foobar nologging will NOT affect the logging/nologging status of the objects already in there).
Only the attribute of the segment actually counts for that segment.
In this case, the table is in NOLOGGING mode.
very nice table for dispaly all situations of logging/nologging
Pauline, May 26, 2004 - 12:45 pm UTC
A reader, May 26, 2004 - 2:02 pm UTC
Tom,
I followed sample you showed us but couldn't see information
updated in UNRECOVERABLE_CHANGE# and UNRECOVERABLE_TIME from v$datafile. I even insert table much more rows than you. Please see below:
SQL> create tablespace test
2 datafile '/db/db02/test.dbf' size 150m;
Tablespace created.
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table t tablespace test
2 as
3 select * from all_objects ;
Table created.
SQL> insert into t select * from t;
36395 rows created.
SQL> /
72790 rows created.
SQL> /
145580 rows created.
SQL> /
291160 rows created.
SQL> create index t_idx on
2 t(object_name,object_type,owner,object_id) tablespace test;
Index created.
SQL> select file#, unrecoverable_change#, unrecoverable_time
2 from v$datafile
3 where file# in ( select file_id from dba_data_files where tablespace_name = 'TEST' );
FILE# UNRECOVERABLE_CHANGE# UNRECOVER
---------- --------------------- ---------
4 0
SQL> select index_name, logging from user_indexes where table_name = 'T';
INDEX_NAME LOG
------------------------------ ---
T_IDX YES
SQL>
SQL> alter index t_idx rebuild tablespace test NOLOGGING;
Index altered.
SQL> select file#, unrecoverable_change#, unrecoverable_time
2 from v$datafile
where file# in ( select file_id from dba_data_files where tablespace_name = ' 3 TEST' );
FILE# UNRECOVERABLE_CHANGE# UNRECOVER
---------- --------------------- ---------
4 0
SQL> select index_name, logging from user_indexes where table_name = 'T';
INDEX_NAME LOG
------------------------------ ---
T_IDX NO
SQL> alter index t_idx rebuild tablespace test;
Index altered.
SQL> select file#, unrecoverable_change#, unrecoverable_time
2 from v$datafile
where file# in ( select file_id from dba_data_files where tablespace_name 3 = 'TEST' );
FILE# UNRECOVERABLE_CHANGE# UNRECOVER
---------- --------------------- ---------
4 0
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
What could be wrong on myn site?
Thanks
May 26, 2004 - 4:37 pm UTC
you are in noarchivelog mode, meaning "logging/nologging" isn't "relevant".
you are not supporting media recovery -- anything that doesn't need to be logged for instance recovery isn't.
in archivelog mode, you generate more redo simply to make sure we can recover from media failure.
Dump Question
A reader, February 16, 2005 - 8:30 pm UTC
Tom,
I have the following dump question :
1. There is no relation between NOLOGGING and UNDO. Right?.
ie. what I mean is that if I perform certain operations on the table/index using NOLOGGING mode that does not generate REDO, there will still be UNDO generated for the changes. Right?
2. You mention that some operations can be performed in NOLOGGING mode that generates less REDO. The data dictionary changes are always logged even in NOLOGGING mode and the redo is to protect the data dictionary. Is this REDO generated on data dictionary as part of the undo protection mechanism?
3. If my understanding on 2 is correct, you also mentioned in one of the discussion threads that if we use an INSERT using the /*+ APPEND */ hint, no UNDO will be generated. Does this mean the UNDO for changes to data dictionary (and the corresponding REDO that protects the undo) will still be generated ?
In such a situation, if the operation fails in the middle of the INSERT, how can we rollback?
4. How is REDO, ROLLBACK and NOLOGGING work together?
I would be very thankful to you if you can clarify my doubts
February 17, 2005 - 7:25 am UTC
1) not directly. I mean they are sort of linked in the recovery of "data". In order to rollback a failed transaction -- we need undo, in order to recover data after a system failure we need undo. So, in as much as they are both related to "being able to fix stuff"....
but they are not directly related. I can have a tranaction not generate undo for my changes, yet be logged. I can have a transaction not generate undo and not generate redo.
However, if I generate undo -- it'll be logged regardless.
2) no, redo is redo, undo is undo. It is true that undo generates redo, but the change to the dictionary will generate redo for the change to the dictionary and redo for the undo for the change to the dictionary.
3) no undo for the TABLE (indexes on table, undo, changes to dictionar, undo). We rollback by undoing the changes to the dictionary tables. Since append grabs space ABOVE the high water mark exclusively, all we need to do upon rollback is "leave the high water mark where it was to begin with". It is as if we never did the insert append.
4) do you have "Expert One on One Oracle"? I think I cover it rather nicely in a chapter or two (takes a bit of talking to tell the entire story). The short story: undo is used to undo changes. redo is used to redo them. To recover an instance, we roll forward with redo -- which rolls UNDO forward as well. Then we look around and say "so, what transactions were in process when we crashed, let's roll them back now"
Clarification
A reader, February 17, 2005 - 9:16 am UTC
Tom,
1. In response the discussion above, for 2) you mentioned "...It is true that undo generates redo, but the
change to the dictionary will generate redo for the change to the dictionary and redo for the undo for the change to the dictionary.". THis is what I meant by undo protection mechanism. Is the term i used to describe not correct? Pls advise.
2. In response to discussion 3) above, you mentioned "no undo for the TABLE (indexes on table, undo, changes to dictionar, undo)". I mean to understand this :
A. No UNDO will be generated for the table (and as a result no associated redo to protect the UNDO)
B. UNDO will still be generated for the data dictionary changes and in case if the table has any indexes (and the associated redo as well to protect the UNDO). Is this what you mean by "minimal redo...". Please confirm my understanding.
Thanks much
February 17, 2005 - 10:35 am UTC
1) now I'm confused. You said "is this redo part of the undo protection mechanism".
I said
"some of the redo is the redo for the modification to the dictionary" (not part of the "undo protection mechanism)"
"some of the redo is for undo"
so the answer is "no, all of the redo is not for undo, some of it is, some of it is not"
2a - correct,
2b - correct
CTAS vs INSERT..SELECT
A reader, August 30, 2005 - 5:29 pm UTC
In an ARCHIVE LOG datatabase,
What is the difference between CREATE TABLE ABC NOLOGGING as select * from all_objects;
AND
create table abc nologging as select * from all_objects
where 1=2;
insert into abc
select * from all_objects;
My first step (CTAS) created a table with 18Million records in 8 minutes and my select option is still going on even after 40 minutes.
Thanks
August 31, 2005 - 5:02 am UTC
the first one created the table without generating redo. therefore, you need to schedule a backup of that tablespace as soon as possible.
The second one generated redo, since conventional path inserts always generate redo. You would have to use insert /*+ append */ to by pass redo generation on a segment that has nologging enabled (and then backup) - BUT remember that indexes will ALWAYS generation redo and undo regardless of the type of insert performed (you can only bypass redo/undo on the index during a build or rebuild type of operation)
CTAS
A reader, August 31, 2005 - 11:09 am UTC
However, create table test_logging tablespace data_users
logging as select * from order_detail;
took 15 minutes to create the table (with logging option).
CTAS with NOLOGGING took 8 minutes for the same amount of data.
INSERT..SELECT on a NOLOGGING table took 1hr 38 minutes and did not complete.
INSERT /*+ APPEND */ .. SELECT on a NOLOGGING table took more than 25 minutes and I killed it later.
In all the cases except CTAS with LOGGING option, there was no growth in the ARCHIVELOG files.
Thanks,
August 31, 2005 - 1:59 pm UTC
compare the plans.
CTAS
A reader, September 01, 2005 - 7:37 am UTC
CTAS did not generate any plans although I set autotrace on.
September 01, 2005 - 9:15 am UTC
ops$tkyte@ORA10GR1> explain plan for &1;
old 1: explain plan for &1
new 1: explain plan for create table t as select * from dual
Explained.
ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1501550473
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 |
| 1 | LOAD AS SELECT | | | | | |
| 2 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
9 rows selected.
from sweden
A Reader, September 27, 2005 - 1:32 pm UTC
Hi Tom,
From the thread above I understand that keeping objects in nologging mode cannot be recocerved in case a media recovery is required in an event of a faliure. And involves a rework. But will it make a big difference in case of indexes ? considering that we can create them using a script in case of faliure.
Please clearify
1) Will it be a big risk to keep some indexes (NO tables) in nologging mode in a production system?
2) Is it like the related Index tablespace file(s) cannot be recovered at all if some of then are in nologging mode? I think there should be NO problem as far as recovery is concerned except that index stats would not be up to date after recovery. Is this correct?
It would be great if you can list the possible risk/disadvantages of keeping indexes in nologging mode in production database.
Thanks as always
September 27, 2005 - 2:03 pm UTC
1) depends, can you afford to rebuild them somehow else if you've done a non-logged operation on them and don't have a backup taken AFTER that operation? Do you run a standby database, one that works off of redo? Are you using redo based replication???
Only you can tell....
2) just the non-logged segment is affected, you could have one non-logged segment in a file with 1,000 other logged segments - the 1,000 logged segments are full recoverable, the non-logged one *might* not be.
I see no reason to keep indexes in non-logged. Only bulk operations like CREATE INDEX, ALTER INDEX REBUILD are affected and you are not doing those often and if you do, you can just alter the index nologging - rebuild, BACK IT UP, and set it non-logging again.
from sweden
reader, September 27, 2005 - 3:06 pm UTC
1) yes, we can rebuild them using pre created script, as anyway we will be keeping a note of those few indexes that we'll change to NOLOGGING. It will take hours, I'm aware of that. But it should be acceptable as failure is a rare event.
No, there is no standby database.
Hot backup is taken every week and bitmap index rebuild is done everyday (after data loading). And I'm sure it will not be acceptable to take a backup everyday after rebuild.
In that case I'll be asked to find other ways to rectify the performance problem while (bitmap) index rebuild.
2) What will be the implication in case we are not able to recover that non-logged segments ?
will there be performance issues or an ORA error be thrown while accessing those indexes ?
as momentory and one time performance problem is acceptable but NO ERRORS at all.
September 27, 2005 - 3:16 pm UTC
You only need to backup the affected datafiles, not the entire database.
Is it acceptable to be down for hours or longer to perform a recovery? If the system fails on Saturday, you have Sun-Sat to apply... could take a while, if you have a relatively high transaction volume you might rethink your backup stategy.
You get ora-xxxx errors when accessing them, bad blocks. The data is by *DEFINITION* unrecoverable, gone, poof, not there. You have to recreate it.
TY, February 21, 2007 - 3:52 pm UTC
Looking through this example,I have a particular select query which takes 2 minutes (to the end of the records not just the FIRST ROWS) to execute but when I do a CTAS (using nologging or otherwise)it takes more then 2 hours to come by ...Insert into as select also takes up more then 2 hours...
All the tables are analyzed and up-to-date.
This is for a particular table and not repeated for all the CTAS.
It is very puzzling to me as I never encountered such a scenario before....
Unrecoverable...
Rich, September 13, 2007 - 4:00 pm UTC
Hi Tom,
Still, few things are unclear to me. What about this scenario (version is 10.2.0.3), would the data be recoverable in case of media failure (after restore and recover)?
1- Create table x nologging...
2- Insert Tons of data in x...
3- Alter table x logging...
4- Media Failure
5- Restore and recover...
September 15, 2007 - 7:47 pm UTC
no, the data is not recoverable. You did not backup X (the datafiles holding X) after you performed a non-logged operation.
I'm assuming here that
1) create table x nologging AS SELECT (some data) <<<== this data would be lost after 4
2) insert /* without any append hints */ into t select ... <<= this data WOULD be recoverable, but the table X would be trashed since you lost the stuff from 1!!!
3) alter <= not relevant here..... doesn't do anything in this example
4) media failure, everything from #1 is gone
now, if you:
1) create table x ( columns.... ) nologging;
2) insert (without append)
then everything is in the log and you are OK.
Karthick Pattabiraman, January 22, 2008 - 2:21 am UTC
After going throught this thread and couple of others and after a look into the doc i came up with this. This is kind of what i understood and i what was not clear to me.
CASE 1:
INSERT INTO TABLE X SELECT ...
1. Write in Redo log buffer
ON COMMIT
1. LGWR writes Redo log buffer info into Redo Log file.
ON ROLLBACK
1. Question (Is redo log buffer written into Redo log file by LGWR)
CASE 2:
TABLE X IS IN LOGGING MODE
INSERT /*+ APPEND */ INTO TABLE X SELECT ....
1. LGWR writes Redo log buffer info into Redo Log file.
ON COMMIT
1. Question (Does any thing happens with respect to Redo Log at this point)
ON ROLLBACK
1. Question (Is the information writtern into Redo Log file is retained)
CASE 3:
TABLE X IS IN NOLOGGING MODE
INSERT /*+ APPEND */ INTO TABLE X SELECT ....
1. LGWR do not writes Redo log buffer info into Redo Log file.
ON COMMIT
1. Nothing happens with respect to Redo Log
ON ROLLBACK
1. Nothing happens with respect to Redo Log
January 22, 2008 - 7:10 am UTC
case 1 - you insert, this generates redo.
what happens next could be one of many things...
for example, you could pause for a moment, and LGWR will flush the redo log buffer to disk (your redo gets written)
AND THEN - you issue commit or rollback. If you issue commit, LGWR will write your commit record to the redo stream and you are done. If you rollback, you will read your UNDO records - process them (generating YET MORE redo, which lgwr will eventually write to disk) - and then when you are done, you'll flush your redo and be finished.
case 2 - direct path insert
see case 1, no different.
case 3 - direct path insert into nologging table
this transaction still generates REDO, the redo is for our dictionary tables and that will be processed as in case 1 again.
They are all the same, the amount of redo generated will differ, but they are all the same.
Karthick Pattabiraman, January 22, 2008 - 8:57 am UTC
Ok got you.
It doesn¿t matter for redo whether it¿s a commit or rollback. In both the case it will be logging as the purpose of redo is to help us out to recover in the case of disaster.
Once again thank you.
A reader, August 29, 2008 - 12:40 am UTC
Dear Tom.
You wrote:
Table Mode Insert Mode ArchiveLog mode result
----------- ------------- ----------------- ----------
NOLOGGING APPEND noarchive log mode no redo
generated
I am in 9iR2.
Just simple insert into table which has no any indexes on it and it generates redo in both case:
1. APPEND hint
2. NO APPEND hint.
even I changed table to nologging mode. the same result.
pls, let me know what can be wrong.
Thansk
August 29, 2008 - 11:15 pm UTC
indexes always, always, always generate redo during any insert, any - any - any insert.
We were talking only about tables.
insert /*+ append */ - since it is done in bulk will MINIZE the redo, but it'll generate redo for the indexes *always* if the index is maintained.
tablespace in nologging mode
David, February 14, 2009 - 11:35 am UTC
I placed application related data/index tablespaces in NOLOGGING. However, when I imported schemas using oracle imp utility, it still generated 250GB of archive log files. The database is in ARCHIVELOG mode though. Is this expected? I thought although the database is in ARCHIVELOG mode, I could override it in tablespace level. I also checked to ensure that database is NOT set to force_logging. Thanks.
February 16, 2009 - 12:13 pm UTC
yes, it is absolutely expected.
read the first link above in the original answer - way up top where I first said "see also"
Indexes remaining in nologging mode
Uwe M. Kuechler, June 18, 2009 - 11:05 am UTC
Hi Tom,
further up you stated:
"I see no reason to keep indexes in non-logged. Only bulk operations like CREATE INDEX, ALTER INDEX REBUILD are affected [...]", and I also acknowledge your demonstration of indexes generating redo in non-bulk operations although the nologging attribute is set.
Just to double-check: Can I assume that there will be no data blocks marked corrupt at recovery, when the index is in nologging mode but no bulk operation has happened on it since the last full backup?
Best regards,
Uwe
June 18, 2009 - 3:25 pm UTC
if you have a backup of a datafile and have not performed any non-logged operations on that datafile since the backup, you are "ok, covered, nothing will be corrupt due to non-logged operations" after restoring that file and applying archives/online redo to recover it.
nologging
Deba, June 23, 2009 - 11:13 am UTC
Hi Tom,
I would like to know nologging in respect of recovery.
Case 1: I have one table in nologging mode. Now I insert data in direct mode insert. Now if databases crashes then what will happen ?
a) recovery process will success but corrupt the object
b) recovery process will be stopped when it will come to this point.
Case 2: I have one table in nologging mode. Now I insert data in conventional insert mode. If database crashed here then there should not be any problem with recovery and object will not be corrupted. Am I right ?
Case 3: I have one table in logging mode. Now I insert data in direct mode insert. Now if the database crashes , there should not be any problem with recovery right ?
I am in 10g.
Thanks
Deba
June 26, 2009 - 9:13 am UTC
a direct path insert writes directly to the datafiles, if the instance crashes - the data is already on disk, it is protected.
nologging only affects MEDIA recovery - not "database crash" recovery.
nologging
Deba, June 24, 2009 - 10:51 am UTC
Hi Tom,
Could you please answer my above thread , if possible ? I know you are very busy . But it will help me to clear my doubt.
Thanks
Deba
nologging
A reader, August 04, 2009 - 5:40 am UTC
Hi Tom,
If I have hot backs running, and I have process, which first load data in to
1) Staging tables
2) Work table, and
3) Fact table.
The staging tables are built from source, and work table is then loaded (truncate/load) by the process by reading from staging.
If I create 2 above in NOLOGGING mode, and media recovery is required, then I can rebuild work table from staging again. Do you see any issue with NOLOGGING of work table?
Thanks
August 04, 2009 - 3:11 pm UTC
hot backs? I presume you mean hot backups.
as long as you are ready and willing to rebuild anything from scratch that is in nologging mode upon any media failure - no problem.
NOLOGGING for indexes
A reader, August 05, 2009 - 6:32 am UTC
Hi Tom,
What is the purpose of NOLOGGING for index, as indexes can always be rebuilt? Can this help to speed up the loading of large volume of data?
Thanks
August 05, 2009 - 9:34 am UTC
the purpose of nologging (for any segment) is to permit us to skip the generation of redo during a direct path (bypass buffer cache) operation.
create table as select - direct path
insert /*+ append */ into t select... - direct path
alter index i rebuild - direct path
create index i - direct path
certain operations, performed in direct path mode, can bypass redo generation. You must be aware that a backup must be scheduled if you want to be able to recover these segments.
Can it speed up loading of large volume of data?
Yes
No
Maybe
Yes it can. You can direct path load an empty table (with nologging) and then create indexes (no logging) on it. This will likely be faster than conventional path inserts and maintaining the index during the load.
No it won't. You are loading a large volume of data (1,000,000 rows), but you are doing it into a table that has 100,000,000 rows already and is indexed. It would likely be faster to direct path load the table BUT MAINTAIN THE INDEXES in real time rather than drop/unusable the indexes and rebuild/create them afterwards.
Maybe - take the above two cases and think about situations where the numbers are different - instead of 0 and 100,000,000 rows - and you are loading 1,000,000 rows - play with different numbers in your head and think about the amount of work that would need be performed in each case.
Undo tablespace issue in Update
Karthi, August 20, 2009 - 8:45 am UTC
Hi Tom,
I have executed one update statement and faced the UNDO table space issue in the production (ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_RBS1').
I have given the results and difference between the production environment and development environment below.
Update script:
----------------
UPDATE PO_ACC_REN A
SET
(ACCOUNT_NBR , GL_STRING , SUBACCOUNT_NBR , loc_cd , cost_center_cd )=
(SELECT SEGMENT2 , trim(segment1)||'-'||
trim(segment2)||'-'||
trim(segment3)||'-'||
trim(segment4)||'-'||
trim(segment5)||'-'||
trim(segment6)||'-'||
trim(segment7) ,
segment3 ,
segment7 ,
segment4
FROM GL_CODE_COMB_ST B WHERE A.ACCRUAL_ACCOUNT_ID = B.CODE_COMBINATION_ID)
where write_off_flag = 'N';
Record count details in both the tables:
----------------------------------------
select count(*) from PO_ACCRUAL_RECON_TEMP_ALL_MV
1392942
select count(*) from GL_CODE_COMBINATIONS_ST
636515
Tom's Table:
------------
Table Mode Insert Mode ArchiveLog mode result
----------- ------------- ----------------- ----------
LOGGING APPEND ARCHIVE LOG redo generated
NOLOGGING APPEND ARCHIVE LOG no redo
LOGGING no append "" redo generated
NOLOGGING no append "" redo generated
LOGGING APPEND noarchive log mode no redo
NOLOGGING APPEND noarchive log mode no redo
LOGGING no append noarchive log mode redo generated
NOLOGGING no append noarchive log mode redo generated
Production Environment:
Result -> Query took very long time and filled the UNDO (Usage is 100%).
Archive mode -> Archive mode is enabled
Index mode -> all the indexes are in the NOLOGGING mode.
The GL_STRING field is having the index and update statement is updating the data in the field.
I am trying to relate to above table.
Table Mode Update_stmt_Mode ArchiveLog mode result
---------- ---------------- --------------- ----------
LOGGING no append ARCHIVE LOG redo generated
Development Environment:
Result -> Query completed in few minutes and the UNDO usage is less only. That is not equal to 100%.
Archive mode -> It is in No archive mode
Index mode -> all the indexes are in the NOLOGGING mode.
The GL_STRING field is having the index and update statement is updating the data in the field.
Table Mode Update_stmt_Mode ArchiveLog mode result
---------- ---------------- --------------- ----------
LOGGING no append noarchive log redo
mode generated
Question:
Q1: in both the situation it is genrating the Redo enreies. I have not received issue in the development server but I got error in the production. could you please explain me why I am getting this error.
Production Environment:
After this I have disabled the indexes in the GL_STRING field and executed the same update statement. It has completed with in few minutes.
Please explain me how come it is completing with out any issue. please let me know if I need to give any other information for the analysis.
Regards,
Karthi
August 24, 2009 - 5:13 pm UTC
... Index mode -> all the indexes are in the NOLOGGING mode. ...
not relevant, only affects CREATE and REBUILD operations, never ever DML
as for the rest of the information, not sure what I'm looking at. can you distill down to the relevant facts only - I have no idea what "tom's table" is.
REDO Size.
Rajeshwaran, Jeyabal, September 06, 2009 - 10:46 am UTC
rajesh@IRADSDB> select log_mode
2 from v$database;
LOG_MODE
------------
NOARCHIVELOG
rajesh@IRADSDB> select dbms_metadata.get_ddl('TABLE','T') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
CREATE TABLE "RAJESH"."T"
( "OWNER" VARCHAR2(30) NOT NULL ENABLE,
"OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER NOT NULL ENABLE,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE NOT NULL ENABLE,
"LAST_DDL_TIME" DATE NOT NULL ENABLE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING <<<=========== (Table is in NOLOGGING mode)
STORAGE(INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TS_IRADS_RPT_D"
rajesh@IRADSDB> insert into T select * from all_objects;
55267 rows created.
Statistics
----------------------------------------------------------
6396 recursive calls
7545 db block gets
96272 consistent gets
0 physical reads
6269384 redo size <<<========== (Nearly 6MB of Redo Files generated)
1775 bytes sent via SQL*Net to client
1143 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
55267 rows processed
rajesh@IRADSDB> insert /*+ append */ into T select * from all_objects;
55267 rows created.
Statistics
----------------------------------------------------------
6263 recursive calls
1469 db block gets
94922 consistent gets
0 physical reads
59432 redo size <<<========== (only 0.05 MB of Redo Files generated)
894 bytes sent via SQL*Net to client
958 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
55267 rows processed
rajesh@IRADSDB> alter table T LOGGING;
Table altered.
rajesh@IRADSDB> insert /*+ append */ into T select * from all_objects;
55267 rows created.
Statistics
----------------------------------------------------------
6050 recursive calls
1397 db block gets
93760 consistent gets
0 physical reads
56888 redo size <<<========== (only 0.05 MB of Redo Files generated)
895 bytes sent via SQL*Net to client
959 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
55267 rows processed
Tom,
A Quote from " Effective Oracle By design "
<quote>
A direct-path insert will only bypass redo generation in two cases:
POINT : 1) You are using a NOARCHIVELOG mode database (my database is in ARCHIVELOG mode).
POINT : 2) You are performing the operation on a table marked as NOLOGGING.
</quote>
Question
1) Always the POINT : 1 & POINT : 2 must be TRUE to reduce the amount of Redo Size. Is that correct ?
2) If the above Statement is correct then why modifiying the table to LOGGING mode still suppressed the REDO Size being generated to 0.05 MB?
3) So, Please tell me is that always the POINT : 1 be TRUE & POINT : 2 (dont care about this if POINT 1 is True) will reduce the REDO Size in BULK Loading?
September 07, 2009 - 1:50 am UTC
1) ... Always the POINT : 1 & POINT : 2 must be TRUE to reduce the amount of Redo Size. Is that correct ? ...
you just demonstrated that - NO, it isn't true. You just demonstrated that, you empirically demonstrated that noarchive log mode plus direct path is sufficient.
2) because you just demonstrated noarchive log mode plus direct path is sufficient - we didn't need to generate any redo since we wrote the blocks directly to disk. It would only be necessary in an archivelog mode database to use nologging.
3) forget the word bulk, think "direct path". direct path operations (create, alter move, insert /*+ APPEND */, sqlldr direct=y) can skip redo generation in a noarchivelog mode database.
REDO Size in ARCHIVELOG mode.
Rajeshwaran, Jeyabal, September 07, 2009 - 4:31 am UTC
scott@10G> select log_mode
2 from v$database;
LOG_MODE
------------
ARCHIVELOG
scott@10G> create table T as select * from all_objects where 1 =0;
Table created.
scott@10G> INSERT INTO T SELECT * FROM ALL_OBJECTS;
50764 rows created.
Statistics
----------------------------------------------------------
6636 recursive calls
6958 db block gets
85586 consistent gets
2 physical reads
5735240 redo size
905 bytes sent via SQL*Net to client
944 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
50764 rows processed
scott@10G> SELECT DBMS_METADATA.GET_DDL('TABLE','T') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "OWNER" VARCHAR2(30) NOT NULL ENABLE,
"OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER NOT NULL ENABLE,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE NOT NULL ENABLE,
"LAST_DDL_TIME" DATE NOT NULL ENABLE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
scott@10G> INSERT /*+ APPEND */ INTO T SELECT * FROM ALL_OBJECTS;
50764 rows created.
Statistics
----------------------------------------------------------
5628 recursive calls
924 db block gets
84067 consistent gets
0 physical reads
5763352 redo size
891 bytes sent via SQL*Net to client
958 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50764 rows processed
scott@10G> ALTER TABLE T NOLOGGING;
Table altered.
scott@10G> SET AUTOTRACE TRACEONLY STATISTICS;
scott@10G> INSERT /*+ APPEND */ INTO T SELECT * FROM ALL_OBJECTS;
50764 rows created.
Statistics
----------------------------------------------------------
5852 recursive calls
894 db block gets
84119 consistent gets
0 physical reads
19816 redo size
1758 bytes sent via SQL*Net to client
1157 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
50764 rows processedTom,
Thanks, Now I did the same in ARCHIVELOG mode and i got it.
In ARCHIVELOG mode having the table as NOLOGGING can suppress the REDO File generations with DIRECT Path Loading.
Standby + nologging
lalu., December 16, 2009 - 11:14 pm UTC
Hi Tom,
I tried altering a table to nologging with standby in place and it went successful.
All DML and ddl are taking place in standby.
Force logging is not enabled in the system.
Is it the expected behaviour?
Thanks.
December 17, 2009 - 7:39 am UTC
well, you might well NOT have done something in nologging format - you don't tell us what you did. A great deal of confusion exists surrounding nologging.
So, my first guess: you didn't do a non-logged operation after all
My second guess: you did, and you just don't know that what you have on the standby is whacked because you haven't switched over and tested it.
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#i1015738
nologging
A reader, September 19, 2010 - 2:49 pm UTC
Hi Tom,
In your book "Expert Oracle Database Architecture", you have told regarding Nologging: "We wouldn't actually lose the subsequent changes, as these are in redo log; we would lose the data to apply this changes to.".
I find this as confusing to me. I thought that NOLOGGING clause only generates redo log to protect the data dictionary only. And I also thought that Redo in LOGGING mode generally records all the data changes made prior and after the operation. Then what do you mean by subsequent changes in redo log in NOLOGGING clause? Aren't subsequent changes in redo containing data this time? Please clarify.
Thank you
September 20, 2010 - 2:26 pm UTC
if you insert /*+ append */ into a table using NOLOGGING
then
the data you just loaded generates no redo log. If you lose a file now, you cannot restore from backup, you will not have your newly loaded records
end if;
over time however, as you UPDATE these rows - we do generate redo for those modifications. if over time you generated ENOUGH redo to "cover everything" - that is, if you changed everything you just loaded, we'd actually be able to recover that since we'd have the redo to redo those changes.
You don't lose the subsequent changes to that data you loaded "nologging", that redo is always generated. What you do lose is the redo for the newly loaded data - and only that redo.
but - that redo is really really important. In general, if you load nologging and suffer a media failure, you will have LOST that data.
Nologging
Arindam, September 20, 2010 - 11:03 pm UTC
Hi Tom,
Thanks. I think I got it.
That means If I load a data with nologging clause, it will have no redo generated with it. Now if I change the loaded data, it is recorded in the redo log. But when media failure happens, I cannot recover the data (even if I committed the change),since there is no data details recorded in the redo log during loading. Do you mean this only?
September 21, 2010 - 3:41 pm UTC
Pretty much - when media failure happens, assuming no backup SINCE the direct path load - you will have some redo for that segment (for the modifications you made) but probably (almost certainly) not ENOUGH redo to recover the entire segment - you will have most likely lost that segment.
Is the table still valid?
A reader, October 19, 2010 - 1:05 pm UTC
Tom,
The table you drew in the very first answer to this thread, is this table still valid in 10g? I am not running with force logging.
Thanks...
October 25, 2010 - 10:26 am UTC
yes
Impact on UNDO clear anywhere
SN, December 11, 2010 - 11:17 am UTC
Tom,
This thread explains the impact of nologging in various permutations, however, it's not explaining the impact on undo clearly. It would be much more clearer if you could add the impact on the undo in a tabular just like how you did for the very first poster.
Thanks,
SN
December 11, 2010 - 2:36 pm UTC
nologging has nothing to do with undo generation. undo is about undo, nologging is about redo.
direct path operations such as:
create table as select;
create index;
insert /*+ append */ into t (and ONLY regarding the table, not the indexes)
sqlldr direct=y
alter table t move;
and other DDL type of operations such as
truncate table t;
do not generate UNDO - regardless of the logging mode. All conventional path operations such as
insert (without append)
update
delete
merge
always generate undo - regardless of the logging mode.
logging = redo
logging <> undo
Undo Generation
A reader, December 11, 2010 - 2:54 pm UTC
Hi Tom,
Can you expand on
insert /*+ append */ into t (and ONLY regarding the table, not the indexes)? What is the rational behind oracle generating undo for indexes?
Thanks
December 12, 2010 - 8:07 am UTC
The undo will be minimized for the index but has to be generated because you ultimately end up merging into a larger structure.
Let's say you have an existing table T (must be true, you are using insert).
Let's say it has an index on some column (must be true, else we wouldn't have this discussion)
You insert /*+ append */ into t select * from t2;
The rows are written above the high water mark (HWM) of the table. In order to "rollback" from this change - all we need to do is RESET the high water to where it was before the insert append and the table is back to normal.
But - think about the index. When we do the insert append - Oracle will load the table data above the HWM and it will create "mini indexes" for the newly loaded data off to the side (no redo, no undo for this operation so far - all done in temp). After the insert append loads all of the rows - we need to take these mini indexes (which could be huge in real life) and merge them into the existing index structure - merge INTO the EXISTING index. What if after the merge - where we combined the new index keys with the existing ones - the application issues "rollback"? We can easily rollback the table load - just reset the HWM. But now we have to "unmerge" the new index keys - that is not possible unless we have the UNDO for the index.
hence, we need undo for the indexes - we need no undo for the table. Note that the undo generated for the index is optimally "small" since we merge in bulk - not slow by slow (row by row)
A reader, December 12, 2010 - 9:43 pm UTC
Hello Tom,
"direct path operations such as:
create table as select;
create index;
insert /*+ append */ into t (and ONLY regarding the table, not the indexes)
sqlldr direct=y
alter table t move;
and other DDL type of operations such as
truncate table t;
do not generate UNDO - regardless of the logging mode."
what I understood is the above direct path operation produce minimal redo and undo to protect changes against data dictionary and not for the user data or am i missing something?
Thanks...
December 13, 2010 - 2:08 am UTC
direct path operations skip undo.
direct path operations have the opportunity to bypass redo as well - see the original answer way up top - there is a table there.
direct path operations tend to minimize the amount of undo generated when it is generated since they skip undo - and we don't have to generate the redo for that undo we don't generate. Also, for operations like index maintenance during a direct path insert (insert append) - the redo generated tends to be less because of the big bulk merge that takes place at the end.
Excellent explanation!
A reader, December 13, 2010 - 7:23 am UTC
Thanks much for the good explanation Tom!
-SN
ddl and redo log
A reader, January 12, 2011 - 11:09 am UTC
Hi Tom,
Does DDL command execution recorded in Redo Log? Is that the only reason why commit is not required for that? Do creating large table and small table take same amount of time?
January 12, 2011 - 11:41 am UTC
The *effects* of the DDL are recorded - any modifications to the data dictionary definitely are. The actual DDL might not be (depends on supplemental log settings) but the *effects* absolutely are.
It has nothing to do with the fact that DDL "auto-commits" however. DDL is executed like this:
begin
commit;
begin
do the ddl;
commit;
exception
when others
then
rollback;
RAISE;
end;
DDL is done that way so that is "finishes" your transaction by committing first and then it does its work and if successful - commit -else rolls back everything.
Of course creating a large table (by large I presume you mean "has lots of data intially") will take longer than creating a small table. There is obviously more work to be performed for the 'large' table.
DDL
Reviewer, January 12, 2011 - 12:41 pm UTC
Hi Tom,
If there is resource manager which has limits on CPU usage, and a user is creating a table which needs more CPU, what happens i.e., does it wait for resource or fails?
Thanks
ddl command
A reader, January 12, 2011 - 9:22 pm UTC
Hi Tom,
You have said, DDL is done that way so that is "finishes" your transaction by committing first and then it does its work and if successful - commit -else rolls back everything.
Why there is commit at the beginning of DDL work?
Also if I want to create a blank table with lots of column in it, will it take more time if compared with creating blank table with smaller number of rows?
January 13, 2011 - 7:18 am UTC
Why there is commit at the beginning of DDL work?
because we want to finish YOUR unit of work. Otherwise, we might roll back your work if the DDL fails. So, to make it predicable and consistent - we
a) commit
b) do the ddl
c) if success commit the ddl else rollback the ddl
will it take more time
It will take nominally more time as there will be more work performed against the data dictionary. In the grand scheme of things, in general - since you don't create tables often - it won't really impact anything.
A reader, November 10, 2011 - 11:09 am UTC
How to avolid the Redo generations for Mview refresh.
vinodh G R, July 16, 2013 - 7:12 am UTC
HI Tom,
In our application we have 5 mviews which are refreshing every 3 min, these refreshes are generating the huge redos. Is there any way to avoid the redos for Mview refresh.
More ever all 5 Mviews are having the indexes.
Thanks,
vinod G R
July 16, 2013 - 4:57 pm UTC
not unless you want the MV's to "disappear" during the refresh. But then again, even if you do - the indexes will generate redo no matter what.
are you doing incremental refreshes? How many rows are you applying here?
(in my opinion having so many MV's that refresh so quickly means.... you really mean to have one database, not two and you want to get rid of replication altogether and consolidate - everyone will be better for it)
M view refresh.
vinodh G R, July 17, 2013 - 6:01 am UTC
Hi Tom,
Thank you for details.
YEs we have only one database.
each mviews are of 4 to 6 million rows of data and we are doing the complete refresh.
Thanks,
July 17, 2013 - 6:05 pm UTC
why?
why in the world would you copy so much data so often in the same database????
Query on Basic Table for NoLogging & Append
Yuvaraj, January 31, 2014 - 10:54 am UTC
In the beginning of this discussion you have given table -
Table Mode Insert Mode ArchiveLog mode result
----------- ------------- ----------------- ----------
LOGGING APPEND ARCHIVE LOG redo generated
NOLOGGING APPEND ARCHIVE LOG no redo
LOGGING no append "" redo generated
NOLOGGING no append "" redo generated
LOGGING APPEND noarchive log mode no redo
NOLOGGING APPEND noarchive log mode no redo
LOGGING no append noarchive log mode redo generated
NOLOGGING no append noarchive log mode redo generated
Then it said for 9iR2 "Force Logging".
My Query is -
1. I am using 10g. Is this table still valid for 10g/11g.
2. If yes, I want to use APPEND hint for Insert statement.
Do I still need to mention NOLOGGING for table? I mean, only Append hint will help me 'not to archive' and Fast Insert Operation. (I don't need REDO)
I am referring line -
LOGGING APPEND noarchive log mode no redo
3. If NOLOGGING is still required along with APPEND, Do I need to mention NOLOGGING for all elements Table,Index(Bit Map), Partition etc. Or NOLOGGING on Table will be enough.
table logging index in nologging
Jorge Delgado, December 08, 2014 - 6:35 pm UTC
Hi,
when the tables are in logging mode and indexes are in nologging mode, what happen?
table logging, index logging = redo for table and index?
table logging, index nologging = redo just for the table?
If the indexes are in nologging mode the database has a better perfomance?
December 09, 2014 - 3:21 pm UTC
indexes are always in logging mode when you load into a able - always.
the only time they'll be able to skip logging is
a) when you create them
b) when you rebuild them
otherwise, they are logging. And a table will ONLY be able to skip logging when
a) you create them
b) you move them (reorg them)
c) you DIRECT PATH load into
logging is a consideration for RECOVERY, not so much about performance.
but in general, your indexes are always in logging mode (even if they say nologging)