Creating an Index for a Sybase IQ Table

Create an index on a Sybase IQ table to speed access to data rows by pointing Sybase IQ to the location of table data on disk.

1. In Enterprise Explorer, right-click the Tables folder and select New > Index .
2. Enter the General options for the index.

   Table 1. General options

   Option	Description
   Index Name	Modify/edit the index name.
   Index Type	Select an index type. Valid values are High Group, High Non-Group, Compare, Date, DateTime, Word, and Low Fast.
   Index Advisor	Generate index advice items.
   Filter Column by Name Pattern	Enter a filter expression for the columns to be displayed in the column list. For example, * lists all columns for the specified table.
   Select the Columns	Select the columns to be used in the index. Use Select All, Deselect All, and Invert Selection to change your current selections. Use Up and Down to change the order of columns listed. When you have selected the columns to be used in the index, click Add.
   Existing Indexes and Indexes to Be Created for Table	Lists the index to be created and existing indexes for the specified table. To delete a particular index entry, highlight the entry and click Delete. To delete all index entries, click Delete All. This field appears for all tabs.
   Parallel	Create the specified indexes in parallel. This option allows the simultaneous execution of DDL statements for creating numerous indexes as though they were a single DDL statement. This option appears for all tabs.

3. Enter the Miscellaneous options for the index and click Next.

   Use Load Default to load defaults for this tab. Save settings to default with Save As Default.

   Table 2. Miscellaneous options

   Option	Description
   Unique	Make the index unique. A unique index permits no two rows to have the same index value, including NULL.
   Delimiter String	The delimiter string for the index.
   Max Size of String	The maximum size of the delimiter string. Note: Delimiter String and Max Size of String are enabled only when the index type is Word.
   Notify Count	Change the number of records. By default, after every 100,000 records are inserted and loaded into indexes, you receive a progress message. To stop these messages, specify a value of 0.
   Dbspace	Specify the type of dbspace in which to place the index. Valid values are IQ_SYSTEM_MAIN, IQ_SYSTEM_MSG, IQ_SYSTEM_TEMP, and SYSTEM.
   Comments	Enter comments for the index.

4. Indicate whether to save the generated DDL, execute it, or both.
   

   Option	Description
   Generate Script	Generate DDL for the index or indexes.
   Enter or Select the Parent Folder	Specify an existing project or create a new project in which to store the DDL.
   Single File	Save all DDL to one file.
   Multiple Files	Save the DDL for each index to its own file. SelectOptions to set the following multiple file options: Prefix – a prefix for all DDL files. Filename – use the index name or a fully qualified schema-table-index name as the file name for each DDL file. Suffix – a suffix for all DDL files. Extension – the file extension, for example, sql.
   Execute Immediately	Execute the DDL immediately and save it.

5. Click Finish to create the index, which appears in the Indexes folder under the associated table.