Sort order of composite primary keys

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?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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


This was first published in January 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top