RE: CTAS use of rollback

Not exactly. The select statement can if there are concurrent transactions and needs for read consistency like any other select statement.

"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863  

-----Original Message-----
Sent: Friday, October 05, 2001 5:00 PM
To: Multiple recipients of list ORACLE-L

Confirmed.

"Nicoll, Iain (Calanais)" wrote:

> 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).

--
Thanks,

Yosi


---------------------------------------------------------
Yosi Greenfield
Oracle Certified DBA
ygreenfield_at_compuserve.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yosi Greenfield
  INET: yosi_at_comhill.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: cspence_at_FuelSpot.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).