WORD index and CONTAINS predicate support for character large object (CLOB) columns [CR 415547]

Sybase IQ 12.7 ESD #3 provides support for WORD (WD) indexes on LONG VARCHAR character large object (CLOB) columns. A WORD index is also called a Containment index.

Sybase IQ 12.7 ESD #3 also includes support of the CONTAINS predicate on LONG VARCHAR (CLOB) columns with a WORD (WD) index. Formerly, the CONTAINS predicate was supported only on CHAR and VARCHAR columns with a WD index.

Users must be specifically licensed to use the Large Objects Management functionality. For details on the Large Objects Management option, see Large Objects Management in Sybase IQ.

The following enhancements support the WORD (WD) index on LONG VARCHAR (CLOB) columns:

• Sybase Central Java allows you to create a WD index on columns of CHAR, VARCHAR, and LONG VARCHAR data types.

• The widest column supported by the WD index increased from 32767 bytes up to the maximum width for a LOB column. (The maximum length is equal to 4GB multiplied by the database page size.)

  The maximum word length supported by Sybase IQ is still 255 bytes.

• All sp_iqcheckdb options for WD indexes over CHAR and VARCHAR columns are also supported for LONG VARCHAR (CLOB) columns, specifically, allocation, check, verify, and repair modes.

• The sp_iqrebuildindex stored procedure supports rebuilding a WD index over a LONG VARCHAR (CLOB) column.

Note that Chinese text or documents in a binary format still require ETL pre-processing to locate and transform the words into a form that can be parsed by the WD index.

Using the CONTAINS predicate, you can now search for string constants of maximum length 255 characters on a LONG VARCHAR (CLOB) column, in addition to CHAR and VARCHAR columns.

Note that the CONTAINS predicate is not supported on LONG BINARY (BLOB) columns. If you attempt to search for a string in a LONG BINARY column using a CONTAINS predicate, an error is returned.

For more information on CONTAINS string searches, see “CONTAINS conditions” in Chapter 3, “SQL Language Elements” of the Sybase IQ Reference Manual.