Index Space Utilization [message #156508] |
Wed, 25 January 2006 08:10 |
vinu_raj
Messages: 26 Registered: April 2005 Location: Bangalore INDIA
|
Junior Member |
|
|
I recently did a bulk data upload in the database using SQL*Loader direct path load. Data volume increased 107GB to 190GB (80%) due to this upload. While loading I kept the indexes in unusable state and rebuild the index after loading and index space utilization has grown from 33GB to 96GB, around 190%. Don't really understand the reason. I thought the index space is proportional to the data volume. Anybody has any clue ?
thanks
Vinu
|
|
|
Re: Index Space Utilization [message #156542 is a reply to message #156508] |
Wed, 25 January 2006 11:46 |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
Depends on many many factors, but it is index data volume that matters more than table data volume. A single table with 5 indexes on several columns each could easily cause indexes to be larger than the table. pctfree settings matter. As does compression. You may not have a problem at all. But we'd need more details to tell if you did.
|
|
|
Re: Index Space Utilization [message #156558 is a reply to message #156542] |
Wed, 25 January 2006 14:34 |
vinu_raj
Messages: 26 Registered: April 2005 Location: Bangalore INDIA
|
Junior Member |
|
|
No real problems with this. Only thiing is that once the index rebuild was completed alter loading the data, the file system was 87% full as I expected it to grow from 33GB to 70-75GB only. I based by calculations on increase in data volume (which 80%). Just wondering, is this a problem with index rebuild not reusing the space or something ?
-Vinu
|
|
|
Re: Index Space Utilization [message #156627 is a reply to message #156508] |
Thu, 26 January 2006 09:39 |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
That would depend on the settings of your datafiles, specifically whether they will autoextend or not. Once they extend, they don't go back down. So say oracle created extra objects in a tablespace during the load (I believe direct path load actually uses multiple copies of the indexes during the process of loading, then merges the versions together and cleans up after itself, all in the tablespace you are loading into).
This causes extra space to have been used during the load, possibly causing your datafile to expand. But afterwards, the space usage in that datafile is actually much less, but the datafile will not shrink back down. So your OS is reporting more space consumed on disk.
|
|
|
Re: Index Space Utilization [message #156644 is a reply to message #156508] |
Thu, 26 January 2006 12:57 |
vinu_raj
Messages: 26 Registered: April 2005 Location: Bangalore INDIA
|
Junior Member |
|
|
Got it. My tablespaces are set to autoextent on with unlimited number of extents and I know that rebuilding index in parallel creates multiple temp segments and merge them finally to have the index. So in this case, the tablespace is not going to grow (allocate more extents) atleast for some time as extents are alerady available and are not used and normal insert/update will use the already allocated extents. Is this statement right ?
thanks
Vinu
|
|
|
Re: Index Space Utilization [message #156653 is a reply to message #156508] |
Thu, 26 January 2006 14:24 |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
Almost...the extents don't already exist. They will be allocated as needed when new records are inserted. But the datafile has already been allocated at the OS level. If you look in dba_free_space, or user_free_space, you should see lots of available space in your tablespace.
In general, I've always found conversations having to do with space usage to be difficult, not because the concepts are difficult, but because of the terminology people use can mean different things to different people. Matter of defining terms.
So in your case the tablespace won't grow in size at the OS level, because space was already allocated to it. But that space is empty, and table and index segments will allocate space from it in the form of extents when new records are added.
Also have a quick read through the chapter in concepts and administrators guides on tablespaces and datafiles etc. It is one of the early chapters.
|
|
|
|