Re: How to reorg a table?

scarrola_at_mailsrvr.bussvc.wisc.edu (Steve Carrola) wrote:

>In databases like DB2, you can reorganize a table so that the physical
>file is placed in sorted order by the primary key. This usually increases
>performance. Does the export/import of a table take care of this?

Maybe, haven't tried it though. It does re-calculates the storage for that table, when you specify compress extents.

>If so, is there another way to do this without an export/import? with
>the table on-line?

You could do it with a little trick. rename the table to TABLE_OLD and type:

INSERT INTO TABLE
SELECT col3, col2, col1 FROM TABLE_OLD
UNION
SELECT col3,col2, col1 FROM TABLE_OLD;

While an order by clause is not allowed in the insert into ... select .. statement, you can do a bogus UNION which implicitely sorts the resulting set. just put the columns in the preferred sort order. In the above case this would be col3, col2, col1. This might not sound like a high-tech solution, but it works on all platforms (really portable) and doesn't cost a cent...

>Also, is there a query sample that someone has to tell you how
>disorganized the table is?

Oracle doesn't store it's data in sorted order. So basically the order where it's put in the table is THE order in the table. So any query on the table without an order by clause gives you THE order.

>Thanks

No swet...

+------------------------------------------------------------+
|   Mike Philippens - Gorinchem - The Netherlands, Europe    |
|                 Vijfhart Automatisering bv                 |
|       Oracle Specialists Training and Consultancy          |
|                 Utrecht    The Netherlands                 |
+------------------------------------------------------------+