Home » RDBMS Server » Server Administration » Index Space Utilization
Index Space Utilization [message #156508] Wed, 25 January 2006 08:10 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Index Space Utilization [message #156767 is a reply to message #156653] Fri, 27 January 2006 11:03 Go to previous message
vinu_raj
Messages: 26
Registered: April 2005
Location: Bangalore INDIA
Junior Member
thanks for the reply.

Vinu
Previous Topic: error installing STATSPACK
Next Topic: REUSE option-tablespace
Goto Forum:
  


Current Time: Sat Jan 25 10:57:30 CST 2025