IZ79809: ORACLE TEXT INDEXES RESIDING IN SAME TABLESPACE AS TABLES CAUSES PERFORMANCE PROBLEMS WITH UPGRADE/UPDATEDB/CONFIGDB

APAR status

• Closed as suggestion for future release.

Error description

• When creating Oracle Text indexes, implicit indexes should be
  stored in MAXINDX tablespace and implicit tables, in MAXDATA
  tablespace. So, it is necessary to create an Oracle preference
  arbitrary called MAXIMO_STORAGE:
  
  The implcit LOB tables should be stored in a separate LOB
  tablespace.
  
  For example, this preference definition would split the implcit
  objects in a text index across three tablespaces - MAXDATA,
  MAXINDX, and MAXLOBS:
  
  
  begin
  ctx_ddl.create_preference('MAXIMO_STORAGE', 'BASIC_STORAGE');
  ctx_ddl.set_attribute('MAXIMO_STORAGE', 'I_TABLE_CLAUSE',
  'tablespace MAXDATA LOB(token_info) store as (tablespace MAXLOBS
  enable storage in row)');
  ctx_ddl.set_attribute('MAXIMO_STORAGE', 'I_INDEX_CLAUSE',
  'tablespace MAXINDX compress 2');
  ctx_ddl.set_attribute('MAXIMO_STORAGE', 'K_TABLE_CLAUSE',
  'tablespace MAXINDX');
  ctx_ddl.set_attribute('MAXIMO_STORAGE', 'R_TABLE_CLAUSE',
  'tablespace MAXDATA LOB(data) store as (tablespace MAXLOBS
  cache)');
  ctx_ddl.set_attribute('MAXIMO_STORAGE', 'N_TABLE_CLAUSE',
  'tablespace MAXINDX');
  end;
  
   Then, to create a Oracle Text index, this preference must be
  indicated in the CREATE INDEX clause as follows:
  
  create  index pm_ndx6 on pm (description) indextype is
  ctxsys.context parameters ('lexer global_lexer language column
  LANGCODE storage MAXIMO_STORAGE');
  

Local fix

• Manually recreate all text indexes.  Rebuild after configure
  upgrade, migration manager, or updatedb operations
  

Problem summary

Problem conclusion

Temporary fix

Comments

• IZ79809 -- another enh req
  

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