CREATE UNIQUE CLUSTERED INDEX index-name ON TABLE owner.catalog.schema.table
(field1 ASC, field2 DESC)
Index Name
The name of an index must be unique within a given table. Index
names follow identifier conventions,
subject to the restrictions below. By default, index names are simple
identifiers; an index name can be a delimited identifier. An index
name should not exceed 128 characters. Index names are not case-sensitive.
InterSystems IRIS uses the name you supply (which it refers
to as the “SqlName”) to generate a corresponding index
property name in the class and the global. This index property name
contains only alphanumeric characters (letters and numbers) and is
a maximum of 96 characters in length. To generate an index property
name, InterSystems IRIS first strips punctuation characters from the
SqlName you supply, and then generates a unique identifier of 96 (or
less) characters to create a unique index property name.
-
An index name can be the same as a field, table, or
view name, but such name duplication is not advised.
-
An index property name (after punctuation stripping)
must be unique. If you specify a duplicate SQL index name, the system
generates an SQLCODE -324 error. If you specify an SQL index name
that differs only in punctuation characters from an existing SQL index
name, InterSystems IRIS substitutes a capital letter (beginning with
“A”) for the final character to create a unique index
property name. Therefore it is possible (though not advisable) to
create SQL index names that differ only in their punctuation characters.
-
An index property name must begin with a letter. Therefore,
either the first character of the index name or the first character
after initial punctuation characters are stripped must be a letter.
A valid letter is a character that passes the $ZNAME test. If the first character of the SQL index name
is a punctuation character (% or _) and the second character is a
number, InterSystems IRIS appends a lowercase “n” as the
first character of the stripped index property name.
-
An index name may be much longer than 31 characters,
but index names that differ in their first 31 alphanumeric characters
are much easier to work with.
The Management Portal SQL interface Catalog Details displays the SQL
index name (SQL Map Name) and the corresponding
index property name (Index Name) for each index.
What happens when you try to create an index with the same name
as an existing index is described below.
Existing Index
By default, InterSystems IRIS rejects an attempt to create an
index that has the same name as an existing index for that table and
issues an SQLCODE -324 error. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays a Allow DDL CREATE INDEX for existing index setting. The
deault is 0, which is the recommended setting for this option. If
this option is set to 1, InterSystems IRIS deletes the existing index
from the class definition and then recreates it by performing the CREATE INDEX. It deletes the named index from the table
specified in CREATE INDEX. This option permits
the delete/recreate of a UNIQUE constraint index (which cannot be
done using a DROP INDEX command). To delete/recreate
a primary key index, refer to the ALTER
TABLE command.
From the Management Portal, System Administration, Configuration, SQL and Object Settings, SQL you can set this option (and other similar create, alter, and drop
options) system-wide by selecting the Ignore redundant DDL
statements check box.
However, even if this option is set to allow the recreating
of an existing index, you cannot recreate a Primary Key IDKEY index if the table contains data. Attempting
to do so generates an SQLCODE -324 error.
Table Name
You must specify the name of an existing table.
-
If table-name is a nonexistent
table, CREATE INDEX fails with an SQLCODE -30 error,
and sets %msg to Table 'SQLUSER.MYTABLE' does not exist.
-
If table-name is a view, CREATE INDEX fails with an SQLCODE -30 error, and sets
%msg to Attempt to CREATE INDEX 'My_Index' on view SQLUSER.MYVIEW
failed. Indices only supported for tables, not
views..
Creating an index modifies the table’s definition; if
you do not have permission to change the table definition, CREATE INDEX fails with an SQLCODE -300 error, and sets
%msg to DDL not enabled for class 'schema.tablename'.
Field Names
You must specify at least one field name to index on. Specify
a field name or a comma-separated list of field names enclosed in
parentheses. Duplicate field names are permitted and preserved in
the index definition. Specifying more than one field may improve performance
of GROUP BY operations, for example, group by state
and then by city within each state. Generally, you should avoid indexing
on a field or fields that have large amounts of duplicate data. For
example, in a database of people, indexing on a Name field would be
appropriate because most names are unique. Indexing on a State field
would (in most cases) not be appropriate because of the large number
of duplicate data values. The fields you specify must either be defined
in the table or in the superclass of the table’s persistent
class. (all classes must, of course, have been compiled.) Specifying
a nonexistent field generates an SQLCODE -31 error.
In addition to ordinary data fields, you can use CREATE
INDEX to create an index:
You cannot create an index on a stream value field.
You cannot create an index with multiple IDKEY fields if one of the IDKEY fields (properties)
is SQL Computed. This limitation does not apply to a single field
IDKEY index. Because multiple IDKEY fields in an index are delimited
using the “||” (double vertical bar) characters, you cannot
include this character string in IDKEY field data.
Field in an Embedded Object (%SerialObject)
To index a field in an embedded object, you create an index
in the table (%Persistent class) referencing that embedded object.
In CREATE INDEX the field-name specifies the name of the referencing field in the table (%Persistent
object) joined by an underbar to the field name in the embedded object
(%SerialObject), as shown in the following example:
CREATE INDEX StateIdx ON TABLE Sample.Person (Home_State)
Here Home is a field in Sample.Person that
references the embedded object Sample.Address, which contains the State field.
Only those embedded object records associated with the persistent
class referencing property are indexed. You cannot index a %SerialObject
property directly.
For further details on defining embedded objects (also known
as serial objects) refer to Embedded Object (%SerialObject); for further details on indexing a property (field) defined in an
embedded object, refer to Indexing an Embedded Object (%SerialObject) Property.
Index Class Name
This optional syntax allow users to specify a class and parameters
for a functional index using SQL.
An SQL example is:
CREATE INDEX HistIdx ON TABLE Sample.Person (MedicalHistory) AS %iFind.Index.Basic (LANGUAGE='en', LOWER=1)
For further details, refer to Indexing Sources for SQL Search in Using InterSystems
SQL Search.
WITH DATA Clause
Specifying this clause may allow a query to be resolved by only
reading the index, which greatly reduces the amount of disk I/O, improving
performance.
You should specify the same field in the field-name and the WITH DATA datafield-name if field-name uses string collation; this allows retrieval of the uncollated value without having to
go to the Master Map. If the value in field-name does not use string collation there is no advantage to specifying
this field in the WITH DATA datafield-name.
You can specify fields in WITH DATA datafield-name that are not indexed. This allows more queries to be satisfied from
the index without going to the Master Map. The tradeoff is how many
indices you want to maintain; and that adding data to an index makes
it quite a bit larger, which will slow down operations that don't
need the data.
You can specify fields in WITH DATA datafield-name that are defined in the superclass for the table’s persistent
class.
The UNIQUE Keyword
Using the UNIQUE keyword, you can specify that each record in
the index has a unique value. More specifically, this ensures that
no two records within the index (and hence in the table that contains
the index) can have the same collated value.
By default, most indices use uppercase string collation (to make searches
not case-sensitive). In this case, the values “Smith” and “SMITH” are considered to be equal and not unique. CREATE INDEX cannot specify non-default index string collation.
You can specify a different string collation for individual indices
by defining the index in the
class definition.
You can change the namespace
default collation to make fields/properties case-sensitive
by default. Changing this option requires recompiling all classes
and rebuilding all indices in the namespace. Go to the Management Portal, select the Classes option, select the namespace for your stored queries and use the Compile option to recompile the corresponding classes.
Then rebuild all indices. They will be case-sensitive.
Caution:
Do not rebuild indices while the table’s data is being
accessed by other users. Doing so may result in inaccurate query results.
The BITMAP Keyword
Using the BITMAP keyword, you can specify that this index will
be a bitmap index. A bitmap index consists of one or more bit strings
in which the bit position represents the row id, and each bit value
represents the presence (1) or absence (0) of a specific value for
the field in that row (or the value for the combined field-name fields). InterSystems SQL maintains these positional bits (as compressed
bit strings) when inserting, updating, or deleting data; there is
no significant difference in the performance of INSERT, UPDATE, or
DELETE operations between using a bitmap index and a regular index.
A bitmap index is highly efficient for many types of query operations.
They have the following characteristics:
-
You can only define bitmap indices in tables (classes)
that either use system-assigned RowID with positive integer values, or use a primary key IDKEY to define custom ID values when the IDKEY
is based on a single property with type %Integer and
MINVAL > 0, or type %Numeric with SCALE = 0 and MINVAL
> 0.
You can use the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method SET status=$SYSTEM.SQL.Util.SetOption("BitmapFriendlyCheck",1,.oldval) to set a system-wide configuration parameter to check at compile
time for this restriction, determining whether a defined bitmap index
is allowed in a %Storage.SQL class. This check only applies to classes
that use %Storage.SQL. The default is 0. You can use $SYSTEM.SQL.Util.GetOption("BitmapFriendlyCheck")Opens in a new tab to determine
the current configuration of this option.
You can only define a bitmap index for tables that use default
(%Storage.Persistent) structure. Tables with
compound keys, such as a child table, cannot use a bitmap index. If
you use DDL (as opposed to using class definitions) to create a table,
it meets this requirement and you can make use of bitmap indices.
-
A bitmap index should only be used when the number
of possible distinct field values is limited and relatively small.
For example, a bitmap index is a good choice for a field for gender,
or nationality, or timezone. A bitmap should not be used on a field
with the UNIQUE constraint. A bitmap should not be used if a field
can have more than 10,000 distinct values, or if multiple indexed
fields can have more than 10,000 distinct values.
-
Bitmap indices are very efficient when used in combination
with logical AND and OR operations in a WHERE clause. If two or more fields are commonly queried in combination,
it may be advantageous to define bitmap indices for those fields.
For more details, see Bitmap
Indices.
The BITMAPEXTENT Keyword
A bitmap extent index is a bitmap index for the table itself.
InterSystems SQL uses this index to improve performance of COUNT(*), which returns the number of records
(rows) in the table. A table can have, at most, one bitmap extent
index. Attempting to create more than one bitmap extent index results
in an SQLCODE -400 error with the %msg ERROR #5445: Multiple
Extent indices defined: DDLBEIndex.
All tables defined using CREATE
TABLE automatically define a bitmap extent index. This automatically
generated index is assigned the Index Name DDLBEIndex and the SQL
MapName %%DDLBEIndex. A table defined as a class may have a bitmap
extent index defined with an Index Name and SQL MapName of $ClassName.
You can use CREATE BITMAPEXTENT INDEX to
add a bitmap extent index to a table, or to rename an automatically-generated
bitmap extent index. The index-name you specify
should be the class name corresponding to the table-name of the table. This becomes the SQL MapName for the index. No field-name or WITH DATA clause can be specified.
The following example creates a bitmap extent index with Index
Name DDLBEIndex and the SQL MapName Patient. If Sample.Patient already
had a %%DDLBEIndex bitmap extent index, this example renames that
index to SQL MapName Patient:
CREATE BITMAPEXTENT INDEX Patient ON TABLE Sample.Patient
For more details, see Bitmap Extent Index.
The BITSLICE Keyword
Using the BITSLICE keyword, you can specify that this index
will be a bitslice index. A bitslice index is used exclusively for
numeric data which is used in calculations. A bitslice index represents
each numeric data value as a binary bit string. Rather than indexing
a numeric data value using a boolean flag (as in a bitmap index),
a bitslice index creates a bit string for each numeric value, a separate
bit string for each record. This is a highly specialized type of index
that should only be used for fast aggregate calculations. For example,
the following would be a candidate for a bitslice index:
SELECT SUM(Salary) FROM Sample.Employee
You can create a bitslice index for a string data field, but
the bitslice index will represent these data values as canonical numbers.
In other words, any non-numeric string, such as “abc”
will be indexed as 0. This type of bitslice index could be used to
rapidly count records that have a value for a string field and not
count those that are NULL.
A bitslice index should not be used in a WHERE clause, because
they are not used by the SQL query optimizer.
Populating and maintaining a bitslice index using INSERT, UPDATE,
or DELETE operations is significantly slower than using a bitmap index
or a regular index. Using several bitslice indices, and/or using a
bitslice index on a field that is frequently updated may have a significant
performance cost.
A bitslice index can only be used for records that have system-assigned
row Ids with positive integer values. A bitslice index can only be
used on a single field-name. You cannot specify
a WITH DATA clause.
For more details, see Bitslice
Indices.
The COLUMNAR Keyword
Using the COLUMNAR keyword, you can specify that this index
will be a columnar index. A columnar index is used for a column that
is frequently queried but whose table has an underlying row storage
structure. By default, each row of a table is stored as a $LIST in a separate global subscript. For more details,
see Columnar Indices and Choose an SQL Table Storage Layout.
Rebuilding an Index
Creating an index using the CREATE INDEX statement
automatically builds the index. However, there are cases when you
may wish to explicitly rebuild an index.
Caution:
You must take additional steps when rebuilding an index if the
table’s data is being accessed by other users. Failing to do
so may result in inaccurate query results. For more details, refer
to Building Indices on an
Active System.
You can build/re-build indices as follows:
-
Using the BUILD INDEX SQL command.
-
Using the Management
Portal to rebuild all of the indices for a specified class
(table).
-
Using the %BuildIndices() method.
To rebuild all indices for an inactive table, execute the following:
SET status = ##class(myschema.mytable).%BuildIndices()
By default, this command purges the indices prior to rebuilding
them. You can override this purge default and use the %PurgeIndices() method to explicitly purge specified
indices. If you call %BuildIndices() for
a range of ID values, InterSystems IRIS does not purge indices by
default.
You can also purge/rebuild specified indices:
SET status = ##class(myschema.mytable).%BuildIndices($ListBuild("NameIDX","SpouseIDX"))
You may want to purge/rebuild an index if the index is corrupt
or to change the case sensitivity of the index, as described above.
To recompress a bitmap index, use the %SYS.Maint.BitmapOpens in a new tab methods, rather
than purge/rebuild.
For more details, see Building
Indices.
Examples
The following example creates a table named Fred, and then creates
an index named "FredIndex" (by stripping out the punctuation from
the supplied name “Fred_Index”) on the Lastword and Firstword fields of the Fred table.
CREATE TABLE Fred (
TESTNUM INT NOT NULL,
FIRSTWORD CHAR (30) NOT NULL,
LASTWORD CHAR (30) NOT NULL,
CONSTRAINT FredPK PRIMARY KEY (TESTNUM))
CREATE INDEX Fred_Index
ON TABLE Fred (LASTWORD,FIRSTWORD)
The following example creates an index, named “CityIndex”
on the City field of the Staff table:
CREATE INDEX CityIndex ON Staff (City)
The following example creates an index, named “EmpIndex”
on the EmpName field of the Staff table. The
UNIQUE constraint is used to avoid having rows with identical values
in the fields:
CREATE UNIQUE INDEX EmpIndex ON TABLE Staff (EmpName)
The following example creates a bitmap index, named “SKUIndex”
on the SKU field of the Purchases table. The
BITMAP keyword indicates that this is a bitmap index:
CREATE BITMAP INDEX SKUIndex ON TABLE Purchases (SKU)
See Also
