Re: Create table as subquery performance

Bruce,

        There is a bug in versions of Oracle that are pre 7.3 that uses the rule based optimizer instead of the cost when you peform a CREATE TABLE AS SELECT statement. If you run the subquery on its own, it is fine. This is fixed in 7.3 but there is a patch available of which I believe there are backports for various versions. Contact the support centre for more details. I have found this problem on a Sequent Platform.

Simon Kelly
Database Consultant
ITnet Ltd

Bruce Douglas <bruce.douglas_at_bridge.bst.bls.com> wrote in article <326E7A80.1725_at_bridge.bst.bls.com>...
> I'm using a reporting tool that makes extensive use of Oracle's
> CREATE TABLE X AS SELECT * FROM Y syntax. I'm tracking down a
> performance problem and have discovered this:
>
> CREATE TABLE X AS SELECT * FROM Y takes much longer than breaking
> the process into pieces:
>
> CREATE TABLE X;
> INSERT INTO X SELECT * FROM Y;
>
> In the trivial example I'm testing, the elapsed time drops from 6
> minutes to 5 seconds.
>
> Can someone point me in the right direction? What's going on
> here?
>
> As always, thanks in advance for your help.
>
> -------------------------------------------------
> Bruce Douglas -- bruce.douglas_at_bridge.bst.bls.com
> BellSouth Telecommunications
> -------------------------------------------------
>
Received on Sat Oct 26 1996 - 00:00:00 CEST