Enabling Alternative Indexes

A CREATE INDEX statement specifies one or more column names, or keys, from the table that the index references. A user-defined secondary access method can support alternative concurrent indexes that reference identical keys.

Typically, a user wants alternative indexes to provide a variety of search algorithms. The access method can test for predefined parameter values to determine how the user wants the index searched.

Consider the following example that enables two methods of search through a document for a character string:

• Look for whole words only.
• Use wildcard characters, such as *, to match any character.

The user specifies parameter keywords and values to distinguish between whole word and wildcard indexes on the same keywords column. This example uses a registered secondary access method named search_text.

CREATE TABLE text(keywords lvarchar, .....) 
CREATE INDEX word ON text(keywords) 
   USING search_text(searchmode='wholeword',wildcard='no');
CREATE INDEX pattern ON text(keywords) 
   USING search_text(searchmode='string', wildcard='yes');

The access method allows both word and pattern indexes because they specify different parameter values. However, the access method issues an error for the following duplicate index:

CREATE INDEX fuzzy ON text(keywords) 
   USING search_text(searchmode='string', wildcard='yes');

To determine if a user attempts to create a duplicate index, the search_text access method calls the following functions:

• The mi_tab_amparam() function returns the string searchmode=string, wildcard=yes from the CREATE INDEX statement.
• The mi_tab_nparam_exist() function indicates the number of indexes that already exist on column keywords (in this case, two).
• The mi_tab_param_exist() function returns the searchmode= and wildcard= values for each index on column keywords.

On the second call, mi_tab_param_exist() returns a string that matches the return string value from mi_tab_amparam(), so the access method alerts the user that it cannot create index fuzzy.

Figure 9 shows how the am_create purpose function tests for duplicate indexes.

MI_AM_TABLE_DESC *td;
mi_string *index_param, *other_param;
mi_integer i;

/* 1- Get user-defined parameters for the proposed index */
index_param = mi_tab_amparam(td);

/* 2- Get user-defined parameters for any other indexes
** that already exist on the same column(s).*/
for (i = 0; i < mi_tab_nparam_exist(td); i++)
   {
   other_param = mi_tab_param_exist(td,i);

   /* No configuration keywords distinguish the newindex
   ** from the existing index.
   ** Reject the request to create a new, duplicate index. */
   if ((index_param == NULL || index_param[0] == '\0')
      && (other_param == NULL || other_param[0] == '\0'))
      mi_db_error_raise(NULL, MI_EXCEPTION,
      "Duplicate index.");

   /* The user specifies identical keywords and values for a 
   ** new index as those that apply to an existing index
   ** Reject the request to create a new, duplicate index.*/

   if (strcmp(index_param, other_param) == 0)
      mi_db_error_raise(NULL, MI_EXCEPTION, 
      "Duplicate index.");
   }

/* The new index has unique keyword values. 
** Extract them and create the new index. (Not shown) */

For more information about mi_tab_nparam_exist(), mi_tab_param_exist(), and mi_tab_amparam(), refer to Descriptor Function Reference.