PCTUSED for create index
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: PCTUSED for create index

  1. #1
    Join Date
    Mar 2001
    Posts
    286
    In Oracle doc, there is a restriction for create index.

    Restriction: You cannot specify the PCTUSED parameter for an index.

    Why?

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    Combination of PCTUSED and PCTFREE allow the rows to remain in the same block in case the row length changes. PCTUSED dictates the amount of space reserved for such updates to rows of a table that may cause the row length to increase.

    For indexes, if the data in the indexed column changes, position of the row in the index tree changes and it has to assume a new location within the tree - necessarily meaning migrating the entry in the index.
    svk

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Because there is no freelists with indexes as there are with tables.

    Internaly index blocks are managed totaly differently as tables blocks. When you delete a row from a table, that block can be put on freelist (ie it is made available for new inserts) if the used space in a block falls below PCTUSED setting. However keep in mind that for table data it is not important in which block physicaly new rows are placed. On the other hand, indexes must be organized in "sorted" orders, so it is very important for the database into which blocks to store new index entries physicaly. That is why there could be no free lists for indexes - new index entry must be physically stored into exactly that specific leaf block that correspond to the new index entry (if there is no more physical space in that block Oracle will create more space by splitting that block).

    So: new index entry must be stored in the correct physical block => no need for free lists => no need for PCTUSED.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Mar 2001
    Posts
    286
    Thank you very much!

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by jmodic
    Because there is no freelists with indexes as there are with tables.
    Anne Robinsson would have voted you the weakest link for this thread :-)

    You can create indexes with freelists. I at least do. The reason why there is no pctused for indexes is because all items in an idex must be in the right order so that the index would work, Oracle should be able to place an index entry into a block regardless of anything.



  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    the problem with index freelists is that a index block is only put into freelist if it is COMPLETELY empty, it does not work like tables where because of delete and update activities the block is linked and unlinked from freelist
    so setting freelists for index is pretty useless tho
    and if you check the SQL reference there isnt a single freelist word in create index and alter index sections

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by julian
    Anne Robinsson would have voted you the weakest link for this thread :-)
    I'm sure she is a nice lady, but I have no idea who she is, so I guess that was ment as a compliment
    You can create indexes with freelists.
    I stand corrected, you are right. You can specify freelists also with indexes. If you don't (and most of us never do), one freelist (and one freelist grup) per index will be created for you automatically. And this single freelist is exactly what one index need, so there is no need to bother with specifying additional freelist.
    I at least do.
    Good for you. I'm sure you feel much better that way, but can you give us any reson for this?
    The reason why there is no pctused for indexes is because all items in an idex must be in the right order so that the index would work, Oracle should be able to place an index entry into a block regardless of anything.
    That's what I have said.

    Maybe one additional variation of an answer to the original question: "why can't we specify the PCTUSED parameter for an index?".
    Answer: Because the only valid value would be PCTUSED=0 (index block must be totaly empty to be put on the freelist). So if there is only one allowable value there is no point in using this parameter for the index.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    I'm sure she is a nice lady, but I have no idea who she is, so I guess that was ment as a compliment
    :-) No, Anne Robinsson is not a very nice lady, not very nice to the Weakest Link players at least. You've never watched "The Weakly Link"?

    I'm sure you feel much better that way, but can you give us any reson for this?
    I do that in OPS only. Having multiple process free lists improves performance in OPS in which several processes are concurrently inserting or updating the same object. At the same time when HWM for a segment is raised I prefered to have more blocks alocated. You probably know that the amount of transfered blocks is 5*(1+number of free lists).

    But of course, in general, having multiple free lists (besides the master free) list is meaningless.


  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by julian
    Having multiple process free lists improves performance in OPS in which several processes are concurrently inserting or updating the same object.
    Even in OPS I don't think additional freelists on indexes have any beneficial influence on performance. A quote from Steve Adams (and I don't see how OPS would make any difference in what he says):

    ***********************************************
    Contention for index leaf and branch blocks is always due to hot key ranges. A special case of this is when monotonically increasing key values are inserted intensively - this is called the "right-hand index" problem. Having multiple freelists cannot affect contention for blocks in a hot key range, because all the contending activity is constrained to hot blocks by virtue of the key values.

    If you have intensive inserts but no hot key ranges, and if distinct block split operations each require a free block simultaneously, then having multiple freelists will not help you either. The processes will contend for the freelist header block (normally the segment header) regardless of whether they are using the same freelist or not. The way the address this problem is to partition the index. Using multiple freelists does not work, and using multiple freelist groups is messy.
    ***********************************************
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Yes Jurij, I have read before Steve's stuff from http://www.ixora.com.au/q+a/0102/21215036.htm and the right-hand index problem :-)

    The long version:

    In OPS you might come to a situation where only one free index block is left below the HWM in a segment created with only with the default master free list. If multiple instances insert data they will first use that block until the HWM is raised with 5 blocks (5 = 5*(1+0)). So you might have consequent entries in the same block. This might cause pinging of that block depending on the logic of the application (in many application that is often the case). Assume Jurij that you create your index segments wih frelists and moreover with freelist groups (it is recommended to have as many freelist groups as many instances). Say you create the segments with 4 freelists. Then each time the HWM is raised you will have 25 blocks transfered. All belonging to the 5 freelists (1 master + 4 normal). Then you reduce the probability that the instances might strive for the same block at the same time, something you want to avoid. In 9i, you wodn't bother much with that, since even if both instances need the same index block, it will "travel" via the Interconnect and performance will not be decreased. But in 8i for example, this activity, called false pinging will occur and it will eventuall leed the DBA to reorgranizing the indexes. After all, creating index segments with multiple freelists and freelist groups will not improve much perforance, even in OPS, as you guessed corectlly.

    The short version:

    All tables and index segments free lists should be set to the HWM of concurrent INSERT or UPDATE activity. Too low value for free lists will or might cause poor Oracle performance.

    If you are more interested in the topic have a look at:

    http://download-west.oracle.com/otnd...gn.htm#1020788

    I hope I could meet you at some Oracle seminar or ILT. It will be interesting to talk Oracle with you.


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