Re-sequence table rows with CTAS

One of the easiest methods for re-sequencing table rows is to copy the table into another tablespace while re-ordering the row sequence. To illustrate this technique, consider the following example.

Other methods for re-sequencing rows In the ctas-resequence example, we re-sequenced the rows with ORDER BY, but there are three methods for re-sequencing rows.

1. Use CTAS in parallel to speed reorganization time by using an index hint instead of ORDER BY. However, using the PARALLEL clause requires a full-table scan and an internal sort. Depending upon the table, this may be slower than running CTAS with ORDER BY.
   

   Create table
   new_customer
   parallel (degree 6)
   As
   Select
   *
   from
   customer
   Order by
   customer_nbr;
   

2. Use CTAS with ORDER BY. This walks the existing index to gather the rows, but it cannot be run in parallel.
   

   Create table
   new_customer
   As
   Select
   *
   from
   customer
   Order by
   customer_nbr;
   

3. Spool the rows into a flat file from SQL*Plus and reload the table using the SQL*Loader utility.

Most Oracle administrators will choose the index approach over the full-table scan because the run time benefits of row clustering outweigh the faster reorganization speed of using the PARALLEL clause. The Oracle optimizer gives precedence to the parallel hint, and hence a full-table scan, over a directive to use an index that would prevent parallelism. Of course, the clustering factor of your newly reorganized table will decline over time as new rows are added onto free blocks at the end of your table. When physical I/O increases for the table, it will signal the time to reorganize the table. The next lesson looks at removing free extents from tables.

Click the Exercise link below to practice writing row re-sequencing syntax.
ctas Resequence table Rows - Exercise