I have created an index in a permanent tablespace called IND6_TEMP.
(Yes, it's a bad name, because the tablespace is not temporary. But
this, of course, should not matter.)
1. Why is Oracle switching in the middle of the process (as opposed
to the end of the process) from using the temporary tablespace (TEMP)
to using the permanent tablespace (IND6_TEMP)?
2. What parameter specifies when the above switch occurs?
3. What are the advantages and/or disadvantages of changing this
parameter? What value should it be set to?
4. Why is Oracle using more space in the permanent tablespace while
the index is being built than the size of the index? How much more is
it using and how can that be controlled (if possible)?
5. What document describes the answers to the above questions (so I
can learn more about this area)?
basically when the index is being created it needs to do some sorting of the data. If the amount of memory needed is more than the sort_area_size parameter (or pga_aggregate_target) then the sorting will be done in the temp tablespace.
That is why you see the switch between the tablespaces during creation
Thanks for the quick response!
However, I still don't see this clearly. Perhaps partly because I did not mention that the database version is 8.1.7.4.
In fact, you responded so quickly that (this being my first ever post on this forum) I couldn't even get the link right which shows the photo. So now that is fixed and hopefully that makes the questions clearer.
If the amount of memory needed is more than the sort_area_size parameter (or pga_aggregate_target) then the sorting will be done in the temp tablespace.
Right, but then why am I seeing the permanent tablespace being used? And why do I need to have more free space in the permanent tablespace than what the final product (the index) needs?
The index has to be stored somewhere doesn't it? So it is being written to disk as it is being created
Yes. My understanding is that free space in the temp tablespace decreases due to this. But why does Oracle start to decrease free space in the permanent tablespace and stop using the temp tablespace?
Well an index segment needs free space agree? After sorting data temp space in temporary tablespace is freed but your index needs free space in permanent tablespace.
Agreed too. Emphasis on "After"!!!! But wy "during"?
Permanent tablespace
Of course you need space during index creation, an index can be GB and hundreds of extents, you dont expect Oracle assigns GB and creates hundreds of extents of space in few seconds do you?!
I certainly understand that indexes need storage. I would therefore expect that as Oracle is building the index, it uses more and more storage until finally the index is built.
However, if you look at the picture I attached originally, it shows the following:
a.) The storage used by Oracle is in the temporary tablespace (TEMP) and sometime into the building process it starts using the permanent tablespace (IND6_TEMP) as well. Once the building process is done and the index is ready, Oracle cleans up the temporary tablespace and all the storage used by the index is in the permanent tablespace, as expected. So one of my questions is not why it is using storage, rather, why is it using two tablespaces instead of one and how to control the amount it uses in each?
b.) Oracle uses more tablespace space during the index building process than the size of the idex when it is ready. Why is that? Overhead? Can it be calculated or at least estimated in order not to run out of space while building the index? Can it be controlled?
when oracle creates an index it first need to full scan the table and sort the data that's why you see temp tablespace usage, sort is done in temporary tablespace and NOT parmanent