| |
 |
|
ss
Oracle Tips by Burleson |
Using CTAS with the order by
Clause
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
by:
create table new_customer
tablespace customer_flip
storage (initial 500m
next 50m
maxextents unlimited)
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:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
