CTAS use of rollback

From: Nicoll, Iain (Calanais) <iain.nicoll_at_calanais.com>
Date: Fri, 05 Oct 2001 11:36:45 -0700
Message-ID: <F001.003A3D53.20011005115139@fatcity.com>

Could anyone tell me whether Create table .. as select .. uses rollback.

I initially thought it would (despite being a cross between ddl and dml) but having created a 3.5 million row table and checked the sum of the writes in v$rollstat it had only done ~130k writes between the start of the ctas and the end. It also doesn't create the table initially but just has a numbered object which it seems to rename only at the very end, so if it fails I would have though it would just drop that object and if it completes successfully then a commit would be done because of the ddl aspects of the command.

I tried inserting 10k rows into the same table and this came back with about 25k writes (seemed reasonable if it's only storing the rowid). Given this it doesn't seem to be using rollback (other than recording changes to extents etc) but I'd appreciate confirmation.

Iain Nicoll
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Nicoll, Iain (Calanais)
  INET: iain.nicoll_at_calanais.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Oct 05 2001 - 13:36:45 CDT