Index syntax in DB2 database

The create index statement is used to CREATE an INDEX for the DB2 Everyplace table. CREATE--INDEX -- index-name -- ON -- table-name. Description: INDEX index-name: ON table-name. Table name: table-name. Name the table for which you want to create an index: column-name indicates the column to be part of the index key. Each column name must be an unlimited name that identifies a table column. Use eight or fewer columns; do not duplicate column names (SQLSTATE 42711 ). Each specified column must be within 1024 bytes. ASC arranges index entries in ascending order by column. This is the default value. DESC sorts index entries in descending order by column. The LCASE/UCASELCASE or LOWER function returns a string in which all SBCS characters have been converted to lowercase characters. That is, characters A to Z will be converted to characters a to z, and characters with distinguished characters will be converted to their lowercase equivalent (if they exist ). The independent variable must be an expression whose value is CHAR or VARCHAR data type. The data type and length attributes of the result of this function are the same as those of the independent variable. If the independent variable can be empty, the result can be empty. If the independent variable is empty, the result is null. Make sure that the characters in the column JOB Value in the EMPLOYEE table are returned in lowercase. For example, select lcase (JOB) from employee where empno = '000000'; a rule can create up to 15 indexes for tables without a primary key. A maximum of 14 indexes can be created for tables with primary keys. If you try to CREATE an INDEX that matches the existing INDEX, the create index statement fails. In the following cases, two index descriptions are considered repeated: the column set in the index and its sequence are the same as the column set and sequence of the existing index. The sorting attributes are the same. Columns with BLOB data types cannot be used in the create index statement. Note: The create index statement can contain a maximum of eight columns. DB2 Everyplace supports two-way indexing scanning. Although the definitions of the following two indexes are different, their functions are the same. Create index IDX1 on employee (job asc) create index IDX1 on employee (job desc) Generally, an INDEX should be created without specifying the sorting direction. Generally, the less the index, the lower the index maintenance cost. DB2 Everyplace supports index prefix scanning. Consider the following example. The following indexes are created. Create index J1 on t (A, B, C, D, E, F, G, K) does not need to CREATE another INDEX for T (A, B, C, D. If the table does not contain data, create index creates an INDEX description. when inserting data into the table, CREATE an INDEX entry. To CREATE an INDEX for a dirty location, use the following example: create index on ($ dirty) for more information about dirty locations, see ***. In the example, create an index named JOB_BY_DPT for the EMPLOYEE table. Sort index entries in ascending order by JOB in each department (WORKDEPT. Create index JOB_BY_DPT on employee (WORKDEPT, JOB) Note: when creating a table, you can specify the index tablespace, for example, create table tabname (...) in tabspace INDEX in tabindexspace