Frank wrote:
> Daniel Morgan wrote:
>
>> Jonathan Lewis wrote:
>>
>>> Repeat your no-append tests,
>>> but start a transaction by doing
>>> a small update some where else
>>> before doing the insert. Then:
>>>
>>> Check v$transaction.used_urec and v$mystat
>>> (your rows from v$sesstat) for statistics
>>> "redo size" and "redo entries" before and
>>> after doing the main insert. I think you will
>>> find that Oracle generates one undo record
>>> (urec) per row for the temp table, but one
>>> undo record per block for the normal table -
>>> hence the significant difference in time.
>>
>>
>>
>> As I recall the original post was about 100 rows and when I run
>> with 100 rows I find no detectable difference.
>>
>> SQL> insert into t1
>> 2 select * from all_objects
>> 3 where rownum < 101;
>>
>> 100 rows created.
>>
>> Elapsed: 00:00:00.04
>> SQL> insert into t2
>> 2 select * from all_objects
>> 3 where rownum < 101;
>>
>> 100 rows created.
>>
>> Elapsed: 00:00:00.04
>> SQL>
>>
>> That is after the first run. The first time I run it I see a
>> difference. All subsequent tests yield the same result.
>>
>
> So taken up with the example, I missed making a point:
> - The append hint makes the difference going away.
> - 100 rows is so little, you would have to test with more.
> - the difference is not 50 times (although tested with 9.2);
> in 8.1.7.4.1, with 100 rows, I had:
> SQL> insert into t2 select * from all_objects where rownum < 101;
> 100 rows created.
> Elapsed: 00:00:00.00
>
> SQL> insert /*+append */ into t1 select * from all_objects where rownum
> <101;
>
> 100 rows created.
> Elapsed: 00:00:00.00
>
> Proves both you and Jonathan are right
> (and the OP, as 50 * 00:00 is 00:00 ;-) )
BTW: I have done this in both 9.2.0.4 and 10.1.0.2 the results are the
same. With 10g and larger samples there is an improvement over 9i but,
based on the NDA, I'm not sure I can post the results so I'm not doing so.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Sat Dec 06 2003 - 12:15:16 CST
