|Lesson 3||Using CTAS to re-sequence table rows|
|Objective||Re-sequence table rows with CTAS. |
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.
- 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.
parallel (degree 6)
- Use CTAS with ORDER BY. This walks the existing index to gather the rows, but it cannot be run in parallel.
- Spool the rows into a flat file from SQL*Plus and reload the table using the SQL*Loader utility.
Which approach is best?
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.
ctas Resequence table Rows - Exercise