Insert /*+ append */ always? (again)

From: kopek <matezuka_at_yahoo.com>
Date: 31 Jul 2002 00:41:10 -0700
Message-ID: <f7cb1d69.0207302341.3964f2f1@posting.google.com>

Sorry for digging up this 3-month old thread. I can not seem to be able to respond from the original post itself. Here, Tom Kyte responded to Jonathan Lewis regarding direct insert, index and redo consumption.
http://shrinkalink.com/275

I am particularly interested in this part from Tom:

---

drop table t;
create table t as select * from all_objects; set autotrace on statistics;
insert into t select * from all_objects; rollback;
insert /*+ append */ into t select * from all_objects; rollback;
create index t_idx1 on t(object_id);
insert into t select * from all_objects; rollback;
insert /*+ append */ into t select * from all_objects; rollback;
set autotrace off

and the results are:

    2572664 redo size

       7304 redo size
    7114216 redo size
     694948 redo size

this time...

So, the table adds about 2.5meg of redo -- meaning we saved about 2meg on the
index.

---

I repeated the steps and got similar results. Questions:

1. Table t is in logging mode in this case. So the direct insert statement is fully logged. Why the significant saving in redo when compared to conventional insert? Why would writing above the HWM induce such redo saving?
2. Shouldn't the redo saving on the index be (7114216 - 694948)"Total saving" - (2572664 - 7304)"Saving on table"

which is about 6.4 - 2.6 = 3.8 meg, instead of 2meg? That means saving on the index side is even greater, about 50% greater than on the table side.

3. I turned off table logging (db runnig in NoArchive mode) and repeated the same steps and got nearly identical results.

4. The autotrace shows "sort" operation. Why does "insert into .. select from" cause a sort?

Can somebody shed some light on these?

• K. M.

Received on Wed Jul 31 2002 - 02:41:10 CDT