Re: Use of TEMP TS with INSERT

<artmt_at_hotmail.com> wrote in message
news:1135808581.749569.37140_at_g49g2000cwa.googlegroups.com...
>I still don't understand what TEMP ts has to do with it.
> Are you saying that extend actually failed in MY_DATA or UNDO, but
> Oracle mistakenly identified it as TEMP?
>
> Additional info:
>
> 9.2.0.6.0
> The statement uses APPEND and PARALLEL
>

The fact that you are doing an APPEND is (probably) the critical bit of information that you needed to supply. When you do an append, Oracle will postpone updating the indexes until all the table data is in place, then it sorts the key value for the new data once for each index before doing an array update to each index in turn. For large amounts of data, the sorts could overflow to disc, i.e. into the TEMP tablespace.

However, your original statement was that you were simply doing:

    insert into tableA select * from tableB; Is the 'select * from tableB' also a simplification to save typing - or is it really a more complex query involving table joins and/or subqueries? If so, then Oracle may be using merge or hash joins to perform the select before it gets to the insert - and those joins could be overflowing to disc, i.e. the TEMP space.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 29th Nov 2005