Defining indexes for user-defined data types

As with built-in data types, you might improve the response time for a query when you define indexes for new data types.

The response time for a query might improve when HCL OneDB™ uses an index for:
  • Columns used to join two tables
  • Columns that are filters for a query
  • Columns in an ORDER BY or GROUP BY clause
  • Results of functions that are filters for a query

For more information about when the query performance can improve with an index on a built-in data type, see Improve performance by adding or removing indexes.

HCL OneDB and DataBlade® modules provide a variety of different types of indexes (also referred to as secondary-access methods). A secondary-access method is a set of database server functions that build, access, and manipulate an index structure. These functions encapsulate index operations, such as how to scan, insert, delete, or update nodes in an index.

To create an index on a user-defined data type, you can use any of the following secondary-access methods:
  • Generic B-tree index

    A B-tree index is good for a query that retrieves a range of data values. For more information, see B-tree secondary-access method.

  • R-tree index

    An R-tree index is good for searches on multidimensional data. For more information, see the R-Tree Index User's Guide.

  • Secondary-access methods that a DataBlade module provides for a new data type

    A DataBlade module that supports a certain type of data can also provide a new index for that new data type. For more information, see Using an index that a DataBlade module provides.

You can create a functional index on the resulting values of a user-defined function on one or more columns. For more information, see Using a functional index.

After you choose the desired index type, you might also need to extend an operator class for the secondary-access method. For more information about how to extend operator classes, see the HCL OneDB User-Defined Routines and Data Types Developer's Guide.