Author: Don Burleson

Reducing disk I/O is the best way to bring about quick improvement in high-transaction system with lagging response times. Table reorganization with the CTAS method can reduce I/O for accessing tables in a transaction system through range scans in primary-key indexes. This method can considerably speed up data retrieval by physically sequencing the rows in the same order as the primary-key index.

Unordered Tables:

Unordered tables can experience huge I/O for the queries that access common rows with a table. This is because the index retrieves a separate data block for each row requested.

If we group like rows together we can get the entire row with a single block read because the rows are together.  You can use 10g hash cluster tables, single table clusters, or manual row re-sequencing (CTAS with ORDER BY) to achieve this goal:

Row Re-sequencing:

• Row re-sequencing is easy, inexpensive, and relatively quick.
• Storing data in the same way that it is retrieved will reduce I/O. 
• Re-sequencing table rows to place related rows together on the same data blocks can greatly reduce I/O because Oracle can grab more relevant data in a single block fetch. 

Reducing I/O for performance improvement:

I/O is the single greatest component of response time and one must regularly work to reduce I/O. Disk I/O is expensive because when Oracle retrieves a block from a data file on disk, the reading process must wait for the physical I/O operation to complete.  Physical reads (disk access) is at least 100x slower than a buffer get, and anything you can do to minimize I/O can greatly improve the performance of any Oracle database.

Clustering all orders for a customer together on the same data block allows the query " show me all orders for this customer " to be retrieved in a single fetch.  With row re-sequencing, adjacent rows comes together, greatly reducing I/O:

Why Row Re-sequencing?

Row re-sequencing can cluster related rows together onto the same data block. It can pre-order the data to avoid expensive disk sorts after retrieval. Row re-sequencing packages adjacent rows together on a single data block to reduce block fetches for index row scans.

Row re-sequencing does not help queries that perform full-scans or index unique scans, and careful attention must be given to the nature of the queries against the target table.

Benefits of Row Re-sequencing:

The benefits of row-re-sequencing depend upon many factors:

• PL/SQL bulk collect can affect consistent gets for row-ordered tables.
• The SQL*plus arraysize can affect consistent gets for row-ordered tables. 
• Rows per block fetch is important and it depends on block size and avg_row_len.
• How you access your table has a great effect on the benefits of row re-sequencing:
• The number of rows returned by the range scan is a major factor. 
• Parameters like db_file_multiblock_read_count, optimizer_mode effect multiblock reads against the target table.
• In large active tables with a large number of index scans, row re-sequencing can triple the performance of queries. 

How much Row re-sequencing should be done?

The degree to which row re-sequencing improves performance depends on how far the rows are when you begin and how many rows you will be accessing in sequence. dba_indexes and dba_tables views in the data dictionary can help you find out how well a table's rows match the index's sequence key.

clustering_factor:

In the dba_indexes view, we look at the clustering_factor column. If the clustering factor-an integer-roughly matches the number of blocks in the table, your table is in sequence with the index order. However, if the clustering factor is close to the number of rows in the table, it indicates that the rows in the table are out of sequence with the index.

Row-Sequencing Tools:

Once you have decided to re-sequence the rows in a table, you can use one of the following tools to reorganize the table.

• You can copy the table using Oracle's Create Table As Select (CTAS) syntax. 
• Oracle9i dbms_redefinition , is an in-place table reorganization tool.
• Oracle cluster tables are specifically made for row sequencing. You can migrate to a single-table or multi-table cluster.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Beware of crashed Oracle Instance Semaphores and Memory!!
   Be cautious while using JOIN Order
   Oracle 11g Enhanced Optimizer Statistics Maintenance!!
   DBA Tips for Verifying Oracle Data Replication!!