Problem solve Get help with specific problems with your technologies, process and projects.

Sort order of composite primary keys

I migrated a database from MS SQL Server 2000 to Oracle 9i. I found that, in a table with a composite primary key, the records are ordered on first part of the key. For example, if we have an Employee table with a composite primary key as Dept id and Emp id, I want the records to be stored in sorted order dept id, emp id. How to achieve this?

Looking to physically store data in a database in sorted order is not always a good proposition. What happens when you insert a new row which belongs in the middle of this sorted order? You now have to shift every row to make room for this row. Any mixture of INSERT, UPDATE, or DELETE has the capability of throwing this order out of whack. This is one of the reasons why indexes were created in the first place, to provide an ordering for your data no matter what order that data is physically stored on disk. It is up to your queries, and the ORDER BY clause to order that data before it is returned to the application.

But if you insist on physically storing the data in sorted order, then please take a look at Indexed Organized Tables. These are tables that are stored in a B-tree structure, just like indexes. But the table will be stored in sorted order.

For More Information

Dig Deeper on Oracle database design and architecture