The CREATE INDEX EXTENSION statement defines an extension object for use with indexes on tables that have structured type or distinct type columns.
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
>>-CREATE INDEX EXTENSION--index-extension-name-----------------> >--+---------------------------------------+--------------------> | .-,---------------------------. | | V | | '-(----parameter-name1--data-type1-+--)-' >--| index-maintenance |--| index-search |--------------------->< index-maintenance |--FROM SOURCE KEY--(--parameter-name2--data-type2--)-----------> >--GENERATE KEY USING--table-function-invocation----------------| index-search .-,---------------------------. V | |--WITH TARGET KEY--(----parameter-name3--data-type3-+--)-------> .-,----------------------------. V | >--SEARCH METHODS----| search-method-definition |-+-------------| search-method-definition .-,---------------------------. V | |--WHEN--method-name--(----parameter-name4--data-type4-+--)-----> >--RANGE THROUGH--range-producing-function-invocation-----------> >--+-------------------------------------------------------+----| '-FILTER USING--+-index-filtering-function-invocation-+-' '-case-expression---------------------'
parameter-name1 must be unique within the definition of the index extension. No more than 90 parameters are allowed. If this limit is exceeded, an error (SQLSTATE 54023) is returned.
Character and graphic string data types cannot specify string units of CODEUNITS32.
The definer of the index extension must have EXECUTE privilege on this function.
More precisely, let a1:t1, ..., an:tn be the function result columns and data types of the key transformation function. The function result columns of the range-producing-function-invocation must be b1:t1, ..., bm:tm, c1:t1, ..., cm:tm, where m <= n and the "b" columns are the start key columns and the "c" columns are the stop key columns.
When the range-producing-function-invocation returns a null value as the start or stop key value, the semantics are undefined.
The definer of the index extension must have EXECUTE privilege on this function.
If not specified, index filtering is not performed.
The definer of the index extension must have EXECUTE privilege on this function.
CREATE INDEX EXTENSION GRID_EXTENSION (LEVELS VARCHAR(20) FOR BIT DATA)
FROM SOURCE KEY (SHAPECOL SHAPE)
GENERATE KEY USING GRIDENTRY(SHAPECOL..MBR..XMIN,
SHAPECOL..MBR..YMIN,
SHAPECOL..MBR..XMAX,
SHAPECOL..MBR..YMAX,
LEVELS)
WITH TARGET KEY (LEVEL INT, GX INT, GY INT,
XMIN INT, YMIN INT, XMAX INT, YMAX INT)
SEARCH METHODS
WHEN SEARCHFIRSTBYSECOND (SEARCHARG SHAPE)
RANGE THROUGH GRIDRANGE(SEARCHARG..MBR..XMIN,
SEARCHARG..MBR..YMIN,
SEARCHARG..MBR..XMAX,
SEARCHARG..MBR..YMAX,
LEVELS)
FILTER USING
CASE WHEN (SEARCHARG..MBR..YMIN > YMAX) OR
SEARCHARG..MBR..YMAX < YMIN) THEN 0
ELSE CHECKDUPLICATE(LEVEL, GX, GY,
XMIN, YMIN, XMAX, YMAX,
SEARCHARG..MBR..XMIN,
SEARCHARG..MBR..YMIN,
SEARCHARG..MBR..XMAX,
SEARCHARG..MBR..YMAX,
LEVELS)
END
WHEN SEARCHSECONDBYFIRST (SEARCHARG SHAPE)
RANGE THROUGH GRIDRANGE(SEARCHARG..MBR..XMIN,
SEARCHARG..MBR..YMIN,
SEARCHARG..MBR..XMAX,
SEARCHARG..MBR..YMAX,
LEVELS)
FILTER USING
CASE WHEN (SEARCHARG..MBR..YMIN > YMAX) OR
SEARCHARG..MBR..YMAX < YMIN) THEN 0
ELSE MBROVERLAP(XMIN, YMIN, XMAX, YMAX,
SEARCHARG..MBR..XMIN,
SEARCHARG..MBR..YMIN,
SEARCHARG..MBR..XMAX,
SEARCHARG..MBR..YMAX)
END