| Loading Data [message #129051] |
Thu, 21 July 2005 13:50  |
gbeverly
Messages: 1
Registered: July 2005
Location: Myrtle Beach
|
Junior Member |
|
|
At my shop, we are using Oracle to create an online data warehouse. I am creating files from a Mainframe to pass down to and Oracle database. Does it matter what order the data is in when it gets on the table? IE: Would it save any time to do a sort by my most used index and load the oracle table in that sequence, or does it really matter?
Thanks for your help.
|
|
|
|
| Re: Loading Data [message #129060 is a reply to message #129051] |
Thu, 21 July 2005 15:04  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Yes, the sort order (clustering) can play a role in performance, especially regarding indexes and compression. But as you mention, you can only sort it one way, so when you help one type of query you by definition hurt another. Your best bet is to test a couple of situations with your specific data.
But the basic concept is that say you have a bunch of records with a date column, and you have say 1000 records with the same date value. If you sort by date, then those 1000 values will be nice and tightly packed together on as few blocks as possible, say 50, (and continuous blocks at that).
This means your table access by rowid from an index will need to fetch only 50 blocks for certain types of queries, than had those 1000 records been on 1000 diffeent blocks.
Likewise, the compression factor will be far greater, because compression is done at the block level. But both of these benefits will only happen if the data is largely static (or rebuilt/resorted on a regular basis).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
