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.
SQL Statement
CREATE TABLE
new_customer
TABLESPACE new_ts
AS
SELECT * FROM CUSTOMER ORDER BY CUST_NBR;
We execute the CTAS command to move the table rows into the new tablespace.
Description and Relevant Features:
The image illustrates the CTAS (Create Table As Select) command being used to copy data from one table (CUSTOMER) into a new table (new_customer) while ordering the data by CUST_NBR.
Key Features:
The CTAS command is shown to create a new table in the new_ts tablespace. This operation helps in efficiently organizing and moving data.
The diagram visually represents data blocks being transferred from the original table to the new tablespace. The colored blocks indicate data rows that are being re-sequenced based on the ORDER BY CUST_NBR clause.
The operation is useful for optimizing the physical data layout in the new tablespace, which can lead to improved performance, particularly if queries frequently use the CUST_NBR column.
2) Now that the tablerows have been moved to a new tablespace, we can rename the table and reorder the rows
3) Drop the table into the reordered rows.
4) The final step is to recreate the indexes on our new customer table.
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.
Create table
new_customer
parallel (degree 6)
As
Select
*
from
customer
Order by
customer_nbr;
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;
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.
