This is the sixth day of my participation in the September Challenge.

SQL > CREATE INDEX;

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 two records in the index (and the table containing the index) cannot have the same sort value. By default, most indexes use uppercase string sort (making the search case insensitive). In this case, the values “Smith” and “Smith” are considered equal, not unique. CREATE INDEX cannot specify a non-default INDEX string collation. By defining indexes in the class definition, you can specify different string collations for individual indexes.

You can change the default collation of namespaces so that fields/attributes are case-sensitive by default. Changing this option requires recompiling all classes in the namespace and rebuilding all indexes. Go to the Management Portal, select the Classes option, select the namespace for the stored queries, and recompile the corresponding Classes using the Compile option. And then rebuild all the indices. They will be case sensitive.

Note: Do not rebuild the index when the table’s data is accessed by another user. Doing so may result in inaccurate query results.

BITMAP keyword

Using the BITMAP keyword, you can specify that the index will be a BITMAP index. A bitmap index consists of a string of one or more bits, where the bit position represents the row ID, and each bit value represents the presence (1) or absence (0) of a particular value for that row’s field (or the value of the combined field name field). SQL maintains these position 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 bitmap indexes and using regular indexes. Bitmap indexes are very efficient for many types of query operations. They have the following characteristics:

• Bitmap indexes can only be defined in tables (classes) that use system-allocated onesRowIDAnd positive integer values, or whenIDKEYBased on the type:%Integer 和 MINVALThe primary key is used for a single attribute ofIDKEYTo define a customID 值 > 0, or type%Numeric, includingSCALE = 0 且 MINVAL > 0.

Can use $SYSTEM. SQL. Util. SetOption () method to SET the status = $SYSTEM. SQL. Util. SetOption (” BitmapFriendlyCheck “, 1,. Oldval) SET up a system-wide configuration parameters, Check this limitation at compile time to determine whether bitmap indexes are allowed to be defined in % storage.sql. This check only applies to classes that use % storage.sql. The default value is 0. You can use the $SYSTEM. SQL. Util. GetOption (” BitmapFriendlyCheck “) to determine if this option is the current configuration.

Bitmap indexes can only be defined for tables that use the default (% storage.persistent) structure. Tables with compound keys, such as child tables, cannot use bitmap indexes. If you create a table using DDL instead of class definitions, it meets this requirement and can use bitmap indexes.

• ** Bitmap indexes should only be used when the number of possible different field values is limited and relatively small.

For example, a bitmap index is a good choice for gender, nationality, or time zone fields. Bitmaps should not be used on fields with UNIQUE constraints. Bitmaps should not be used if a single field can have more than 10,000 different values, or if multiple index fields can have more than 10,000 different values. 台湾国

• ** Bitmap index inWHEREClause and logicANDandORThe operation is very effective when used in combination.

If two or more fields are typically queried in combination, it may be advantageous to define bitmap indexes for these fields. 台湾国

BITMAPEXTENT keyword

A bitmap segment index is a bitmap index of the table itself. SQL uses this index to improve COUNT(*) performance, which returns the number of records (rows) in the table. A table can have at most one bitmap extended index. Creating Multiple bitmap Extent indexes will result in a SQLcode-400 ERROR #5445: Multiple Extent Indices defined: DDLBEIndex with % MSG

Bitmap range indexes are automatically defined for all tables defined using CREATE TABLE. This automatically generated index is assigned the index name DDLBEIndex and the SQL MapName %%DDLBEIndex. Tables defined as classes can have a graph range index with an index name and SQL MapName of $ClassName.

You can use CREATE BITMAPEXTENT INDEX to add a BITMAPEXTENT INDEX to a table, or rename an automatically generated BITMAPEXTENT INDEX. The specified index-name should be the class name corresponding to the table-name of the table. This will become the SQL MapName for the index. You cannot specify field names or the WITH DATA clause.

The following example creates a bitmap index using the index name DDLBEIndex and SQL MapName Patient. If sample. Patient already has a %%DDLBEIndex bitmap index, this example renames the index to SQL MapName Patient:

  &sql(CREATE BITMAPEXTENT INDEX Patient ON TABLE Sample.Patient)
  WRITE !,"SQL code: ",SQLCODE
Copy the code

BITSLICE keyword

Using the BITSLICE keyword, you can specify that this index will be a BITSLICE index. Bitslice indexes are specifically used for numerical data used in calculations. Bitslice indexes represent each numeric data value as a binary string of bits. Rather than indexing numeric data values using Boolean flags (as in bitmap indexes), bitslice indexes create a bit string for each numeric value and a separate bit string for each record. This is a highly specialized index type and should only be used for fast aggregation calculations. For example, the following would be candidates for bit-slice indexes:

SELECT SUM(Salary) FROM Sample.Employee
Copy the code

Bitslice indexes can be created for string data fields, but bitslice indexes represent these data values as canonical numbers. In other words, any non-numeric string (such as “ABC”) will be indexed to 0. This type of bit-slice index can be used to quickly count records that have string field values, rather than counting those that are empty.

Should not be inWHEREClause uses bitslice index becauseSQLThe query optimizer does not use bitslice indexes.

Populating and maintaining bitslice indexes using INSERT, UPDATE, or DELETE operations is much slower than using bitmap or regular indexes. Using multiple bit-slice indexes and/or using bit-slice indexes on frequently updated fields can have significant performance costs.

Bitslice indexes can only be used for system-allocated rowsIDIs a record of positive integer values. Bitslice indexes can only be used for a single field name. Cannot specifyWITH DATAClause.

Rebuilding the index

Creating an INDEX using the CREATE INDEX statement automatically builds the INDEX. However, in some cases, you may want to explicitly regenerate the index.

Note: If other users are accessing the table’s data, additional steps must be taken when rebuilding the index. If you do not do this, the query results may be inaccurate. For more information, see Building indexes on live Systems.

Indexes can be built/rebuilt as follows:

• Using build indexesSQLCommand.
• Rebuild all indexes of the specified class (table) using the management portal.
• use%BuildIndices()Methods.

To rebuild all indexes of an inactive table, do the following:

  SET status = ##class(myschema.mytable%).BuildIndices(a)Copy the code

By default, this command clears the index before rebuilding it. You can override this clearing default and explicitly clear the specified index using the %PurgeIndices() method. If %BuildIndices() is called for a range of ID values, IRIS does not clear the index by default.

You can also clear/rebuild the specified index:

  SET status = ##class(myschema.mytable%).BuildIndices($ListBuild("NameIDX","SpouseIDX"))
Copy the code

As mentioned above, if the index is corrupted, you may need to clean/rebuild the index or change the case sensitivity of the index. To recompress the Bitmap index, use the % sys.maint. Bitmap method instead of cleaning/rebuilding.

The sample

The embedded SQL example below creates a table named Fred and then creates an index named “FredIndex” on the Lastword and Firstword fields of the Fred table (by removing punctuation from the name “Fred_Index” provided).

ClassMethod CreateIndex(a)
{
	&sql(
		CREATE TABLE Fred 
		(
			TESTNUM     INT NOT NULL,
			FIRSTWORD   CHAR (30) NOT NULL,
			LASTWORD    CHAR (30) NOT NULL,
			CONSTRAINT FredPK PRIMARY KEY (TESTNUM)))if SQLCODE = 0{ w ! ."Table creation" 
	} elseif SQLCODE = -201{ w ! .Table already exists 
	} else{ w ! .SQL table creation error code:,SQLCODE
		q 
	}
	&sql(
		CREATE INDEX Fred_Index
		ON TABLE Fred
		( LASTWORD,FIRSTWORD )
	)
	if SQLCODE = -324{ w ! ."Index already exists" 
		q 
	} elseif SQLCODE = 0{ w ! ."Create index" 
	} else{ w ! .SQL index creation error code:,SQLCODE 
		q 
	}
}

Copy the code

The following example creates an index named “CityIndex” on the City field of the Staff table:

CREATE INDEX CityIndex ON Staff (City)
Copy the code

The following example creates an index named “EmpIndex” on the EmpName field of the Staff table. The UNIQUE constraint is used to avoid rows that have the same value in a field:

CREATE UNIQUE INDEX EmpIndex ON TABLE Staff (EmpName)
Copy the code

The following example creates a bitmap index named “SKUIndex” on the SKU field of the All-day table. The BITMAP keyword indicates that this is a BITMAP index:

CREATE BITMAP INDEX SKUIndex ON TABLE Purchases (SKU)
Copy the code