Space usage during index creation
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Space usage during index creation

  1. #1
    Join Date
    Oct 2004
    Posts
    14

    Question Space usage during index creation

    Hello All,

    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.)

    This link shows how Oracle uses the tablespace space during this procees:
    http://ca.pg.photos.yahoo.com/ph/gab...&.src=ph<br />
    Can someone please help me answer the following questions?

    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)?

    Thanks for your help vey much in advance,

    Gabor
    Last edited by Gabor; 10-31-2004 at 04:05 PM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  3. #3
    Join Date
    Oct 2004
    Posts
    14
    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?

    Thanks again,

    Gabor
    Attached Images Attached Images

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    well the index has to be stored somewhere doesnt it?

    So it is being written to disk as it is being created - as to why it is larger than the final index size I cannot say.

    Hoever after this post I looked at your picture, and it shows free space in the tablespace going down, this is normal.

    It's not usage going down, its free space
    Last edited by davey23uk; 10-31-2004 at 06:02 PM.

  5. #5
    Join Date
    Oct 2004
    Posts
    14
    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?

    Thanks very much,

    Gabor

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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.

  7. #7
    Join Date
    Oct 2004
    Posts
    14
    Well, an index segment needs free space agree?
    Agreed. But where?
    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"?

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by Gabor
    Agreed. But where?

    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?!

  9. #9
    Join Date
    Oct 2004
    Posts
    14
    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?

    Thanks,

    Gabor

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width