Index extents
Kevin asked why detached indexes in IDS 9.30 on HP-UX sometimes end up in two extents, how Informix decides index extent size, and whether splitting matters, noting you can't set index extent size explicitly. Respondents explained the size is derived internally from the table's first/next extent sizes scaled by the ratio of key size (plus 9 bytes detached / 5 attached) to row size, reduced 20% for non-unique indexes, with a 4-page minimum; extra extents are grabbed when contiguous free space is short, and adjacent extents get merged. Advice: keep table initial extent sizes modest since they drive index sizing too. No further action needed.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Using IDS 9.30FC2 on an HP-UX 11.0 box. in creating detached indexes, I have noticed that sometimes the index has multiple extents in a particular dbspace. For example: prddwh:'dw_admin'.fl_keylinks 943776 40 prddwh:'dw_admin'.fl_keylinks_n1 139338 8 prddwh:'dw_admin'.fl_keylinks_n1 148623 8 prddwh:'dw_admin'.fl_shipto 943592 24 prddwh:'dw_admin'.fl_shipto_n1 994962 4 prddwh:'dw_admin'.fl_shipto_n1 58472 4 The index 'fl_keylinks_n1' and 'fl_shipto_n1' are split into 2 extents. I know I can't set the extent size as I can a table. Is it advantageous to split them up? I'm not sure what logic is used in determining where and how large the index extent should be. It looks as though it finds the first available opening in the dbspace and uses it. If it doesn't all fit there, look for the next available opening. Hmmm... TIA, Kevin Struckhoff Yamaha Motors U.S. kevin_struckhoff@yamaha-motor.com kevin_
Kevin, I believe even on detached indices, the extent size while be the same extent size as the table and all the usual rules apply.... if it can't get the whole extent size (be it 1st extent or next), it will take what it can get... if it needs more, it'll get another extent. if it can, it will concatenate extents that are contiguous..... but I could be wrong (I'm on 7, not 9), it should be in the admin books, or others may confirm with answers. Norma Jean -----Original Message----- From: kevinstruckhoff@yahoo.com [mailto:kevinstruckhoff@yahoo.com] Sent: Tuesday, August 19, 2003 5:24 PM To: ids@iiug.org; forum.subscriber@iiug.org Subject: Index extents [1725] Using IDS 9.30FC2 on an HP-UX 11.0 box. in creating detached indexes, I have noticed that sometimes the index has multiple extents in a particular dbspace. For example: prddwh:'dw_admin'.fl_keylinks 943776 40 prddwh:'dw_admin'.fl_keylinks_n1 139338 8 prddwh:'dw_admin'.fl_keylinks_n1 148623 8 prddwh:'dw_admin'.fl_shipto 943592 24 prddwh:'dw_admin'.fl_shipto_n1 994962 4 prddwh:'dw_admin'.fl_shipto_n1 58472 4 The index 'fl_keylinks_n1' and 'fl_shipto_n1' are split into 2 extents. I know I can't set the extent size as I can a table. Is it advantageous to split them up? I'm not sure what logic is used in determining where and how large the index extent should be. It looks as though it finds the first available opening in the dbspace and uses it. If it doesn't all fit there, look for the next available opening. Hmmm... TIA, Kevin Struckhoff Yamaha Motors U.S. kevin_struckhoff@yamaha-motor.com kevin_ --openmail-part-40e4b3f6-00000002 Content-Type: application/rtf Content-Disposition: attachment; filename="BDY.RTF" ;Creation-Date="Wed, 20 Aug 2003 07:48:56 -0500" Content-Transfer-Encoding: base64 {\\rtf1\\ansi\\ansicpg1252\\fromtext \\deff0{\\fonttbl {\\f0\\fswiss Arial;} {\\f1\\fmodern Courier New;} {\\f2\\fnil\\fcharset2 Symbol;} {\\f3\\fmodern\\fcharset0 Courier New;}} {\\colortbl\\red0\\green0\\blue0;\\red0\\green0\\blue255;} \\uc1\\pard\\plain\\deftab360 \\f0\\fs20 Kevin,\\par \\par I believe even on detached indices, the extent size while be the same extent size as the table and all the usual rules apply....\\par if it can't get the whole extent size (be it 1st extent or next), it will take what it can get... if it needs more, it'll get another extent. if it can, it will concatenate extents that are contiguous.....\\par \\par but I could be wrong (I'm on 7, not 9), it should be in the admin books, or others may confirm with answers.\\par \\par Norma Jean\\par \\par \\par -----Original Message-----\\par From: kevinstruckhoff@yahoo.com [mailto:kevinstruckhoff@yahoo.com]\\par Sent: Tuesday, August 19, 2003 5:24 PM\\par To: ids@iiug.org; forum.subscriber@iiug.org\\par Subject: Index extents [1725]\\par \\par \\par Using IDS 9.30FC2 on an HP-UX 11.0 box.\\par \\par in creating detached indexes, I have noticed that sometimes the index has multiple extents in a particular dbspace. For example:\\par \\par prddwh:'dw_admin'.fl_keylinks 943776 40\\par prddwh:'dw_admin'.fl_keylinks_n1 139338 8\\par prddwh:'dw_admin'.fl_keylinks_n1 148623 8\\par prddwh:'dw_admin'.fl_shipto 943592 24\\par prddwh:'dw_admin'.fl_shipto_n1 994962 4\\par prddwh:'dw_admin'.fl_shipto_n1 58472 4\\par \\par \\par The index 'fl_keylinks_n1' and 'fl_shipto_n1' are split into 2 extents. I know I can't set the extent size as I can a table. \\par \\par Is it advantageous to split them up? I'm not sure what logic is used in determining where and how large the index extent should be. It looks as though it finds the first available opening in the dbspace and uses it. If it doesn't all fit there, look for the next available opening. Hmmm...\\par \\par TIA,\\par \\par Kevin Struckhoff\\par Yamaha Motors U.S.\\par kevin_struckhoff@yamaha-motor.com\\par \\par kevin_\\par \\par } --openmail-part-40e4b3f6-00000002--
Informix uses an internal means to calculate first / next extent for an index, based on first / next extent of the related table. At this point, there is no way to directly control extent sizing for an index. -----Original Message----- From: KEVIN STRUC.... [mailto:kevinstruckhoff@yahoo.com] Sent: Tuesday, August 19, 2003 6:24 PM To: ids@iiug.org Subject: Index extents [1725] Using IDS 9.30FC2 on an HP-UX 11.0 box. in creating detached indexes, I have noticed that sometimes the index has multiple extents in a particular dbspace. For example: prddwh:'dw_admin'.fl_keylinks 943776 40 prddwh:'dw_admin'.fl_keylinks_n1 139338 8 prddwh:'dw_admin'.fl_keylinks_n1 148623 8 prddwh:'dw_admin'.fl_shipto 943592 24 prddwh:'dw_admin'.fl_shipto_n1 994962 4 prddwh:'dw_admin'.fl_shipto_n1 58472 4 The index 'fl_keylinks_n1' and 'fl_shipto_n1' are split into 2 extents. I know I can't set the extent size as I can a table. Is it advantageous to split them up? I'm not sure what logic is used in determining where and how large the index extent should be. It looks as though it finds the first available opening in the dbspace and uses it. If it doesn't all fit there, look for the next available opening. Hmmm... TIA, Kevin Struckhoff Yamaha Motors U.S. kevin_struckhoff@yamaha-motor.com kevin_ "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."
This is a multipart message in MIME format. --=_related 004DB9CC86256D88_= Content-Type: multipart/alternative; boundary="=_alternative 004DB9CC86256D88_=" --=_alternative 004DB9CC86256D88_= Content-Type: text/plain; charset="us-ascii" Actually, the extent size for indexes is not the same as the table, but is based on the field size(s) of the columns included in the index. This is automatically calculated by Informix and cannot be set. "NormaJean.S...." <NormaJean.Sebastian@tellabs.com> Sent by: forum.subscriber@iiug.org 08/20/2003 07:51 AM To: ids@iiug.org cc: Subject: RE: Index extents [1728] Kevin, I believe even on detached indices, the extent size while be the same extent size as the table and all the usual rules apply.... if it can't get the whole extent size (be it 1st extent or next), it will take what it can get... if it needs more, it'll get another extent. if it can, it will concatenate extents that are contiguous..... but I could be wrong (I'm on 7, not 9), it should be in the admin books, or others may confirm with answers. Norma Jean -----Original Message----- From: kevinstruckhoff@yahoo.com [mailto:kevinstruckhoff@yahoo.com] Sent: Tuesday, August 19, 2003 5:24 PM To: ids@iiug.org; forum.subscriber@iiug.org Subject: Index extents [1725] Using IDS 9.30FC2 on an HP-UX 11.0 box. in creating detached indexes, I have noticed that sometimes the index has multiple extents in a particular dbspace. For example: prddwh:'dw_admin'.fl_keylinks 943776 40 prddwh:'dw_admin'.fl_keylinks_n1 139338 8 prddwh:'dw_admin'.fl_keylinks_n1 148623 8 prddwh:'dw_admin'.fl_shipto 943592 24 prddwh:'dw_admin'.fl_shipto_n1 994962 4 prddwh:'dw_admin'.fl_shipto_n1 58472 4 The index 'fl_keylinks_n1' and 'fl_shipto_n1' are split into 2 extents. I know I can't set the extent size as I can a table. Is it advantageous to split them up? I'm not sure what logic is used in determining where and how large the index extent should be. It looks as though it finds the first available opening in the dbspace and uses it. If it doesn't all fit there, look for the next available opening. Hmmm... TIA, Kevin Struckhoff Yamaha Motors U.S. kevin_struckhoff@yamaha-motor.com kevin_ --openmail-part-40e4b3f6-00000002 Content-Type: application/rtf Content-Disposition: attachment; filename="BDY.RTF" ;Creation-Date="Wed, 20 Aug 2003 07:48:56 -0500" Content-Transfer-Encoding: base64 {\\rtf1\\ansi\\ansicpg1252\\fromtext \\deff0{\\fonttbl {\\f0\\fswiss Arial;} {\\f1\\fmodern Courier New;} {\\f2\\fnil\\fcharset2 Symbol;} {\\f3\\fmodern\\fcharset0 Courier New;}} {\\colortbl\\red0\\green0\\blue0;\\red0\\green0\\blue255;} \\uc1\\pard\\plain\\deftab360 \\f0\\fs20 Kevin,\\par \\par I believe even on detached indices, the extent size while be the same extent size as the table and all the usual rules apply....\\par if it can't get the whole extent size (be it 1st extent or next), it will take what it can get... if it needs more, it'll get another extent. if it can, it will concatenate extents that are contiguous.....\\par \\par but I could be wrong (I'm on 7, not 9), it should be in the admin books, or others may confirm with answers.\\par \\par Norma Jean\\par \\par \\par -----Original Message-----\\par From: kevinstruckhoff@yahoo.com [mailto:kevinstruckhoff@yahoo.com]\\par Sent: Tuesday, August 19, 2003 5:24 PM\\par To: ids@iiug.org; forum.subscriber@iiug.org\\par Subject: Index extents [1725]\\par \\par \\par Using IDS 9.30FC2 on an HP-UX 11.0 box.\\par \\par in creating detached indexes, I have noticed that sometimes the index has multiple extents in a particular dbspace. For example:\\par \\par prddwh:'dw_admin'.fl_keylinks 943776 40\\par prddwh:'dw_admin'.fl_keylinks_n1 139338 8\\par prddwh:'dw_admin'.fl_keylinks_n1 148623 8\\par prddwh:'dw_admin'.fl_shipto 943592 24\\par prddwh:'dw_admin'.fl_shipto_n1 994962 4\\par prddwh:'dw_admin'.fl_shipto_n1 58472 4\\par \\par \\par The index 'fl_keylinks_n1' and 'fl_shipto_n1' are split into 2 extents. I know I can't set the extent size as I can a table. \\par \\par Is it advantageous to split them up? I'm not sure what logic is used in determining where and how large the index extent should be. It looks as though it finds the first available opening in the dbspace and uses it. If it doesn't all fit there, look for the next available opening. Hmmm...\\par \\par TIA,\\par \\par Kevin Struckhoff\\par Yamaha Motors U.S.\\par kevin_struckhoff@yamaha-motor.com\\par \\par kevin_\\par \\par } --openmail-part-40e4b3f6-00000002-- --=_alternative 004DB9CC86256D88_= Content-Type: text/html; charset="us-ascii" <br><font size=2 face="sans-serif">Actually, the extent size for indexes is not the same as the table, but is based on the field size(s) of the columns included in the index. This is automatically calculated by Informix and cannot be set.</font> <br><font size=2 face="sans-serif"><br> </font><img src=cid:_1_013000005928004DB9CC86256D88> <br> <br> <br> <table width=100%> <tr valign=top> <td> <td><font size=1 face="sans-serif"><b>"NormaJean.S...." <NormaJean.Sebastian@tellabs.com></b></font> <br><font size=1 face="sans-serif">Sent by: forum.subscriber@iiug.org</font> <p><font size=1 face="sans-serif">08/20/2003 07:51 AM</font> <br> <td><font size=1 face="Arial"> </font> <br><font size=1 face="sans-serif"> To: ids@iiug.org</font> <br><font size=1 face="sans-serif"> cc: </font> <br><font size=1 face="sans-serif"> Subject: RE: Index extents [1728]</font></table> <br> <br> <br><font size=2 face="Courier New">Kevin,<br> <br> I believe even on detached indices, the extent size while be the same<br> extent size as the table and all the usual rules apply....<br> if it can't get the whole extent siz
Index extent and next sizes are determined by the ratio of the index's key to the rowsize multiplied by the table's extent and next sizes. Extent compression pertains so if the free extents in the dbspace are not fragmented the index will be allocated adjacent extents which will be merged into one larger one which is why sometimes the actual extent sizes seem to be random. Art S. Kagel ----- Original Message ----- From: Kevin Struc.... <kevinstruckhoff@yahoo.com> At: 8/19 19:36 > Using IDS 9.30FC2 on an HP-UX 11.0 box. > > in creating detached indexes, I have noticed that sometimes the index has > multiple extents in a particular dbspace. For example: > > prddwh:'dw_admin'.fl_keylinks 943776 40 > prddwh:'dw_admin'.fl_keylinks_n1 139338 8 > prddwh:'dw_admin'.fl_keylinks_n1 148623 8 > prddwh:'dw_admin'.fl_shipto 943592 24 > prddwh:'dw_admin'.fl_shipto_n1 994962 4 > prddwh:'dw_admin'.fl_shipto_n1 58472 4 > > > The index 'fl_keylinks_n1' and 'fl_shipto_n1' are split into 2 extents. I know I > can't set the extent size as I can a table. > > Is it advantageous to split them up? I'm not sure what logic is used in > determining where and how large the index extent should be. It looks as though > it finds the first available opening in the dbspace and uses it. If it doesn't > all fit there, look for the next available opening. Hmmm... > > TIA, > > Kevin Struckhoff > Yamaha Motors U.S. > kevin_struckhoff@yamaha-motor.com > > kevin_
Actually, as someone pointed out ...the size of the extent of the indexes are calculated based on the first extent of the table .... I had posted the calculation earlier in the same group long back ... here it is again ... Extentsize = Initial Extent Size of Table * ((keysize + 9 bytes (for detached) or 5 bytes (for attached)) / <table rowsize>) If it is a Non-Unique Index the Extentsize is reduced by 20% with the calculation , Extentsize *= 0.8; The function to return the extentsize returns the MAX(Extentsize, 4 pages). That's why when you migrate from 7.x --> 9.x suddenly you see much more space being used ..since this calculation is done for FULLY DETACHED indexes and in 9.x the indexes are detached by default. That's why when you do a DBEXPORT/DBIMPORT of a database, it is important to look into the INITIAL EXTENTSIZE of the table ..if you don't really require a huge INITIAL EXTENTSIZE it is better to reduce it since the EXTENTSIZE will not only affect the TABLE extent allocation but the INDEX extent allocation as well ...:-) Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi "Stephanie_P...." <Stephanie_Peltier@txnp.us To: ids@iiug.org courts.gov> cc: Sent by: Subject: RE: Index extents [1730] forum.subscriber@iiug.org 08/20/2003 07:12 AM This is a multipart message in MIME format. --=_related 004DB9CC86256D88_= Content-Type: multipart/alternative; boundary="=_alternative 004DB9CC86256D88_=" --=_alternative 004DB9CC86256D88_= Content-Type: text/plain; charset="us-ascii" Actually, the extent size for indexes is not the same as the table, but is based on the field size(s) of the columns included in the index. This is automatically calculated by Informix and cannot be set. "NormaJean.S...." <NormaJean.Sebastian@tellabs.com> Sent by: forum.subscriber@iiug.org 08/20/2003 07:51 AM To: ids@iiug.org cc: Subject: RE: Index extents [1728] Kevin, I believe even on detached indices, the extent size while be the same extent size as the table and all the usual rules apply.... if it can't get the whole extent size (be it 1st extent or next), it will take what it can get... if it needs more, it'll get another extent. if it can, it will concatenate extents that are contiguous..... but I could be wrong (I'm on 7, not 9), it should be in the admin books, or others may confirm with answers. Norma Jean -----Original Message----- From: kevinstruckhoff@yahoo.com [mailto:kevinstruckhoff@yahoo.com] Sent: Tuesday, August 19, 2003 5:24 PM To: ids@iiug.org; forum.subscriber@iiug.org Subject: Index extents [1725] Using IDS 9.30FC2 on an HP-UX 11.0 box. in creating detached indexes, I have noticed that sometimes the index has multiple extents in a particular dbspace. For example: prddwh:'dw_admin'.fl_keylinks 943776 40 prddwh:'dw_admin'.fl_keylinks_n1 139338 8 prddwh:'dw_admin'.fl_keylinks_n1 148623 8 prddwh:'dw_admin'.fl_shipto 943592 24 prddwh:'dw_admin'.fl_shipto_n1 994962 4 prddwh:'dw_admin'.fl_shipto_n1 58472 4 The index 'fl_keylinks_n1' and 'fl_shipto_n1' are split into 2 extents. I know I can't set the extent size as I can a table. Is it advantageous to split them up? I'm not sure what logic is used in determining where and how large the index extent should be. It looks as though it finds the first available opening in the dbspace and uses it. If it doesn't all fit there, look for the next available opening. Hmmm... TIA, Kevin Struckhoff Yamaha Motors U.S. kevin_struckhoff@yamaha-motor.com kevin_ --openmail-part-40e4b3f6-00000002 Content-Type: application/rtf Content-Disposition: attachment; filename="BDY.RTF" ;Creation-Date="Wed, 20 Aug 2003 07:48:56 -0500" Content-Transfer-Encoding: base64 {\\rtf1\\ansi\\ansicpg1252\\fromtext \\deff0{\\fonttbl {\\f0\\fswiss Arial;} {\\f1\\fmodern Courier New;} {\\f2\\fnil\\fcharset2 Symbol;} {\\f3\\fmodern\\fcharset0 Courier New;}} {\\colortbl\\red0\\green0\\blue0;\\red0\\green0\\blue255;} \\uc1\\pard\\plain\\deftab360 \\f0\\fs20 Kevin,\\par \\par I believe even on detached indices, the extent size while be the same extent size as the table and all the usual rules apply....\\par if it can't get the whole extent size (be it 1st extent or next), it will take what it can get... if it needs more, it'll get another extent. if it can, it will concatenate extents that are contiguous.....\\par \\par but I could be wrong (I'm on 7, not 9), it should be in the admin books, or others may confirm with answers.\\par \\par Norma Jean\\par \\par \\par -----Original Message-----\\par From: kevinstruckhoff@yahoo.com [mailto:kevinstruckhoff@yahoo.com]\\par Sent: Tuesday, August 19, 2003 5:24 PM\\par To: ids@iiug.org; forum.subscriber@iiug.org\\par Subject: Index extents [1725]\\par \\par \\par Using IDS 9.30FC2 on an HP-UX 11.0 box.\\par \\par in creating detached indexes, I have noticed that sometimes the index has multiple extents in a particular dbspace. For example:\\par \\par prddwh:'dw_admin'.fl_keylinks 943776 40\\par prddwh:'dw_admin'.fl_keylinks_n1 139338 8\\par prddwh:'dw_admin'.fl_keylinks_n1 148623 8\\par prddwh:'dw_admin'.fl_shipto 943592 24\\par prddwh:'dw_admin'.fl_shipto_n1 994962 4\\par prddwh:'dw_admin'.fl_shipto_n1 58472 4\\par \\par \\par The index 'fl_keylinks_n1' and 'fl_shipto_n1' are split into 2 extents. I know I can't set the extent size as I can a table. \\par \\par Is it advantageous to split them up? I'm not sure what logic is used in determining where and how large the index extent should be. It looks as though it finds the first available opening in the dbspace and uses it. If it doesn't all fit there, look for the next available opening. Hmmm...\\par \\par TIA,\\par \\par Kevin Struckhoff\\par Yamaha Motors U.S.\\par kevin_struckhoff@yamaha-motor.com\\par \\par kevin_\\par \\par } --openmail-part-40e4b3f6-00000002-- --=_alter