Richer -- Thanks for the question regarding "Append hint in archivelog mode.", version 8.1.7

Sorry for a stupid question.  It was so obvious it almost got me blind...

 

"no difference - in archive log, append surpressed ROLLBACK but NOT redo, "

Tom what is showing you that ROLLBACK was supressed?? 

Followup   March 6, 2003 - 6pm Central time zone:

I know it was, that is why :)

Here is the "tangible evidence"

ops$tkyte@ORA9I> create table t as select * from all_objects where 1=0;

Table created.

ops$tkyte@ORA9I>
ops$tkyte@ORA9I> insert into t select * from all_objects;

44876 rows created.

ops$tkyte@ORA9I> select used_ublk
  2    from v$transaction
  3   where addr = ( select taddr from v$session where sid =
  4                    ( select sid from v$mystat where rownum = 1 )
  5                 );

 USED_UBLK
----------
        22

ops$tkyte@ORA9I> commit;

Commit complete.

ops$tkyte@ORA9I>
ops$tkyte@ORA9I> insert /*+ APPEND */ into t select * from all_objects;

44876 rows created.

ops$tkyte@ORA9I> select used_ublk
  2    from v$transaction
  3   where addr = ( select taddr from v$session where sid =
  4                    ( select sid from v$mystat where rownum = 1 )
  5                 );

 USED_UBLK
----------
         1

ops$tkyte@ORA9I> commit;

Commit complete.


That is what append does.  It writes above the HWM of the table, bypassing the need for rollback.  
That is why you cannot query the table after a direct path insert without committing -- it sort of 
"breaks" our consistent read mechanism for that session on that table.

rollback for inserts is very very small (delete rowid is all that is made)...

 

Tom,

I was always wondering, why oracle doesn't allow to read the data inserted thru "direct insert" 
without doing a commit?

Is it because, oracle doesn't have any info on this insert in the RBS? But how come, it allows me 
to read after "commit"? 

Followup   March 7, 2003 - 7am Central time zone:

We don't have the information needed in the RBS for our consistent read mechanism to work (as 
stated above).

...
That is what append does.  It writes above the HWM of the table, bypassing the 
need for rollback.  That is why you cannot query the table after a direct path 
insert without committing -- it sort of "breaks" our consistent read mechanism 
for that session on that table
......


Insert /*+APPEND*/ is a "special" thing, a tool that you may or may not choose to use.  It does 
what it does. 

Hi Tom,

You wrote in the middle of the first reply:

*no difference - in archive log, append surpressed ROLLBACK but NOT redo,* 

If rollback generation was surpressed then why we got the same amount of redo as without append? 
My understanding (from your book actually) was that redo protects *both data blocks and rollback 
entries*. As we do not have rollback for the operation we should get less redo. Why the numbers are 
the same?

Thank you, 
 

Followup   March 7, 2003 - 1pm Central time zone:

because in the insert we generated

21 blocks of undo without append
 1 blocks of undo with append

and CREATED 

ops$tkyte@ORA9I> analyze table t compute statistics for table;

Table analyzed.

ops$tkyte@ORA9I> select blocks from user_tables where table_name = 'T';

    BLOCKS
----------
      1228


new blocks.


So, in one case we had redo for 1249 blocks, in the other case 1229 blocks.  

No significant difference there.....  that is why the same amount of redo was generated more or 
less -- we had about the same amount of blocks to log redo for.



 

Yes, number of generated undo blocks times less than created data block. That's why the difference 
in redo is not significant.

Thank you, 

Hello Tom, let me continue the discussion. The question is whether any difference between SQL 
Loader Direct Insert and Insert with the Append Hint.  
... SQL*Loader then uses the column array structure to format Oracle data blocks and build index 
keys. The newly formatted database blocks are then written directly to the database (multiple 
blocks per I/O request using asynchronous writes if the host platform supports asynchronous I/O). 
Is there anything like this for Inserts with append hint? 
Namely, will such inserts skip the buffer cache or any preprocessing? I did not find anything on 
any differences, but they are called the same, direct inserts, or loads. 

Followup   March 14, 2003 - 6pm Central time zone:

they are not 100% the same but are for all intents and purposes "so similar as to be not really 
distinguisable by us".

direct path inserts also do direct io. 

Tom I did some testing as follows

CREATE TABLE A1 unrecoverable parallel (degree 30)
tablespace sdmuser01
as
select Cust_num , 'PFS' as PFS_CD from tdmcustinsight a
where  a.rm_lob_code = '03'
and a.pfs_offer_code is not null
/

Elapsed: 00:00:05.66

No rollback .

insert into a1 (cust_num,PFS_CD)
select Cust_num , 'PFS' as PFS_CD from tdmcustinsight a
where  a.rm_lob_code = '03'
and a.pfs_offer_code is not null
/


Elapsed: 00:00:14.81

Statistics
----------------------------------------------------------
    9313840  redo size
     366527  rows processed

rollback usage 

NAME           USED_KB   SID   PID SPID 
R08                896    67   118 15906     

insert /*+ APPEND */ into a1 (cust_num,PFS_CD)
select Cust_num , 'PFS' as PFS_CD from tdmcustinsight a
where  a.rm_lob_code = '03'
and a.pfs_offer_code is not null
/

Elapsed: 00:00:12.45

Statistics
----------------------------------------------------------
      13760  redo size
     366527  rows processed

NAME           USED_KB   SID   PID SPID      
R05                 16    67   118 15906     


CREATE TABLE A1 
tablespace sdmuser01
as
select Cust_num , 'PFS' as PFS_CD from tdmcustinsight a
where  a.rm_lob_code = '03'
and a.pfs_offer_code is not null
/

Elapsed: 00:00:10.46


insert into a1 (cust_num,pfs_Cd)
select Cust_num , 'PFS' from tdmcustinsight a
where  a.rm_lob_code = '03'
and a.pfs_offer_code is not null
/

Elapsed: 00:00:13.11

Statistics
----------------------------------------------------------
    9427752  redo size
     366527  rows processed

NAME           USED_KB   SID   PID SPID      
R04                896    67   118 15906     

insert /*+ APPEND */ into a1 (cust_num,pfs_Cd)
select Cust_num , 'PFS' from tdmcustinsight a
where  a.rm_lob_code = '03'
and a.pfs_offer_code is not null
/

Elapsed: 00:00:12.43

Statistics
----------------------------------------------------------
      36104  redo size
     366527  rows processed

NAME           USED_KB   SID   PID SPID      
R01                 16    67   118 15906     

Couple of questions.
When we create a table there is lot of difference in time as against we insert or insert with 
append hint.
Why is that?? 
   If the answer is no REDO & ROLLBACK , the numbers don't justify the difference.

But if we  compare the timing between normal table creation (Without unrecoverable option) & insert 
then elapsed time 
are more meaningful.

So exactly what happens with unrecoverable option ??
Also can you show some light (or link) on parallel option for table creation.

Also if such a minimal rollback is created with append option how we can rollback transaction 
successfully ???

Thanks 

Followup   June 20, 2003 - 3pm Central time zone:

a couple of seconds wall clock difference is not meaningful -- you are taking like 2 seconds on the 
wall clock.

check out the concepts guide, chapter on parallel operations.

read the chapter near the parallel stuff on direct path operations for information on APPEND 
(append writes straight to disk, bypasses the buffer cache and writes ABOVE the current high water 
mark for the table -- hence, if you need to rollback, we simply put the HWM back -- data 
"disappears" 

"a couple of seconds wall clock difference is not meaningful -- you are taking 
like 2 seconds on the wall clock."

What do u mean by that??

Also let me rephrase my question.

If we don't care about redo generation (In read intensive data warehouse) should it be standard 
practice to insert data using /*+ APPEND */ hint ??

When I say only 2 sec difference what I was referring to was it's not that great as compared to 
create table with unrecoverable option.
create table 05.66
insert append 12.45
insert normal 14.81

I was expecting insert append timings would be closer to create table timings. Can you throw some 
light ??

Meantime I am going through concept guide too.

Sam






 

Followup   June 20, 2003 - 5pm Central time zone:

I mean, in the grand scheme of things -- 2 seconds between 10 and 12 seconds is "noise".  

maybe the disks spun slower.
maybe someone else was doing something.
your query ran faster one time, slower the next time.

everything will affect that time.  You need to use tracing and tkprof to see what's up there.

In a data warehouse, during bulk loads, you can definitely use this feature as long as you BACKUP 
right afterwards.  You can use this in any situation -- as long as you BACKUP.

I just don't observe what you do:

create table t NOLOGGING as select * from all_objects

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      4.23       4.34          0     146054        522       31578
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      4.24       4.36          0     146054        522       31578

insert into t select * from all_objects


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.08          0          0          0           0
Execute      1      4.10       4.79          0     146487       4326       31578
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      4.11       4.88          0     146487       4326       31578


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


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.06          0          0          0           0
Execute      1      4.04       4.12          0     146050        123       31578
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      4.05       4.18          0     146050        123       31578



so, I just put it down to "you have other things going on on your system, they are affecting you, 
things will run slower and faster depending on who is doing what".




 

Followup   February 9, 2007 - 9am Central time zone:

"it depends"


did test have data from before.
indexes on test being used (append relates to the table segment only).


"probably" was testb, likely.

Followup   February 12, 2007 - 8am Central time zone:

it is not so much that append surpressed undo as append did a direct path (clean blocks) load and block cleanout would not be necessary.

probably it was testB that was the cause, yes, but we already stated that.

Followup   February 12, 2007 - 9am Central time zone:

yes, "probably" this was testb in this particular case.