Re: problem with 'create table xxx as <query>' with order by clause

One common work around is to index the
field you want to order on, then

create as select /*+ index (t, ind) */ * from

    t
where indexed_column > '{known minimum possible value}' ;

or in your case:

create as select /*+ index_desc (t, ind) */ * from

    t
where indexed_column < '{known maximum possible value}' ;

The simple

    create as select order by

works in 8.1, and (probably by accident) in 7.3.4 but not in 8.0

BTW:
    create table temp
    unrecoverable
    as
    select ....

is a good way of improving performance on creating transient tables - does no redo log generation.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Paul Wagner wrote in message <7nak20$e67$1_at_news1.tc.umn.edu>...
>Working with Oracle 7.3.2. I just noticed that 'create table temp as
>select field from table order by field' does not work - generates an
>'SQL command ended improperly' error. Of course, the plain 'select
>field from table order by field' works fine, as does the above create
>table statement if you remove the order by clause.
>
>I found one workaround - 'create table temp as select field from table
>group by field', as Oracle orders the groups in ascending order, which
>is what I want now. This won't work for descending order, though. Anyone
>know of a workaround that allows me to use "order by" in a "create table"
>statement?
>
Received on Fri Jul 23 1999 - 15:44:21 CDT