IC94738: IFX_ROW_ID UNDOCUMENTED FEATURE IS DOING SEQUENTIAL SCAN INSTEADOF READING DIRECTLY IN THE APPROPRIATE PAGE

APAR status

• Closed as documentation error.

Error description

• IFX_ROW_ID feature is undocumented but the equivalent "with
  rowid" is said deprecated in the documentation:
  
  Using the WITH ROWIDS Option
  
  Nonfragmented tables contain a hidden column called rowid, but
  by default, fragmented tables have no rowid column. You can use
  the WITH ROWIDS keywords to add the rowid column to a fragmented
  table. Each row is automatically assigned a unique rowid value
  that remains stable for the life of the row and that the
  database server can use to find the physical location of the
  row. Each row requires an additional four bytes to store the
  rowid.
  Important: This is a deprecated feature. Use primary keys as an
  access method rather than the rowid column.
  
  IFX_ROW_ID can be used to address the need of rowids for
  fragmented tables.
  
  When used to search for a particular row like in:
  
    SELECT col FROM tab WHERE ifx_row_id like 'val%'
  
  the engine is reading through all rows of the table and does not
  read only in the page where the given row can be found.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Informix 11.70, 12.10                                        *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * See problem conclusion.                                      *
  ****************************************************************
  

Problem conclusion

• The database server might not use an index scan if ROWID shadow
  columns are defined on fragmented tables.
  
  If you want the query optimizer to use an index scan on
  fragmented tables, you should define a primary-key on the table,
  rather than precede the FRAGMENT BY clause with the WITH ROWIDS
  keywords in ALTER TABLE, ALTER FRAGMENT, or CREATE TABLE
  statements of SQL.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels