Oracle Tips by Burleson
Using CTAS with the order by
When using CTAS with the order by clause, you are directing Oracle
to perform the following operations.
As we can see, the full table scan can be used with Parallel Query
to speed the execution, but we still have a large disk sort
following the collection of the rows. Because of the size of most
tables, this sort will be done in the TEMP tablespace.
Here is an example of the SQL syntax to perform a CTAS with order
create table new_customer
storage (initial 500m
parallel (degree 11)
as select * from customer
order by customer_number;
Using CTAS with order by can be very slow without the
parallel clause. A parallel full table scan reads the original table
quickly (in non-index order).
As we know from Oracle Parallel Query, the CTAS operation will cause
Oracle to spawn to multiple background processes to service the full
table scan. This often makes the order by approach faster than using
the index-hint approach to CTAS. The choice to use parallel depends
on the database server. If your hardware has multiple CPUs and many
(perhaps hundreds of) processes, using parallel is likely to be
significantly faster. However, if your hardware configuration has a
relatively modest number of processes (such as the four specified in
the example), the index-hint approach is likely to be faster.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA