Re: Create Index with nologging?

In article <9jsfpv$a73$1_at_spiney.sierra.com>, "Steve says...
>
>updates? you're correct.
>
>however, if I do
>
>create table ....
>
>create index ... no logging
>
>THEN DO an update, the update gets logged.
>
>If I then do
>
>insert into table
>select * from...
>
>the index changes are NOT logged.

Hold on -- they sure enough do. Definitely!

insert into table select * from ....

is treated NO differently then

insert into table values ( x,y,z )

I believe you might be thinking of:

insert /*+ APPEND */ into t select * from .....

which is a direct path insert and can by pass logging operations on the table....

HOWEVER, most of the operations on the index however will still be logged even with NOLOGGING on the index in this case.

Simple test case:

ops$tkyte_at_ORA817.US.ORACLE.COM> create table t NOLOGGING as select * from all_objects where 1=0;

Table created.

ops$tkyte_at_ORA817.US.ORACLE.COM> create index t_idx on t(object_name, owner, object_type, status, created ) NOLOGGING;

Index created.

ops$tkyte_at_ORA817.US.ORACLE.COM> select logging from user_tables where table_name = 'T';

LOG

---
NO

ops$tkyte_at_ORA817.US.ORACLE.COM> select logging from user_indexes where
index_name = 'T_IDX';

LOG
---
NO


So, that shows the logging for mode operations that can be "unlogged" is set to
NO LOG for the index and the table, now lets measure it:


ops$tkyte_at_ORA817.US.ORACLE.COM> set autotrace on statistics
ops$tkyte_at_ORA817.US.ORACLE.COM> insert into t select * from all_objects;

27029 rows created.


Statistics
----------------------------------------------------------
        209  recursive calls
      10880  db block gets
     236678  consistent gets
          0  physical reads
   12503284  redo size
       1017  bytes sent via SQL*Net to client
        820  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      27029  rows processed

ops$tkyte_at_ORA817.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte_at_ORA817.US.ORACLE.COM> insert /*+ APPEND */ into t select * from
all_objects;

27029 rows created.


Statistics
----------------------------------------------------------
        229  recursive calls
       8258  db block gets
     235786  consistent gets
        511  physical reads
    9367792  redo size
       1001  bytes sent via SQL*Net to client
        834  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          1  sorts (disk)
      27029  rows processed


As it turns out, the 9meg of log here is generated for the index -- we can see
this by droppping the index and doing the insert again:

ops$tkyte_at_ORA817.US.ORACLE.COM> drop index t_idx;

Index dropped.

ops$tkyte_at_ORA817.US.ORACLE.COM> insert /*+ APPEND */ into t select * from
all_objects;

27028 rows created.


Statistics
----------------------------------------------------------
        315  recursive calls
        104  db block gets
     234915  consistent gets
          0  physical reads
       8776  redo size
       1002  bytes sent via SQL*Net to client
        834  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
      27028  rows processed

ops$tkyte_at_ORA817.US.ORACLE.COM> set autotrace off
ops$tkyte_at_ORA817.US.ORACLE.COM> 


when we goto merge the new index pieces generated as a part of the load into the
greater index structure -- we must do that in a logging mode.







>>updates and insert into table values (...) get logged.

>bulk inserts (via select from) don't.

>

>"Guruju,Narendra" <NGURUJU_at_cerner.com> wrote in message

>news:D15ED542E12BD3119FFE00805F6551F00BE369D9_at_mailwhqnews.cerner.com...

>> Steve,

>> alter index nologging will work only during the create. It has no affect

>> once the index is created. So even with nologging option turned on

>> inserts, updates to the index would still be logged on.

>>

>> -----Original Message-----

>> From: Steve McDaniels [mailto:steve.mcdaniels_at_sierra.com]

>> Posted At: Tuesday, July 24, 2001 5:50 PM

>> Posted To: comp.databases.oracle.server

>> Conversation: Create Index with nologging?

>> Subject: Re: Create Index with nologging?

>>

>>

>> You know you can alter index logging (or nologging) as often as you

>> like.

>> It only affects operations _after_ the alter statement.

>>

>> import your data.

>> create index nologging

>> [don't forget parallel (degree N)]

>>

>> when indexing complete,

>> alter index logging

>>

>>

>> "Peter Stryjewski" <pstryjew_at_worldnet.att.net> wrote in message

>> news:3B5DED3B.F4F5365D_at_worldnet.att.net...

>> > Hello,

>> >

>> > I have searched the Deja (now Google Groups) archives and the Ask Tom

>> > page (how often does Tom take questions?) and found conflicting

 answers.


>> >



well, i take about 30 a day on a busy day.  3 batches of 10... I'm just one
guy....



>> > Scenario, Oracle 8.1.x:

>> >

>> > Export DB from original system (with direct option for speed)

>> > Move export file to new system

>> > Use "indexfile" to get table create and index create statements

>> > Create tables

>> > Import Data without index build

>> > ...

>> > Now create indexes.  This I would like to speed up!

>> >

>> > If I modify the index create statements, created by the "indexfile"

 and


>> > add nologging (or is it norecovery?). This should speed things up.

>> >

>> > But what happens after the Create Index?  I know I should take a

>> > backup.  But will all subsequent operations on the index be logged?

 Do


>> > I have to ALTER the index, for this to happen?  This is the portion

 with


>> > a lot of conflicting information floating around.

>> >

>> > Does anyone have any good information on achieving the fastest import

>> > and index build.

>> >

>> > Pete

>>

>>

>

>


--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp