How does one resequence table rows in physical order on the disk?

In practice, many Oracle SQL tuning professionals will resequence the table rows 开发者_开发知识库into the same physical order as the primary index.

Source: http://www.remote-dba.net/t_op_sql_index_access.htm

How does one resequence table rows in physical order on the disk? Is this "Index Organized tables"?

It would only make sense to want the data in a heap table to be physically ordered with the primary key index if you are doing a range scan of the primary key index and then doing a single row lookup in the table. That tends to be relatively uncommon-- you don't commonly want to fetch all the data for ORDER_ID 1-100 from the ORDER table, for example. It is probably more common if you are using natural, multi-column primary keys but that is somewhat unusual.

If you do find yourself with a table that undergoes frequent index range scans on the primary key and you want to optimize that particular access path, you'd almost certainly be better off using an index-organized table or a hash cluster in order to have Oracle take care of the physical ordering of rows automatically. It makes little sense to create a maintenance headache for yourself by regularly reorganizing the table when you can simply instruct Oracle to maintain the order. Of course, optimizing for this access path will decrease the efficiency of accessing the table via any other index so it is far from a cost-free option.

Even in the case where you have index range scans on the primary key and the trade-offs of optimizing that access path outweigh the costs to the other index access methods, the physical order of rows in the table is going to have a relatively small impact on the cost of executing the query. The vast majority of queries and processes have the potential for much greater levels of optimization with far fewer problems using standard SQL tuning techniques rather than bothering with the order of rows in a table.

Caveat 1: If you happen to be trying to compress a table (not using the Advanced Compression option), the order of rows in the table can be important because better ordered data is more easily compressed. That's the only time I've ever found myself caring about the physical order of data.

Caveat 2: If you do have a table that meets all the criteria outlined and you do physically order the data in the table and you happen to use RAC, you could end up creating much more interconnect traffic by concentrating "interesting" rows into fewer hot blocks that have to constantly be passed between nodes. That can easily offset whatever marginal benefit you received from reorganizing the table in the first place.

Generally you don't.

It is possible with a

CREATE TABLE ... AS SELECT .... ORDER BY

But you then get into the business of dropping the old table and renaming the new one - plus grants, constraints etc.

But IOTs have to be organised on the primary key. That might not be the "primary" index if you mean the index used for most range scans on the table.

Consider, a CUSTOMERS table with an ID as primary key and a customer name. If you are looking for a customer based on id, then clustering doesn't have any benefit, because you aren't really interested in the customers with IDs just above or below. If you are looking from customers called "Seinfeld", then you would do a range scan and there might be a benefit in having all the Seinfeld table records in the one block. If you are then going to filter on first_name, you would probably be better off just including that in the index so you don't need to access the table records.

In an 'ORDER_LINES' table, you'd probably find all the lines for a particular order on the same block anyway, as they are probably created at the same time. Clustering factor on the index would tell you.

A CUSTOMER_INVOICES table might benefit from being clustered on the customer id if you have a lot of queries for all invoices for a particular customer. In that case you might look at Single Table Hash Clusters as a way to cluster data on a column value. But it would be way down my list of things to do

Typically one uses sql-loader to Unload and Load the table. However, see Tom Kyte's articles on table reloading and unbalanced indexes for their side-effects.

An index-organized table is a two-edged sword: yes, it puts the table data in the same block as the (primary key) index, thereby avoiding 1 disk I/O. It also means that the amount of data to be read to access that table goes up whenever it needs to be accessed in any other order.

The best purposes for an IOT is as a lookup-table, or validation-table, where there is little additional information beyond the key value.