Skip to main content

Define SQL Optimized Tables Through Persistent Classes

In InterSystems IRIS SQL, you can define persistent classes that will present themselves as SQL tables, instead of writing Data Definition Language (DDL) statements. This page describes a number of class features you can use within a class definition to ensure high performance for SQL statements accessing the table. These features can be applied to a new or existing class definition, but some require additional attention if data already exists for this table.

Global Naming Strategy

The name of the global that stores data is determined by the value of the USEEXTENTSET and DEFAULTGLOBAL class parameters that define the table. The global names also determine how indexes are named. The relationship between the two parameters is summarized below:

  • If USEEXTENTSET=0, the global name will consist of a user-specified name and an appended letter code. For example, a class named Sample.MyTest will correspond with a global named ^Sample.MyTestD for the master map and ^Sample.MyTestI for all index maps. If DEFAULTGLOBAL is specified, the specified global name is substituted for the persistent class name.

  • If USEEXTENTSET=1, hashed global names will be created for the master map and each of the separate index maps. This involves hashing both the package name and the class name and appending an incrementing integer for each master and index map. These names are less user readable, but lead to better low-level efficiencies for storing and traversing the globals. Using separate globals for each index map also offers performance and operational benefits. If DEFAULTGLOBAL is specified, that name is substituted for the hashed package and class names.

Both the USEEXTENTSET and DEFAULTGLOBAL class parameters drive how a storage definition is generated. Changing them for a class that already has a storage definition will have no impact until you reset the storage definition, as described in Resetting the Storage Definition. Note that this will render any existing data inaccessible; therefore, these parameters should only be updated in a development environment, prior to loading any import data.

InterSystems recommends setting USEEXTENTSET to 1, which is the default when creating tables with CREATE TABLE. For reasons of backwards compatibility, the default for the USEEXTENTSET parameter in class inheriting from %Persistent is still 0. Therefore, InterSystems recommends setting this parameter to 1 for new classes, before compiling for the first time.

For more information about the USEEXTENTSET and DEFAULTGLOBAL parameters, refer to Hashed Global Names and User-Defined Global Names respectively.

Decide Storage Layout

You can define persistent classes to take advantage of columnar storage on either all properties of the class or a subset of the properties of the class. The benefits and drawbacks of these approaches are further explained in Choose an SQL Table Storage Layout.

By default, persistent classes use the row storage layout for all properties in a class. However, you can use the STORAGEDEFAULT parameter to set this default to columnar. In addition, you can define a mixed storage layout that uses a row storage layout for some properties and a columnar storage layout for others.

Note:

InterSystems IRIS uses the value of the STORAGEDEFAULT parameter as the default when generating storage definition entries for the class. This value is only considered upon the initial compilation of the class or when adding new properties. Changing this parameter for a class that already has a storage definition (saved in a Storage XData block) has no effect on that storage, including on data already stored for the class extent. As a result, you should decide your storage layout before compiling your class for the first time.

Indexes

You can define an index for a table field or group of fields. You can define several different type of indexes: standard, bitmap, bitslice, and columnar. SQL optimization uses the defined indexes to access specific records for a query, update, or delete operation based on predicates that involve the fields covered by those indexes.

If you add an index to a class after loading data into it, you must separately build the index, as class compilation affects the definition of a class and never stores or processes its data. You must manually build the defined indexes to make use of an index defined through a class definition in future SQL queries.

See Defining Indexes Using a Class Definition for examples of how to define an index in a class definition.

For more information about what fields to index, refer to the What to Index.

The Extent Index

An Extent Index is a special type of index that does not index any particular fields, but only contains the ID entries for each row in the master map. When a class has a bitmap-compatible IDKEY, the extent index can be implemented as a bitmap extent index, which offers extremely efficient existent checking and counting. For example, the query SELECT COUNT(*) FROM t is an order of magnitude more efficient on a table with a bitmap extent index than it is on a table without such an index.

To define a bitmap extent index in a class, write the following:

Index BME [ Extent, Type = bitmap ];

The type keyword can be left out in the rare case that your class does not have a bitmap-compatible IDKEY.

A bitmap extent index will automatically be created when creating a table using the CREATE TABLE DDL statement. InterSystems recommends adding a bitmap extent index to any persistent class. As with standard indexes, the extent index must be built separately from being created.

FeedbackOpens in a new tab