Thanks for the question, Serene .

Asked: December 26, 2004 - 10:14 pm UTC

Last updated: December 28, 2004 - 2:49 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi,

My understanding is global temp table will only make use of temp tablespace. But why I enountered "rollback segment not enough" error while trying to insert huge data to a global temp table?

thks,
Serene

and Tom said...

Your understand is wrong.

GTT's are temporary tables -- but they have to support

a) read consistency
b) rollback
c) rollback to savepoint

they definitely -- totally definitely -- generate UNDO.

consider:

ops$tkyte@ORA9IUTF> create global temporary table t ( x int ) on commit preserve rows;

Table created.

ops$tkyte@ORA9IUTF> insert into t values ( 1 );
1 row created.

ops$tkyte@ORA9IUTF> variable x refcursor
ops$tkyte@ORA9IUTF> exec open :x for select * from t;
PL/SQL procedure successfully completed.

that is (as always) a read consitent result set, it is pre-ordained, we haven't fetched a single row of data yet -- NO IO HAS BEEN performed, the result set is not 'copied' somewhere -- just like any other query

ops$tkyte@ORA9IUTF> savepoint foo;
Savepoint created.

ops$tkyte@ORA9IUTF> insert into t values ( 2 );
1 row created.

ops$tkyte@ORA9IUTF> select * from t;

X
----------
1
2

that shows we can see two rows -- but

ops$tkyte@ORA9IUTF> print x

X
----------
1

that query used UNDO to rollback the changes made to the block we are querying to the point in time that cursor was opened. We just used UNDO (query would fail with 1555 if the undo didn't exist)

ops$tkyte@ORA9IUTF> rollback to savepoint foo;
Rollback complete.

ops$tkyte@ORA9IUTF> select * from t;

X
----------
1
there, we used the undo again to put table T back the way it was immediately prior to the savepoint

ops$tkyte@ORA9IUTF> rollback;

Rollback complete.

ops$tkyte@ORA9IUTF> select * from t;

no rows selected

and we used it yet again (remember, this was on commit PRESERVE rows -- commit would have left the row, rollback undid the changes using UNDO)

If you want to minimize the amount of undo generated -- you would use the same approach you would for a "real" table -- insert /*+ append */, that'll skip undo generation on the TABLE data (but not indexes, nothing skips that)

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, December 27, 2004 - 10:47 am UTC

I was under the impresssion that APPEND hint only skips the redo generation(not undo generation) for table data...

December 27, 2004 - 10:55 am UTC

you got that entirely backwards.

APPEND doesn't skip redo
APPEND skips UNDO only.

the following applies to TABLES only, indexes -- always generate undo, always generate redo:

If database is in NOARCHIVELOG_MODE
then
insert generates REDO and UNDO
insert /*+ append */ generates NO REDO, and NO UNDO

elsif database is in ARCHIVELOG_MODE
then
insert generates REDO And UNDO
if ( table is NOLOGGING prior to the insert /*+ append */
and FORCE LOGGING is not enabled for the database )
then
insert /*+ append */ generates NO REDO, and NO UNDO
else
insert /*+ append */ generates REDO, but NO UNDO
end if;
end if;

insert append skips UNDO generation

in archive log mode, insert append generates redo unless you said "nologging" AND the DBA permits it by not forcing logging.

A reader, December 27, 2004 - 10:49 am UTC

Provided the table in question is NOLOGGING

Tom,do you explain about the things you told above in your book?

A reader, December 27, 2004 - 3:55 pm UTC

Gil

December 27, 2004 - 4:44 pm UTC

Not so directly -- for you see, this is "anti documentation"

I say and describe how NOLOGGING affects redo generation with various statements.

I do not say "by the way, append doesn't generate redo", not anymore than I would say "by the way, append doesn't cause rain to fall" or "by the way, append will not make you appear younger looking in the mirror" and so on :)

It is hard to anticipate every mistaken thought and say "by the way, it is not true that..."

I tended to write about "positive documentation" stuff "here is how it works"

A reader, December 27, 2004 - 7:37 pm UTC

Append hint

A reader, December 28, 2004 - 12:23 pm UTC

Tom,

(a) If append hint does not generate any UNDO, why are we able to rollback immediately after the insert with append hint ?

(b) Here is what I observerd with and without the append hints. Why is the redo stats so higher for the normal insert without the append hint when compared with the insert with the append hint ?

Our database is in noarchivelog mode and is a development instance.


SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 28 12:12:20 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> create table abcd as select * from dba_objects where 1=2;

Table created.

SQL> set autotrace on
SQL> insert /*+ append */ into abcd select * from dba_objects;

33406 rows created.


Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
        236  recursive calls
         45  db block gets
      27564  consistent gets
        307  physical reads
       6660  redo size
        335  bytes sent via SQL*Net to client
        331  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      33406  rows processed

SQL> rollback;

Rollback complete.

SQL> select * from abcd;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'ABCD'




Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
          5  consistent gets
          0  physical reads
         40  redo size
        511  bytes sent via SQL*Net to client
        234  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> insert into abcd select * from dba_objects;

33406 rows created.


Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=CHOOSE
   1    0   VIEW OF 'DBA_OBJECTS'
   2    1     UNION-ALL
   3    2       FILTER
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
   5    4           NESTED LOOPS
   6    5             TABLE ACCESS (FULL) OF 'USER$'
   7    5             INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
   8    3         TABLE ACCESS (BY INDEX ROWID) OF 'IND$'
   9    8           INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)
  10    2       TABLE ACCESS (BY INDEX ROWID) OF 'LINK$'
  11   10         NESTED LOOPS
  12   11           TABLE ACCESS (FULL) OF 'USER$'
  13   11           INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)




Statistics
----------------------------------------------------------
          7  recursive calls
       2596  db block gets
      27940  consistent gets
          0  physical reads
    3789332  redo size
        372  bytes sent via SQL*Net to client
        317  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      33406  rows processed

SQL> 
SQL> rollback;

Rollback complete.

SQL>  insert /*+ append */ into abcd select * from dba_objects;

33406 rows created.


Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
        203  recursive calls
         60  db block gets
      27569  consistent gets
          3  physical reads
       8612  redo size
        350  bytes sent via SQL*Net to client
        332  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      33406  rows processed

December 28, 2004 - 2:23 pm UTC

(a) append says "write above HWM, don't reuse any existing allocated space. Therefore, just update the dictionary to extent the HWM. If you need to rollback, all we need do is "un-update" the dictionary, it is as if the append never happened"

append, because it writes "above" the table, in new space -- is rolled back by simply undoing the dictionary update that grew the table in the first place.

(b) did you read the above?

If database is in NOARCHIVELOG_MODE
then
insert generates REDO and UNDO
insert /*+ append */ generates NO REDO, and NO UNDO

insert generates REDO and UNOD
insert append generates NO REDO, NO UNDO

when database is in NOARCHIVELOG MODE.

Append hint

A reader, December 28, 2004 - 2:49 pm UTC

Thanks, Tom for the answers. I should not have asked the second question, it was already there in your previous post, as you rightly pointed out .

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Tom,do you explain about the things you told above in your book?

Append hint

Append hint

More to Explore

PL/SQL demos

PL/SQL docs

Global temp table using rollback segment

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Tom,do you explain about the things you told above in your book?

Append hint

Append hint

More to Explore

PL/SQL demos

PL/SQL docs