Re: understanding rollback segments (somewhat long)

Jim Yoshii wrote:
> CREATE TABLE is a DDL statement, it cannot be rolled back, and it
> doesn't use rollback segments.

In fact this is *incorrect*. DDL statements cannot be rolled back after they complete, but this is not because they don't use rollback segments. Rather, it is because a "commit" is automatically performed before and after the database processes the DDL command.

The fact that rollback segments are used for DDL commands is obvious on reflection: suppose that the session performing the DDL command is terminated (either by doing an "alter system kill session..." or by shutting down the database) prior to completion of the command. Obviously the work which has already been done to perform the DDL must be undone; that is the function of rollback segments.

It is also easy to demonstrate this empirically: just connect to a database and issue a "create table as select..." command which will not complete for some time. Then observe which rollback segment is used for that session -- by joining v$session, v$rollname, and v$transaction -- and observe the changing values in v$rollstat for that rollback segment. Specifically, the value in the "writes" column will continually increment. (I know that this works, because I've just done it to make sure I'm not spouting misinformation.)

>
> You should have done the obvious:
>
> CREATE TABLE foo AS SELECT * FROM bar;

This part is (partially) correct. It is rare to run out of rollback space while doing a "create table as select" because the table being created is not indexed (yet, anyway); inserts on unindexed tables generate a very small amount of rollback. If you are running out of space in the rollback tablespace when doing this, then you do not have nearly enough rollback space!

The question is not nearly so clear when doing an "INSERT INTO... SELECT FROM..." into an existing table. If the table is indexed, it will generate a great deal more rollback information -- in my testing, I've seen inserts into indexed tables generating 43 times the amount of rollback information as inserts into non-indexed tables.

HOWEVER, I recommend that you *not* populate the new table using "create table as select". Instead, create the table structure first using a command like:

	create table [NAME}
	tablespace [...]
	storage [...]
	as select [COLUMN(S)] from [TABLE(S)]
		where 1=2;

Which will create an EMPTY table; and then following this with an "INSERT INTO [NAME] SELECT [...]" command. The reason for this is concurrent access; I have often seen (in Oracle 7.2 and earlier, anyway) that when doing a "CREATE TABLE AS SELECT" with a select that joins several tables, locks are established on some of the data dictionary tables which make it impossible for any other session to select from -- or even describe -- one or more of the source tables, until the CREATE TABLE command is complete. This can take several hours for complex statements.

I hope this helps, and I hope that I haven't tried your patience with the lengthy article...

-bn
neumebm_at_hpd.abbott.com Received on Thu Apr 24 1997 - 00:00:00 CDT