CREATE INDEX from a HUGE TABLE

Hi, I have a task of creating 2 index on a big table. OS: Linux Memory of the Box: 1TB TEMP DBspace: 160GB Below is the index: CREATE UNIQUE INDEX IF NOT EXISTS event_store.uidx_usage_event_1 onusage_event (event_instance_id, event_time) ONLINE; ALTER TABLE event_store.usage_event ADD CONSTRAINT UNIQUE (event_instance_id,event_time) CONSTRAINT uc_usage_event_1 ; Table Size: select dbsname, tabname, count(*) num_of_extents, sum( pe_size ) total_size_pages, (sum( pe_size ) * 16)/1024/1024 total_size_gb from systabnames, sysptnext where partnum = pe_partnum and tabname matches 'usage_event*' group by 1, 2 order by 3 desc, 4 desc;> > > > > > > > > tabname usage_event num_of_extents 1241 total_size_pages 91165357 total_size_gb 1391.072952270508 Now, after kicking off the script, I'm monitoring the session and so far it has done more than the total_size_pages read. Userthreads address flags sessid user tty wait tout locks nreads nwrites 262ca10a8 --B-R-- 32357566 informix - 0 0 976 109168752 0 I believe it has read the whole table already in memory and did sorting of it. But is there a way to calculate how much more read it will do, or how many more pass it need to cycle through the table to complete the index? Thanks for your time.

Are you sure your query returns the information on the table correctly?Just out of my curiosity, the total number of pages (?) is 91165357 then I wonder how the size of the table gets up to 1391GB, also the number of extents is interesting, can it ever get to that number?Anyway, to create index(es) on a large table, perhaps utilizing PDQ would help to speed up, and create it "online" then it won't lock your table. tabname usage_event num_of_extents 1241 total_size_pages 91165357 total_size_gb 1391.072952270508 Let's go GreenThis email contains 100% recycled electrons. From: ALEXANDER CABANGCALAN <a.b.cabangcalan@gmail.com> To: ids@iiug.org Sent: Tuesday, March 21, 2017 10:56 PM Subject: CREATE INDEX from a HUGE TABLE [38790] Hi, I have a task of creating 2 index on a big table. OS: Linux Memory of the Box: 1TB TEMP DBspace: 160GB Below is the index: CREATE UNIQUE INDEX IF NOT EXISTS event_store.uidx_usage_event_1 onusage_event (event_instance_id, event_time) ONLINE; ALTER TABLE event_store.usage_event ADD CONSTRAINT UNIQUE (event_instance_id,event_time) CONSTRAINT uc_usage_event_1 ; Table Size: select dbsname, tabname, count(*) num_of_extents, sum( pe_size ) total_size_pages, (sum( pe_size ) * 16)/1024/1024 total_size_gb from systabnames, sysptnext where partnum = pe_partnum and tabname matches 'usage_event*' group by 1, 2 order by 3 desc, 4 desc;> > > > > > > > > tabname usage_event num_of_extents 1241 total_size_pages 91165357 total_size_gb 1391.072952270508 Now, after kicking off the script, I'm monitoring the session and so far it has done more than the total_size_pages read. Userthreads address flags sessid user tty wait tout locks nreads nwrites 262ca10a8 --B-R-- 32357566 informix - 0 0 976 109168752 0 I believe it has read the whole table already in memory and did sorting of it. But is there a way to calculate how much more read it will do, or how many more pass it need to cycle through the table to complete the index? Thanks for your time. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Kern: FYI, since v11.70 the limit on the number of extents has virtually vanished. That limit was imposed by the fact that prior releases had to store all of a table's details (special columns, extents, attached index keys, row and page counts, etc.) on a single page. So extents could not exceed about 360 on a 2K page. However, since 11.70 the engine will add a "next" page to the partition header to contain additional extent entries. The new limit is 32765 extents which with the 2^24 page count limit and extent doubling behavior means you really cannot ever "run out" of extents anymore. So, yes, 1200+ extents is possible. Art

Thank you Art for the info. Let me ask, with version 11.70 and going forward, do you know if we still have the 32G (2k page) table size limitation? Let's go GreenThis email contains 100% recycled electrons. From: ART KAGEL <art.kagel@gmail.com> To: ids@iiug.org Sent: Friday, March 31, 2017 1:38 PM Subject: Re: CREATE INDEX from a HUGE TABLE [38836] Kern: FYI, since v11.70 the limit on the number of extents has virtually vanished. That limit was imposed by the fact that prior releases had to store all of a table's details (special columns, extents, attached index keys, row and page counts, etc.) on a single page. So extents could not exceed about 360 on a 2K page. However, since 11.70 the engine will add a "next" page to the partition header to contain additional extent entries. The new limit is 32765 extents which with the 2^24 page count limit and extent doubling behavior means you really cannot ever "run out" of extents anymore. So, yes, 1200+ extents is possible. Art ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Yes. That limitation is per partition/fragment though not strictly per table and the limit is 2^24-1 pages or 16,777,215 pages. The reason for that, and why it will never go away, is that a ROWID is 32 bits but the low order 8 bits are the slot number on the page leaving only 24 bits for the page number. The internal 32 bit ROWID (even for partitioned tables which don't have exposed ROWIDs) is deep in the code so it is unlikely to be expanded to 40 bits or more. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 31, 2017 at 3:17 PM, Kern Doe <kern_doe@yahoo.com> wrote: > Thank you Art for the info. Let me ask, with version 11.70 and going > forward, do you know if we still have the 32G (2k page) table size > limitation? > > > > > > *Let's go Green* > *This email contains 100% recycled electrons.* > > > ------------------------------ > *From:* ART KAGEL <art.kagel@gmail.com> > *To:* ids@iiug.org > *Sent:* Friday, March 31, 2017 1:38 PM > *Subject:* Re: CREATE INDEX from a HUGE TABLE [38836] > > Kern: > > FYI, since v11.70 the limit on the number of extents has virtually > vanished. > That limit was imposed by the fact that prior releases had to store all of > a > table's details (special columns, extents, attached index keys, row and > page > counts, etc.) on a single page. So extents could not exceed about 360 on a > 2K > page. However, since 11.70 the engine will add a "next" page to the > partition > header to contain additional extent entries. The new limit is 32765 > extents > which with the 2^24 page count limit and extent doubling behavior means > you > really cannot ever "run out" of extents anymore. So, yes, 1200+ extents is > possible. > > Art > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > --94eb2c0d29403ba721054c0bd33a

OK Art, thank you! Let's go GreenThis email contains 100% recycled electrons. From: Art Kagel <art.kagel@gmail.com> To: ids@iiug.org Sent: Friday, March 31, 2017 3:33 PM Subject: Re: CREATE INDEX from a HUGE TABLE [38840] Yes. That limitation is per partition/fragment though not strictly per table and the limit is 2^24-1 pages or 16,777,215 pages. The reason for that, and why it will never go away, is that a ROWID is 32 bits but the low order 8 bits are the slot number on the page leaving only 24 bits for the page number. The internal 32 bit ROWID (even for partitioned tables which don't have exposed ROWIDs) is deep in the code so it is unlikely to be expanded to 40 bits or more. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 31, 2017 at 3:17 PM, Kern Doe <kern_doe@yahoo.com> wrote: > Thank you Art for the info. Let me ask, with version 11.70 and going > forward, do you know if we still have the 32G (2k page) table size > limitation? > > > > > > *Let's go Green* > *This email contains 100% recycled electrons.* > > > ------------------------------ > *From:* ART KAGEL <art.kagel@gmail.com> > *To:* ids@iiug.org > *Sent:* Friday, March 31, 2017 1:38 PM > *Subject:* Re: CREATE INDEX from a HUGE TABLE [38836] > > Kern: > > FYI, since v11.70 the limit on the number of extents has virtually > vanished. > That limit was imposed by the fact that prior releases had to store all of > a > table's details (special columns, extents, attached index keys, row and > page > counts, etc.) on a single page. So extents could not exceed about 360 on a > 2K > page. However, since 11.70 the engine will add a "next" page to the > partition > header to contain additional extent entries. The new limit is 32765 > extents > which with the 2^24 page count limit and extent doubling behavior means > you > really cannot ever "run out" of extents anymore. So, yes, 1200+ extents is > possible. > > Art > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > --94eb2c0d29403ba721054c0bd33a ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.