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.
The following figure shows how the am_create purpose
function tests for duplicate indexes.Figure 1: Avoiding 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) */