Re: Create table as ...

Actually, it is in Oracle documentation. Create table syntax says (SQL Language Reference Manual, p.4-207): CREATE TABLE ... AS subquery. Note, not query, but subquery. P.4-374 (same manual) shows subquery syntax. It does not have ORDER BY clause. At the same time I agree with you, it is usefull in some cases to populate a table in a physically ordered way. There are some tricks to force Oracle into it without using ORDER BY. Simply create an index on ID column and force Oracle to use it in your subquery. Then rows retrieved by subquery will be ordered by IS. For example:

CREATE TABLE test_bak AS SELECT * FROM test WHERE id > 0;

This example assumes ID is positive, therefore condition id > 0 is always true. As a result, subquery returns all table test rows ordered by id.

Solomon Yakobson.

In article <69ovpt$qdj$1_at_scapa.cs.ualberta.ca>,   wtang_at_cs.ualberta.ca (Wei Tang) wrote:
>
> Hi, there:
>
> I wonder if anyone of you ever used "create table as subquery" statement
> to create a table.
>
> I have a table called test:
> create table test(
> name varchar2(10),
> ID number);
>
> But I cannot use the following statement:
> create table test_bak as select * from test order by ID;
>
> It said: ORA-00933: SQL command not properly ended.
>
> When I remove the "order by" clause, everything is fine. I couldn't find
> any documentation saying "order by" is not supported in "create table
> as..." statement.
>
> What's wrong with that?
>
> I need your expertise in a rush. Please help me.
>
> Thanks.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet