Informix

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:

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

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

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: 1Estimated # 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 = 'cfoutsourcereferencenumber' )UDRs in query:--------------    UDR id  :   -114    UDR name:   equal    UDR id  :   -722    UDR name:   compareQuery 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   1QUERY: (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: 51538Estimated # 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:

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

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

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

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?

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

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.

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?

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

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?

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.

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?

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

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?

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.

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?

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

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?

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.

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?

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