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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.