Creating an Index for an ASE Table

Create an index on an Adaptive Server Enterprise table to speed access to data rows by pointing Adaptive Server Enterprise to the location of table data on disk.

Prerequisites

Before you can create an index, be sure the data server is running, and you created and established a connection to it.

Note: Do not define an index on a column that is defined as a foreign key, because foreign keys are already optimized for quick reference.

In the Databases folder in Enterprise Explorer, under the database connection profile, expand the Databases folder, and then expand the tree for a database and then the tree for the appropriate schema owner.
Right-click the Tables folder and select New > Index.

Enter the General options for the index.

Table 1. General options
Field	Description
Index Name	Enter a name for the index.
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.

Enter the Storage options.

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

Table 2. Storage options
Field	Description
Unique	Make the index unique. A unique index permits no two rows to have the same index value, including NULL.
Clustered	Make the index clustered. In clustered indexes, table data is physically stored in the order of the keys on the index.
Ignore Duplicate Key	If you have chosen to make the index unique, specify whether or not to ignore duplicate keys. The default is No.
Duplicate Row	If you have chosen to make the index clustered but not unique, specify how duplicate rows are to be treated. You can allow, disallow, or ignore duplicate rows. The default is to not allow duplicate rows.
Segment	Specify whether to create the index on a default or system segment.
Fill Factor	Choose the fill factor setting, which specifies how full Adaptive Server Enterprise makes each page when it creates a new index on existing data. The default is 1.
Rows Per Page	Specify the number of rows on data pages and the leaf-level pages of indexes. The default is 0.
Reserve Page Gap	Specify the ratio of filled pages to empty pages to be left during I/O allocation operations. For each specified number of pages, an empty page is left for future expansion of the index. Valid values are 0 – 255. The default is 0.

Enter the Miscellaneous options.

Table 3. Miscellaneous options
Field	Description
Bind to Cache	Specify the cache binding to be used in sorts. The default and only option is currently the default data cache.
Cache Strategy	Most Recently Used Replacement – replace items in cache with those that have been most recently used. Large Buffer Prefetch – enlarge the buffer for prefetching from cache.
Data Already Sorted	Specify whether the table data have already been sorted. If the data have already been sorted, the index can be created faster.
Number of Consumer Processes	Specify a number of processes that are likely to be using the index. The default is 0.
Statistics sStep	Specify the number of steps to generate for the histogram used in query optimizing. The default value is 20.

Enter the Partition options.

Table 4. Partition options
Field	Description
Create Local Partitioned Index	Create a local partitioned index. In the table, enter a name or names for the partitions, and choose whether to use the default or system segment for each. 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 specified the partitions to be used in the index, click Add.

Click Next to save or execute a generated script.

Enter the Script options.

Table 5. Script options
Field	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 save 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 in addition to saving the DDL.

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