Indexes
You create indexes using the
New Index dialog (see
Index Dialog).
Note: Creating an index on a table in versions of Actian Vector prior to 3.0 can only be done if the table is newly created and has not been filled with data. With Actian Vector 3.0, you can create indexes on both empty and non-empty tables.
You can reach this dialog in the following ways.
Preconditions:
• An installation must be connected to.
• The Indexes folder of a table node must be selected in the Instance Explorer.
Access Route:
• From the context menu: New Index
• By clicking the
Database menu tab, Table, New Index (see
Database Tab)
Index Dialog
You use the Index dialog to create indexes for tables. You access this dialog in the ways described in
Indexes. This dialog contains the following pages:
• General
• Options
• Included Columns
• Storage
These pages are described in the following sections.
For more information, see
Create an Index.
General Page
This page lets you set the following options:
Table Name
Displays the table for which you are creating an index. You may select another table from the drop-down.
Name(s), Add and Remove buttons
Lets you add indexes to the selected table.
To specify the first index, double-click the placeholder row at the top of the Name(s) list and then type the name. While this name is selected, specify the options on all of the dialog pages. See
Create an Index.
To add another index, click the Add button. Then double-click the new placeholder row and type the name.
To remove an index, select it in the list and then click Remove.
Index Structure
Specifies one of the following structures for the index.
• ISAM
• BTREE
• HASH
• RTREE
Default: ISAM
Unique
Specifies whether the index is unique. If the structure is RTREE, the Unique option cannot be specified.
Index Key Columns
Lets you add the table columns to index.
To add columns, click the Add button. Select columns in the Columns to add as keys dialog. Then click OK.
Rearrange the order of key columns using the Move Up and Move Down buttons.
To delete a column, select it in the list and then click Delete.
Options Page
This page lets you set the following options:
NOCOMPRESSION
Specifies whether the index key and data are to be compressed. If the structure is RTREE, compression cannot be specified.
KEY COMPRESSION
Specifies whether the key is to be compressed
DATA COMPRESSION
Specifies whether the data is to be compressed
PERSISTENCE
Specifies whether the modify statement recreates the index when its base table is modified
UNIQUE_SCOPE
For unique indexes only (see General page option). Specifies whether rows are checked for uniqueness one by one as they are inserted or after the update is complete. If the structure is RTREE, UNIQUE_SCOPE cannot be specified.
PAGE_SIZE
Specifies page size
CACHE PRIORITY
Allows tables to be assigned fixed priorities
Limits: An integer from 0–8
FILLFACTOR
Specifies the percentage of each primary data page that can be filled with rows
Limits: 1–100
Default: 10%
ALLOCATION
Specifies the number of pages initially allocated for the index
Limits: An integer from 4–8,388,607
Default: 4
EXTEND
Specifies the number of pages by which the index is extended when more space is required
Limits: An integer from 1–8,388,607
Default: 16
BTREE Fill
Defines the percentage full that each leaf index page or nonleaf index page is when the index is created. You can use this option when creating an index with a BTREE or compressed BTREE structure.
Limits: 1–100
Default: 10%
HASH Pages
Defines the minimum and maximum number of primary pages a hash or compressed hash index table must have. The value can be expressed as an integer literal or integer variable.
Defaults: 16 for MIN PAGES, 0 for MAX PAGES
RTREE Range
(For RTREE indexes only.) Specifies the minimum and maximum values of the index column.
Limits: The values must have the same data type as the index column, either integer4 or float8. The RTREE Range parameter must be specified if the structure is RTREE.
Defaults: 0
Included Columns Page
This page lets you add any non-key columns to the index.
To add columns, click the Add button. Select the columns from the Non-key columns to add dialog. Then click OK.
To delete columns, select the column in the list and click Delete.
Storage Page
This page lets you specify the areas in which the index is created. You may choose the default location or select a specific location from the list.
Create an Index
You create index relationships with the
Index dialog (see
Index Dialog). You access this dialog by one of the routes explained in
Indexes (see
Indexes).
To create an index
2. Select the table for which you are creating the index from the Table Name drop-down.
3. To add an index name, double-click on the placeholder row in the Name(s) list. Then type the name.
Note: Ensure that this name is selected while you specify the remaining options on the dialog.
4. Select an index structure.
5. Specify whether the index is unique.
Note: If the structure is RTREE, the Unique option cannot be specified.
6. Add key columns to the index:
a. Click the Add button and then select columns from the dialog list.
b. Click OK to close the Columns to add as keys dialog.
c. Rearrange the order of key columns using the Move Up and Move Down buttons.
Delete columns by selecting them and clicking Delete.
7. Click the Options page link.
8. Set any options desired. For more information, see
Index Dialog.
9. Click the Included Columns page link.
10. Add any non-key columns you want to include in the index:
a. Click the Add button and then select columns from the dialog list.
b. Click OK to close the Non-key columns to add dialog.
Delete columns by selecting them and clicking Delete.
11. Click the Storage page link.
12. Accept the default locations or choose specific locations to create the index.
13. To create another index:
a. Click the General page link.
b. Select a table from the Table Name drop-down.
c. Next to the Name(s) field, click the Add button
d. Double-click the new placeholder row in the Name(s) list. Then type the name. Ensure that this name is selected while you specify the remaining options on the dialog.
e. Go back to Step 4 to specify options for the selected index.
14. Click OK to save the indexes and close the dialog.
Delete an Index
You delete index relationships with the Delete Index dialog.
To delete an index
1. In the Instance Explorer, drill down through the instance to the Databases, Tables, and Indexes folders to locate the index you want to delete.
2. Right-click on the index node and select Delete on the context menu.
The Delete Index dialog opens.
3. Click OK to delete the index and close the dialog.