Speedup Index Creation on a large table

Hello All, Greetings from amit. I need some suggestion from you all. I'm creating an index on a table which is having 230 Millions of records. Table is fragmented and having NVARCHAR columns, so we can't create ONLINE index. This index creation is taking around 6 hours. index size 16 GB We have set below parameters: Version IBM Informix Dynamic Server Version 14.10.FC6X3 DS_TOTAL_MEMORY 8000000PDQPRIORITY 100 DS_MAX_QUERIES 100 LTXHWM 25Below is the output: Could someone please suggest, how can we reduce the index creation time. We have enough Temp Space , logical logs, physical dbspace. few more information : Please let me know, if you need any additional information So I can provide you. Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------

Amit: First, the thing preventing you from creating the index ONLINE is not the nvchar columns but rather that it is a functional index. That is also the main reason that the index build takes so long - 230,000+ calls to the f_lower_sp() function! My suggestion? Create a table with the primary key of the cs_cust table and first_name and last_name columns and populate it with the LOWER() or the corresponding columns from cs_cust and create an index on last_name and first_name. Then you can just join that table to cs_cust on the primary/foreign key and directly filter on this lookup table. Triggers on cs_cust can populate the lookup table with any new rows or name changes (this has the advantage that the lookup table can be used to find someone who's name has changed - say be marrieage - using their previous name by inserting on update instead of updating). So, for example: create table cs_cust_names( cs_cust_key int, first varchar(125), last varchar(125) ); create index cs_cust_names_lookup on cs_cust_names( last, first ); create index cs_cust_fk on cs_cust_names( cs_cust_key ); alter table cs_cust_names add constraint foreign key (cs_cust_key ) references cs_cust( cust_key );-- Populate -- set isolation dirty read; insert into cs_cust_names select cust_key, first, last from cs_cust;Then queries that need to do a name lookup can: select ... from cs_cust as c join cs_cust_names as n on c.cust_key = n.cs_cust_key where n.first = lower( "SomeName" ) and n.last = lower( "LastName" );Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Amit, You could have used PSORT_NPROCS, but your problem lies in the calling of the f_lower_sp 230 Millions times . Setting PDQPRIORITY is to keep of course to accelerate the index creation. Try creating an index on this table of 230 million rows (which is not huge) without using the f_lower_sp and check the time it takes; this is just to see that the problem comes from the calling of the f_lower_sp function call. I bet you that it will fast. Plus, your are using a concatenation of to lower function calls. Cordialement, Regards, Khaled Bentebal Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 09/03/2026 à 12:09, AMIT PATEL via IBM Community a écrit :

Hi, 768 cores? What kind of machine is this? How many sockets/core/threads? Regards, David. ------------------------------ David Williams Senior Database Platform Engineer Flutter London ------------------------------

David: Could be 3 sockets of Sparc T7 or M7 processor chips with 32 cores and 8 SMT threads per core which is 256 threads per socket for 768 threads which Informix will see as 768 cores. The biggest Oracle/Sun box can host up to 16 of those besties for a whopping 4096 SMT threads! Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Dear Art, Thanks for your reply. This I tried, but not giving the solution. when I create the normal index, and when we use the LOWER keyword like "lower(frst_name) = 'Amit'", so it ignore the error and goes for SEQUENTIAL SCAN, So we need functional index. Apart from this, is there any option to get the Index creation progress? Thanks Amit Patel ------------------------------ AMIT PATEL ------------------------------

Dear Khaled, We created ONLINE composite index, and time reduced to get data to 3 minutes from 10 minutes, but not in seconds. create index "informix".csn_cust_idx_19 on "informix".cs_cust (last_name,frst_name,date_of_brth) using btree in dbs285; Regards Amit Patel ------------------------------ AMIT PATEL ------------------------------

That is odd Amit, because when I tested this on my server, just to make sure I wasn't steering you up a blind alley, it used the index. Were you searching on "lower(first_name) and lower(last_name)" or "lower( first_name || "_" || last_name )"? Here's my SET EXPLAIN output: QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2026 08:09:50) ------ select * from case_test where first = lower( 'Art' ) and last = lower( 'Kagel' )Estimated Cost: 1 Estimated # of Rows Returned: 1 1) art.case_test: INDEX PATH (1) Index Name: art.case_test_names Index Keys: last first (Serial, fragments: ALL) Lower Index Filter: (art.case_test.last = 'kagel' AND art.case_test.first = 'art' ) Query statistics: ----------------- Table map : ------------------------------ Internal name Table name ------------------------------ t1 case_test type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t1 256 1 256 00:00.00 1 And here is the table schema; CREATE LARGE TABLE "art".case_test ( first VARCHAR(125,0), last VARCHAR(125,0) ) IN datadbs_2 EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW; { Please review extent sizing and adjust to allow for growth. } REVOKE ALL ON "art".case_test FROM public AS "art";CREATE INDEX "art".case_test_names ON "art".case_test ( last ASC, first ASC ) USING btree IN datadbs_2; ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com