Re: "create table as" statement versus an "insert into" statement (performance

From: <Heidi_Schmidt_at_gillette.com>
Date: Thu, 26 Oct 2000 10:51:00 -0400
Message-Id: <10661.120289@fatcity.com>

Thanks ever so much for the insight.

I also had the opportunity to catch a great presentation Dave with Prec= ise
at Bentley College
which also helped with some SQL concepts about different optimizer meth= ods
I was curious about.

Many thanks!

     Heidi

                                                                       =
                       =20
                    paquette stephane                                  =
                       =20
                    <stephane_paquette@        To:     Multiple recipie=
nts of list ORACLE-L   =20
                    yahoo.com>                 <ORACLE-L_at_fatcity.com>  =
                       =20
                    Sent by:                   cc:                     =
                       =20
                    root_at_fatcity.com           Subject:     Re: "create=
 table as" statement   =20
                                               versus an "insert into" =
statement (performance =20
                                                                       =
                       =20
                    10/24/00 03:25 AM                                  =
                       =20
                    Please respond to                                  =
                       =20
                    ORACLE-L                                           =
                       =20
                                                                       =
                       =20
                                                                       =
                       =20

I think the CTAS use the direct path, that's why it is faster and that's why you can use it with the nologging option to speed it even more.

• Heidi_Schmidt_at_gillette.com a =E9crit=A0: >
  > I could use some help.
  >
  > Does someone know why a create table as statement
  > would perform better than
  > a truncate table, and then insert into statement?
  > Gut instinct tells me the create table is cleaner
  > and the insert into has
  > to find free chunks, but that can't be all of it.
  >
  > The background is that we have some developers who
  > are executing large data
  > loads and have to recreate objects every evening as
  > part of a hierarchical
  > OLAP tool process. They are looking to shave time
  > wherever they can to get
  > it all into their batch window.
  >
  > I am not sure where to begin to look for information
  > that would show why
  > one way would perform better than the other.
  > I think it would be under parsing algorithms, which
  > would be cool to learn
  > if there was a list of them or a chart.
  >
  > Thanks in advance,
  >
  > Heidi
  >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author:
  > INET: Heidi_Schmidt_at_gillette.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).
  

=3D=3D=3D=3D=3D
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com

---

Do You Yahoo!?
Yahoo! Messenger - Talk while you surf! It's FREE. http://im.yahoo.com/

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =3D?iso-8859-1?q?paquette=3D20stephane?=3D
  INET: stephane_paquette_at_yahoo.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

Received on Thu Oct 26 2000 - 09:51:00 CDT