Index creation progress

Hi all, How could I determine how much time it would take to create this index? Table has row = 128 million records This one Index is on 3 columns ( col1 char(20), col2 varchar(254) , col3 integer) Please let me know. Thanks. Regards, Jose

Jo wrote: > Hi all, > > How could I determine how much time it would take to create this > index? > > Table has row = 128 million records > This one Index is on 3 columns ( col1 char(20), col2 varchar(254) , > col3 integer) > > Please let me know. Thanks. Varchar 254? There's no polite way to say this, so I'll just come straight out with it: Are you fucking mad? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

Nasty index. varchar(254) It will depend a lot on your machine. Scan rate of disk, cpu speed, amount of memory, amount and number of temp spaces as well as ONCONFIG settings. Estimate it, grab 128K rows into a table and build the index on them, it won't be a perfect comparison, but it will give you an idea. On one of my boxes I would expect ~20 min. j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of Jo Sent: Thursday, December 03, 2009 10:25 AM To: informix-list@iiug.org Subject: Index creation progress Hi all, How could I determine how much time it would take to create this index? Table has row = 128 million records This one Index is on 3 columns ( col1 char(20), col2 varchar(254) , col3 integer) Please let me know. Thanks. Regards, Jose _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

IB you can only build an index with a key wider than 254bytes in a dbspace with a pagesize greater than 2K. That means you will need IDS version 10.00 or later. PLEASE POST YOUR VERSION AND PLATFORM INFORMATION WHEN YOU POST! Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Thu, Dec 3, 2009 at 10:24 AM, Jo <josephska@gmail.com> wrote: > Hi all, > > How could I determine how much time it would take to create this > index? > > Table has row = 128 million records > This one Index is on 3 columns ( col1 char(20), col2 varchar(254) , > col3 integer) > > Please let me know. Thanks. > > Regards, > Jose > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Hi Jo. As others have said, you want to avoid an index that wide. See below a recent exchange I had with a client on this subject. Regards, Doug Lawry --- QUESTION --- Bill was working on my test server this weekend updating the application with the latest changes and he run into a problem with the database. He added a table in which he wanted to add a primary composite key of three fields of which two are varchar(20) and the third is lvarchar(350). The server threw and exception because the dbspace size on my server is 2 K which is to small for this index. I have never run into this problem before so I am asking for any advise that you might have. I know from researching this problem that I can create a new dbspace with a page size all the way up to 16 K. I know it is easy to create an index in another dbspace on the server, but is it possible to create the table in the 2 K dbspace and the primary index in a 4 K dbspace? If I were to create a 4 K dbspace should I let the server engine create the buffer pool or should it be configured manually? --- RESPONSE --- Indexes on such a large column set are not recommended for obvious reasons. Could you not add a SERIAL column if you just need a primary key? If you do need to make the combination of these three columns unique - and to use it for quick search purposes - would it be enough to test just the front of the LVARCHAR? In which case, you could add a functional index, such as in the following example (tested on 11.50.TC4, SUBSTR cannot be used directly): CREATE FUNCTION test_proc (value VARCHAR(20)) RETURNING VARCHAR(20) WITH (NOT VARIANT); RETURN value; END FUNCTION; CREATE UNIQUE INDEX test_index ON table-name (column1, column2, test_proc(column3)); If you absolutely have to index the entire three columns and you find this needs a larger page size, you will have to create another dbspace with this page size as you say (see "onspaces"). In the $ONCONFIG file, you should find entries such as: BUFFERPOOL default,... BUFFERPOOL size=2K,... If you are not happy with the "default" settings, create a new entry for your page size. e.g. with "size=4K". When you create the index, use: CREATE UNIQUE INDEX ... IN dbspace-name; "Jo" <josephska@gmail.com> wrote in message news:9332c178-5e50-4b32-9702-80add55f2eb7@p23g2000vbl.googlegroups.com... > Hi all, > > How could I determine how much time it would take to create this > index? > > Table has row = 128 million records > This one Index is on 3 columns ( col1 char(20), col2 varchar(254) , > col3 integer) > > Please let me know. Thanks. > > Regards, > Jose

On Dec 4, 1:47 pm, "Doug Lawry" <la...@nildram.co.uk> wrote: > Hi Jo. > > As others have said, you want to avoid an index that wide. > See below a recent exchange I had with a client on this subject. > > Regards, > Doug Lawry > > --- QUESTION --- > >     Bill was working on my test server this weekend updating the > application with the latest changes and he run into a problem with the > database.  He added a table in which he wanted to add a primary > composite key of three fields of which two are varchar(20) and the third > is lvarchar(350).  The server threw and exception because the dbspace > size on my server is 2 K which is to small for this index.  I have never > run into this problem before so I am asking for any advise that you > might have. > >     I know from researching this problem that I can create a new dbspace > with a page size all the way up to 16 K.  I know it is easy to create an > index in another dbspace on the server, but is it possible to create the > table in the 2 K dbspace and the primary index in a 4 K dbspace?  If I > were to create a 4 K dbspace should I let the server engine create the > buffer pool or should it be configured manually? > > --- RESPONSE --- > > Indexes on such a large column set are not recommended for obvious reasons. > Could you not add a SERIAL column if you just need a primary key? > > If you do need to make the combination of these three columns unique - and > to use it for quick search purposes - would it be enough to test just the > front of the LVARCHAR? In which case, you could add a functional index, such > as in the following example (tested on 11.50.TC4, SUBSTR cannot be used > directly): > > CREATE FUNCTION test_proc (value VARCHAR(20)) >     RETURNING VARCHAR(20) WITH (NOT VARIANT);>     RETURN value; > END FUNCTION; > > CREATE UNIQUE INDEX test_index ON table-name >     (column1, column2, test_proc(column3));> > If you absolutely have to index the entire three columns and you find this > needs a larger page size, you will have to create another dbspace with this > page size as you say (see "onspaces"). In the $ONCONFIG file, you should > find entries such as: > > BUFFERPOOL default,... > BUFFERPOOL size=2K,...> > If you are not happy with the "default" settings, create a new entry for > your page size. e.g. with "size=4K". > > When you create the index, use: > > CREATE UNIQUE INDEX ... IN dbspace-name; > > "Jo" <joseph...@gmail.com> wrote in message > > news:9332c178-5e50-4b32-9702-80add55f2eb7@p23g2000vbl.googlegroups.com... > > > > > Hi all, > > > How could I determine how much time it would take to create this > > index? > > > Table has row = 128 million records > > This one Index is on 3 columns ( col1 char(20), col2 varchar(254) , > > col3 integer) > > > Please let me know. Thanks. > > > Regards, > > Jose- Hide quoted text - > > - Show quoted text - === This is one of customer env that we support. No choice.. Could I get the total Index space calculation. i.e How much GB it will occupy in the DBSPACE. For the above Index & no. or records. As per my calculation I am getting huge value. -Jo

Index space caluculation used for the above index: (20+254+4+9) * 128867926 * 1.25 = 287 * 128867926 * 1.25 = 43 GB However the actual index space after rebuild of index was 8.7 GB. Why there is this difference? Only reason I can think of is because of Varchar. Is my analysis correct?