varchar index on varchar(255) column

We have a table with approximately 50 million rows that contains a VARCHAR(255), not realizing that there is a max size of 254 when creating an index, we attempted to create an index on the column. It succeeded but seemed detrimental to system performance. So we dropped the index and things returned to normal. But we are still needing an index on this column, short of reducing the column and fixing it everywhere in our code, is there a way around this? We were thinking about a fragmented index on the column? Thanks, Pete Rushie Currency Technics & Metrics ------------------------------ Pete Rushie ------------------------------

Pete: You could alter the table for that column from VARCHAR(255) to LVARCHAR(255) which would be indexable. If you do that and don't want to have to modify your code to cast the column back to a VARCHAR everywhere it is referenced, you could rename the altered table and then create a VIEW that does the cast, so: ALTER TABLE orig_tbl MODIFY (vc_col LVARCHAR(255));RENAME TABLE orig_tbl TO new_tbl; CREATE VIEW orig_tbl AS SELECT col1, col2, vc_col::VARCHAR(255), col3, ... FROM new_tbl;If you join to the original table often, make sure that IFX_FOLDVIEW is enabled so that the engine can substitute the new table in place of the view within joins. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

I tried the view method but queries against the view do not use the index. If we just alter the table and do not change our code (so it still thinks it is a VARCHAR(255)) will it automatically cast it? QUERY: (OPTIMIZATION TIMESTAMP: 11-01-2023 23:11:20) ------ SELECT cust_ix FROM ctm_customer_item_actual WHERE customer_field_name = 'cfoutsourcereferencenumber' AND customer_field_value = 'HALI57984' Estimated Cost: 1 Estimated # of Rows Returned: 1 1) mm.ctm_customer_item_actual: INDEX PATH (1) Index Name: mm.ix_customer_item_field_value Index Keys: customer_field_value customer_field_name cust_ix (Serial, fragments: ALL) Lower Index Filter: (informix.equal(mm.ctm_customer_item_actual.customer_field_value ,'HALI57984' )AND mm.ctm_customer_item_actual.customer_field_name = 'c foutsourcereferencenumber' ) UDRs in query: -------------- UDR id : -114 UDR name: equal UDR id : -722 UDR name: compare Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 ctm_customer_item_actual type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 1 1 1 00:00.00 1 QUERY: (OPTIMIZATION TIMESTAMP: 11-01-2023 23:11:20) ------ SELECT cust_ix FROM ctm_customer_item WHERE customer_field_name = 'cfoutsourcereferencenumber' AND customer_field_value = 'HALI57984' Estimated Cost: 51538 Estimated # of Rows Returned: 10 1) mm.ctm_customer_item_actual: SEQUENTIAL SCAN Filters: (mm.ctm_customer_item_actual.customer_field_name = 'cfoutsourcereferencenumber' AND mm.ctm_customer_item_actual.customer_field_value ::varchar(255 )= 'HALI57984' ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 ctm_customer_item_actual type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 1 10 1191485 00:00.43 51539 ------------------------------ Pete Rushie ------------------------------

Yes, you can fetch an LVARCHAR(255) value into a VARCHAR(255) host variable and the client library will make the case for you. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Not quite getting "not realizing that there is a max size of 254 when creating an index", and then index creation succeeded. And why in how far then was this detrimental to system performance? ------------------------------ Andreas Legner ------------------------------

From the documentation ( VARCHAR(m,r) data type - IBM Documentation ) there is this caveat: If you are placing an index on a VARCHAR column, the maximum size is 254 bytes. The effect on system performance seems to depend on the number of rows, in our test environment with 1 million rows there was no noticeable performance hit. In our production environment with 50 million rows the system was brought to a crawl until we removed the index. ------------------------------ Pete Rushie ------------------------------

Hmmm, create table test_vc (i int, v varchar(255)) in rootdbs;Table created. create index test_vc_ix on test_vc (i, v) in rootdbs;Index created. As you can see I did this in rootdbs, so with minimum possible page size, and I even included the i column. From finderr 517: -517 The total size of the index is too large or too many parts in index. Informix Dynamic Server has limits on the number of columns that can be included in an index and on the total number of bytes in a key (the sum of the widths of the columns). This CREATE INDEX statement would exceed that limit for this database server. Informix Dynamic Server allows 16 key parts (columnar or functional) and a width of 390 bytes on a 2K page platform or in a 2K page dbspace. On 4K page platforms or using a dbspace with a non-default page size allows greater than 390 bytes for the width of indexed columns. So I don't know when that phrase in the docs last was true, apparently it no longer is - or does anyone have a different view on this? Now on the performance aspect: apparently what you'd like and possibly expect is a key-only scan (since all columns in your query are part of the index). With varchars (and also lvarchar?) in an index, this is not possible, so, even if the query goes via the desired index, it would do a cross-check with the row for every match it finds in the index: many index matches, many row retrievals == slow. On the other hand, the query you're citing only produces one row, so should have done only one such cross check, and, quite obviously it is not slow. -> what type of query would suffer bad performance with such index? ------------------------------ Andreas Legner ------------------------------

My impression is the 254 limit only pertains to VARCHAR data types, I assume the limit is due to some technical aspect of the data type. Currently without the index it is doing a sequential scan, I would think even with the cross-check it would be quicker than the scan. It looks like there is some built-in UDRs for searching through LVARCHAR data types. Regards, Pete Rushie ------------------------------ Pete Rushie ------------------------------

Hello Andreas, I think current limit is 764 bytes per index: (from 12.10 docs abou Informix limits) Key parts per index 16 Columns per functional index 102 (for C UDRs) 341 (for SPL or Java™ UDRs) Maximum bytes per index key: 2K page size = 387 4K page size = 796 8K page size = 1615 12K page size = 2435 16K page size = 3254 But max. length of varchar column to be indexed might ca be only 254 chars? Milan Rafaj Senior Lead, Infrastructure/Cloud Architecture Kyndryl Consult +420 737 264 248 www.kyndryl.cz Planned absence/Plánovaná nepřítomnost: Kyndryl Česká republika, spol. s r. o. Sídlo: Praha 4, Chodov, V Parku 2308/8, PSČ: 148 00, IČ: 14890992 Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277) Registered address: Prague 4, Chodov, V Parku, 2308/8, Zip code: 148 00 Company ID: 14890992 Entered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277) -- Unless stated otherwise above: Kyndryl Česká republika, spol. s r. o. Sídlo: V Parku 2308/8, Chodov, 148 00 Praha 4, IČ: 096 28 886 Zapsaná v obchodním rejstříku, vedeném Městským soudem v Praze (oddíl C, vložka 339277) Registered address: V Parku 2308/8, Chodov, 148 00 Prague 4 Company ID: 096 28 886 Registered in the Commercial Register maintained by the Municipal Court in Prague (Part C, Entry 339277)

Hi Milan, tested this briefly, with some random 255 characters strings, and the same full 255 characters can be seen in data and index pages. Again, all this in rootdbs on Linux, so least possible page size. Still couldn't think of a reason behind a 254 characters/bytes restriction, nor seeing the restriction being in effect. BR, Andreas ------------------------------ Andreas Legner ------------------------------