Chapter 3 DBMS Resource File Reference

Index

The Index category is located in the Root→Script→Objects category, and can contain the following items that define how indexes are modeled for your DBMS.

Item Description
[Common
items] 		The following common object items may be defined for indexes:

  • Add
  • AfterCreate, AfterDrop, AfterModify
  • BeforeCreate, BeforeDrop, BeforeModify
  • Create, Drop
  • Enable, EnableOwner
  • Header, Footer
  • Maxlen
  • ModifiableAttributes
  • Options, DefOptions
  • ReversedQueries
  • ReversedStatements
  • SqlAttrQuery, SqlListQuery, SqlOptsQuery


For a description of each of these common items, see Common object items.
AddColIndex Specifies a statement for adding a column in the Create Index statement. This parameter defines each column in the column list of the Create Index statement.

Example (ASE 15): 

%COLUMN%[ %ASC%]


%COLUMN% is the code of the column defined in the column list of the table. %ASC% is ASC (ascending order) or DESC (descending order) depending on the Sort radio button state for the index column.
Cluster Specifies the value to be assigned to the Cluster keyword. If this parameter is empty, the default value of the %CLUSTER% variable is CLUSTER.
CreateBefore
Key 		Controls the generation order of keys and indexes. The following settings are available:

  • Yes – Indexes are generated before keys.
  • No – Indexes are generated after keys.
DefIndexType Specifies the default type of an index.

Example (DB2): 

Type2
DefineIndex
Column 		Specifies the column of an index.
EnableAscDesc Enables the Sort property in Index property sheets, which allows sorting in ascending or descending order. The following settings are available:

  • Yes – The Sort property is enabled for indexes, with Ascending selected by default. The variable %ASC% is calculated, and the ASC or DESC keyword is generated when creating or modifying the database
  • No – Index sorting is not supported.


Example (SQL Anywhere 10):

A primary key index is created on the TASK table, with the PRONUM column sorted in ascending order and the TSKNAME column sorted in descending order: 

create index IX_TASK on TASK (PRONUM asc, TSKNAME desc);
EnableCluster Enables the creation of cluster indexes. The following settings are available:

  • Yes - The Cluster check box is enabled in index property sheets.
  • No – Cluster indexes are not supported.
EnableFunction Enables the creation of function-based indexes. The following settings are available:

  • Yes - You can define expressions for indexes.
  • No – Function-based indexes are not supported.
IndexComment Specifies a Statement for adding a comment to an index.

Example (SQL Anywhere 10): 

comment on index [%QUALIFIER%]%TABLE%.%INDEX% is
%.q:COMMENT%
IndexType Specifies a list of available index types.

Example (IQ 12.6): 

CMP
HG
HNG
LF
WD
DATE
TIME
DTTM
MandIndexType Specifies whether the index type is mandatory for indexes. The following settings are available:

  • Yes – The index type is mandatory.
  • No - The index type is not mandatory.
MaxColIndex Specifies the maximum number of columns that may be included in an index. This value is used during model checking.
SqlSysIndex
Query 		Specifies a SQL query used to list system indexes created by the database. These indexes are excluded during reverse engineering.

Example (AS IQ 12.6): 

{OWNER, TABLE, INDEX, INDEXTYPE}
select u.user_name, t.table_name, i.index_name, i.index_type
from sysindex i, systable t, sysuserperms u
where t.table_id = i.table_id
and u.user_id = t.creator
and i.index_owner != 'USER'
[and u.user_name=%.q:OWNER%]
[and t.table_name=%.q:TABLE%]
union
select u.user_name, t.table_name, i.index_name, i.index_type
from sysindex i, systable t, sysuserperms u
where t.table_id = i.table_id
and u.user_id = t.creator
and i.index_type = 'SA'
[and u.user_name=%.q:OWNER%]
[and t.table_name=%.q:TABLE%]
UniqName Specifies whether index names must be unique within the global scope of the database. The following settings are available:

  • Yes – Index names must be unique within the global scope of the database.
  • No – Index names must be unique per object

 

Copyright (C) 2008. Sybase Inc. All rights reserved.