Creating and Editing Indexes

From ER/Studio Data Architect
Jump to: navigation, search

Go Up to Developing the Physical Model

An index is an ordered set of pointers to rows in a base table. Each index is based on the values of data in one or more table columns. An index is an object that is separate from the data in the table. When an index is created, the database builds and maintains it automatically. Indexes are used to improve performance; in most cases, access to data is faster with an index. Although an index cannot be created for a view, an index created for the table on which a view is based can improve the performance of operations on that view. Indexes are also used to ensure uniqueness; a table with a unique index cannot have rows with identical keys. If Unique is checked or UNIQUE is selected, each row must have a unique index value. If Unique is unchecked, or NONUNIQUE is selected, rows can have duplicate index values. Oracle offers a third option, BITMAP, type of index commonly used in data warehousing and other environments with large amounts of data and ad hoc queries, but a low level of concurrent DML transactions. DB2: Allow Reverse

Indexes improve performance by providing an efficient mechanism for locating data. Indexes work like a card catalog in a library: instead of searching every shelf for a book, you can find a reference to the book in the card catalog, which directs you to the book's specific location. Logical indexes store pointers to data so that a search of all of the underlying data is not necessary.

Indexes are one of the most important mechanisms for improving query performance. However, injudiciously using indexes can negatively affect performance. You must determine the optimal number of indexes to place on a table, the index type and their placement in order to maximize query efficiency.

Consider the following rules to optimize your indexes:

  • Index Number - While indexes can improve read (query) performance, they degrade write (insert, update, and delete) performance. This is because the indexes themselves are modified whenever the data is modified. As a result, you must be judicious in the use of indexes. If you know that a table is subject to a high level of insert, update, and delete activity, you should limit the number of indexes placed on the table. Conversely, if a table is basically static, like most lookup tables, then a high number of indexes should not impair overall performance.
  • Index Type - Generally, there are two types of queries: point queries, which return a narrow data set, and range queries, which return a larger data set. For those databases that support them, clustered indexes are better suited to satisfying range queries, or a set of index columns that have a relatively low cardinality. Non-clustered indexes are well suited to satisfying point queries.
  • Index Placement - Almost every SQL database provides some physical storage mechanism for placing tables and indexes. At a minimum, you should take advantage of this database feature by separating the placement of tables from their indexes. In this manner, the database can read both the index and the table data in parallel, leading to faster performance.

In addition to indexing tables, you can also define indexes for auxiliary tables. For more information, see Creating and Editing Auxiliary Tables.

Create an Index

  1. In the Data Model Explorer, select a physical data model.
  2. In the Data Model Window, double-click the table you want to add an index to.
  3. On the Columns tab of the Table Editor, click a column and then click Add to Primary Key.
    An index is automatically created and appears under the Indexes node in the Data Model Explorer.
  4. In the Table Editor, select the Indexes tab.
  5. An index is also created when you create an identifying relationship that propagates the primary key of the parent table to the child table as a foreign key.

Edit an Index

  1. In the Data Model Explorer, expand the Index node of a physical data model, and then double-click the index you want to change.
  2. You can also edit an index on the Index tab of the Table Editor for the table to which the index belongs.
  3. Complete the Index Editor as required and then click OK.
  4. If you change the index options and can see no reflection of the change in the DDL, click Customize on the DDL tab and ensure the options required to display your changes are selected. For example, If you create a non-unique index for a MySQL table and turn on full text, you must customize the DDL by selecting Generate Defined Nonunique Indexes for the index to show up.

Index Editor Options

The following help you complete the Index Editor options:

  • The options and tabs available depend on the database platform.
  • Set as PK Index: Read-only. If selected, indicates that the index selected is the primary key.
  • Constraint: Enter a constraint to specify that every value in the named column must be unique. Unique constraints are similar to Primary keys. They require unique values throughout the named column or columns in the table.
  • Physical Only: Select to specify that the index be ignored when comparing the physical model to a logical model.
  • Disable: Adds CONSTRAINT... DISABLE to the syntax of the table that contains the PK. Disabling a PK constraint requires that you also disable the FK index of the child table. When you click DISABLE, ER/Studio Data Architect automatically presents you with an option to disable all the FK constraints. Disabling a constraint can improve performance when performing large data loads, making mass changes to tables, or importing or tables one at a time.
  • You cannot disable the primary key of an index-organised table, hence the Disable option described above will not be available for that index type.
  • NoValidate: This option is available for the Oracle 9i, 10g, and 11g platforms. Specify NoValidate if you do not want the constraint to be applied to the data in the table.

If generation of the FK or PK constraint is enabled, NOVALIDATE will be generated if the option is set for the constraint. For example, if the PK is not disabled, but NoValidate is set, the DDL would be

CREATE TABLE Entity1

(

PKCOL CHAR(10) NOT NULL,

CONSTRAINT PK1 PRIMARY KEY (PKCOL) NOVALIDATE

);

The same syntax will be used for FKs when created inside the CREATE TABLE or by ALTER TABLE.

ALTER TABLE ENTITY2 ADD CONSTRAINT BLIVOT

FOREIGN KEY (PK1)

REFERENCES ENTITY1(PK1) NOVALIDATE


If Disable is set but NoValidate is not set, the DDL for an FK created by ALTER TABLE would be the following, since NOVALIDATE is the default option. The same syntax is used for PKs.

ALTER TABLE ENTITY2 ADD CONSTRAINT BLIVOT

FOREIGN KEY (PK1)

REFERENCES ENTITY1(PK1) DISABLE VALIDATE

For information on the various tabs available, click a link below:

Columns tab

Select the columns or keys, the index should be based on. The efficiency of the index depends upon the keys chosen and their search order.

Properties tab

This tab is available for Oracle 8.x, 9i, 10g, 11g and 12c.

  • Index Type: Select the appropriate index type; it can have a big impact on performance.
  • Bitmap: Select for an index that will not be frequently updated by concurrent applications, although be aware that when applied to the index of a unique column (primary key candidate) you need to consider the space required, which depends on the cardinality of the column and the data distribution. A bitmap for each key value is used instead of a list of row IDs.
  • B-tree: Select when indexing a primary key, because Oracle does not support primary key indexes, or select B-tree when space is a consideration. B-tree indexes are balanced to equalize access times to any row.
  • Unique: ( UNIQUE ) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created that lets table rows have duplicate values in the columns that define the index. For a unique index, there is one rowid per data value. If selected, Oracle will generate a separate unique index before creating the PK constraint.
  • No Sort: ( NOSORT ) If selected, specifies to not sort the data when the index is rebuilt.
  • Reverse Byte Order: If selected, reverses the bytes of each column indexed (except the rowid) while keeping the column order. By reversing the keys of the index, the insertions will be distributed across all leaf keys in the index.
  • Enable Compression: ( COMPRESS ) Select to enable index compression. Compression can repress duplication of keys in non-unique indexes. For concatenated indexes (indexes with multiple columns), the compress option can reduce the size of the index by more than half. For concatenated indexes (indexes with multiple columns), compression can reduce the size of the index by more than half. The compress option allows you to specify the prefix length for multiple column indexes.
  • Prefix Length: ( COMPRESS prefix ) This is the prefix length for multiple column indexes and is available when Enable Compression is selected.
  • Tablespace: Displays the tablespace on which the table index is stored.
  • No Logging: Select, if you do not want the DLL operations to be logged in the redo file. This can reduce index creation and updates by up to 30%.
  • Pct Free: ( PCTFREE ) Specifies the maximum percentage of space in each data block to reserve for future updates. This reserved space helps to avoid row migration and chaining caused by an update operation that extends a data row's length. Tables that will not be updated should have this value set to 0.
  • Initial Trans: ( INITTRANS ) Specifies the number of DML transactions for which space is initially reserved in the data block header. Oracle stores control information in the data block to indicate which rows in the block contain committed and uncommitted changes. The amount of history that is retained is controlled by this parameter. If too many transactions concurrently modify the same data block in a very short period, Oracle may not have sufficient history information to determine whether a row has been updated by a too recent transaction. Initial Transactions limit the minimum number of concurrent transactions that can update a data block to avoid the overhead of allocating a transaction entry dynamically. Specify a higher value for indexes that may experience many transactions updating the same blocks.
  • Max Trans: ( MAXTRANS ) Specifies the maximum number of concurrent transactions that can update a data block to avoid performance problems. Once the space reserved by INITRANS is depleted, space for additional transaction entries is allocated out of any available free space. Once allocated, this space effectively becomes a permanent part of the block header. This parameter limits the number of transaction entries that can concurrently use data in a data block and therefore limits the amount of free space that can be allocated for transaction entries in a data block.
  • Parallel: Selects Oracle's parallel query option. You can achieve substantial performance gains by using Oracle's parallel query option.
  • Degrees: Specifies the number of query server processes that should be used in the operation.
  • Instances: Specifies how you want the parallel query partitioned between the parallel servers.
  • Initial Extent: ( INITEXTENT ) Specifies the initial number of data blocks that Oracle should reserve. Oracle will reserve the number of data blocks that correspond to the initial extent for that table's rows.
  • Next Extent: ( NEXT ) Specifies the size in kilobytes of the next extent. You should monitor this figure against the largest available chunk of free space in the tablespace. If a table cannot allocate its next extent, it will no longer be able to extend and, therefore, cannot accept additional data.
  • Pct Increase: ( PCTINCREASE ) Specifies the percentage by which the next extent should grow over the previous extent's size. Be careful when setting Percent Increase because it magnifies how an object grows and, therefore, can materially affect available free space in a tablespace.
  • Min Extents: ( MINEXTENTS ) Specifies the number of extents to allocate when the segment is created. Controls free space fragmentation by making sure that every used or free extent is at least as large as the value you specify.
  • Max Extents: ( MAXEXTENTS ) Specifies the maximum number of extents that Oracle can allocate to the index. Once this limit is reached, Oracle prevents further growth of the cluster and cannot accept additional data. Carefully monitor the number extents already allocated to the index with this limit.
  • Free Lists: ( FREELISTS ) Specifies the number of free lists to apply to the table. The default and minimum value is 1. Free lists can help manage the allocation of data blocks when concurrent processes are issued against the index. Oracle uses the free list to determine which data block to use when an INSERT operation occurs. Oracle allows table and indexes to be defined with multiple free lists. All tables and index free lists should be set to the high-water mark of concurrent INSERT or UPDATE activity. For example, if the index has up to 20 end users performing INSERT s at any time, then the index should have FREELISTS=20.Too low a value for free lists will cause poor Oracle performance. An increase in FREELISTS or FREELIST GROUPS can alleviate segment header contention.
  • Free List Groups: ( FREELIST GROUPS ) Applicable only if you are using Oracle with the Parallel Server option in parallel mode. Specifies the number of free list groups which allow the table to have several segment headers. This enables multiple tasks to insert into the index; thereby alleviating segment header contention. Free list groups should be set to the number of Oracle Parallel Server instances that access the index. For partitioned objects and cases of segment header contention, free list groups my be set for non-RAC systems.
  • Buffer Pools: ( BUFFER_POOL ) Specifies the memory structure or buffer pool that is used for caching data blocks in memory, providing faster data access.
  • DEFAULT: Caches data blocks in the default buffer pool.
  • KEEP: Retains the object in memory to avoid I/O conflicts.
  • RECYCLE: Moves data blocks from memory as soon as they are no longer in use, thereby saving cache space.

Options tab

Options available depend on the database platform selected. For information on the index options available for your database platform, click a link below:

Firebird Index Options

  • Unique: ( UNIQUE ) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created that lets table rows have duplicate values in the columns that define the index.
  • Sort Order: Specify the sort order of the index, Ascending ( ASC ) or Descending ( DESC ).

Hitachi HiRDB Index Options

  • Unique: ( UNIQUE ) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created that lets table rows have duplicate values in the columns that define the index.

IBM DB2 for AS/400, Common Server, and UDB Versions 5 through 9 Index Options

  • Unique: ( UNIQUE ) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created that lets table rows have duplicate values in the columns that define the index.
  • Where Not Null: ( WHERE_NOT_NULL ) If selected, specifies that multiple nulls can exist in a unique index. Useful when an index contains at least one nillable column, but all non-null entries must be unique.
  • Cluster: If selected, specifies to create a clustered index. A clustered index specifies that the rows of the table should be inserted sequentially on data pages, which generally requires less disk I/O to retrieve the data. Generally, the clustered index should be on a column that monotonically increases, such as an identity column, or some other column where the value is increasing, and is unique. This is especially true if the table is subject to many INSERTS, UPDATES, and DELETES. Clustered indexes can greatly increase access speed, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items are selected.
  • Percent Free: ( PCTFREE ) Specifies the maximum percentage of space in each data block to reserve for future updates. This reserved space helps to avoid row migration and chaining caused by an update operation that extends a data row's length. Tables that will not be updated should have this value set to 0.
  • Min Percent Used: ( MINPCTUSED ) Specifies the minimum amount of used space on an index leaf page. If used, online index defragmentation is enabled for this index.
  • Allow Reverse Scans: Specifies that the index be created in a way so that scans can also be performed in the direction opposite to that with which they were defined.
  • Include: Specifies additional columns to be appended to the set of index key columns. The included columns may improve the performance of some queries through index-only access.

IBM DB2 for z/OS Index Options

  • Unique: ( UNIQUE ) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created that lets table rows have duplicate values in the columns that define the index.
  • Cluster: ( CLUSTER ) Select to create a clustered index. A clustered index specifies that the rows of the table should be inserted sequentially on data pages, which generally requires less disk I/O to retrieve the data. Generally, the clustered index should be on a column that monotonically increases--such as an identity column, or some other column where the value is increasing--and is unique. This is especially true if the table is subject to many INSERTS, UPDATES, and DELETES. Clustered indexes can greatly increase access speed, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items are selected.
  • For IBM DB2 for OS/390 5.x, the Cluster option must be selected to enable the index partitioning options on the Partitions tab of the Index Editor. For more information, see Partitioning a Table Index.
  • Where Not Null: ( WHERE NOT NULL ) Specifies that multiple nulls can exist in a unique index. Useful when an index contains at least one nillable column, but all non-null entries must be unique.
  • Concurrent Copy: ( COPY ) If selected, concurrent copies of the index are allowed, which can increase index availability by supporting index recovery.
  • Do Not Close Dataset: ( CLOSE ) If selected, the index will not be closed when not in use. If not selected, the dataset can be closed if it is not in use and the maximum number of open datasets is reached or exceeded.
  • Defer Index Creation: ( DEFER ) Specifies that index modifications are to be deferred during INSERT, UPDATE, and DELETE operations. This allows the data updates to complete quicker, but queries may not return the most up-to-date information until the index is synchronized. This option is not supported for an index on a declared temporary or auxiliary table.
  • Padded: ( PADDED ) If selected, the index entry is padded to the full length of its datatype which makes scanning the index efficient if most of the index entries are close to or at the maximum length. If not selected, the index supports true variable length key which allows for shorter indexes and more index entries on each page. It also allows index-only access. If the index is not padded, data can be retrieved directly from the index without having to look up the data length in the data page. Not padding the index can be more efficient if you will save a lot of disk space by not padding them.
  • Partitioned: Select if you want to partition the index. Then the options in the Index Partitions Editor accessible from the Index Editor > Partitions tab become available. For more information, see Partitioning a Table Index.
  • Using Claus: ( USING ) Specifies what kind of index space is created for the index and who manages the index. If you do not specify a USING claus, DB2 manages the index space on volumes listed in the default storage group of the table's database, which must exist. DB2 uses the defaults values PRIQTY, SECQTY, and Erase Data.
  • VCAT: ( VCAT ) If selected, allocates space for the index on a data set managed using the virtual catalog (VCAT) whose name is specified next to this option, and the user manages the data sets required for the index. The VCAT method is usually used for defining system catalogs. It requires that the VSAM dataset be pre-defined before the index space is created.
  • STOGROUP: ( STOGROUP ) If selected, stores the index on a data set managed by DB2 in the named storage group. Stogroup defined index spaces let DB2 do all the VSAM allocation work for you. If you have created a Stogroup for the data model, using the Stogroup Editor (see [ERStudio.htm#50614277_67903 See Creating and Editing StoGroups]), you can select that Stogroup from the list. If you select Stogroup, you can also define values for PRIQTY and SECQTY. To prevent wasted space for non-partitioned indexes, you should not define PRIQTY and SECQTY ; instead let DB2 manage the amount of primary and secondary space allocated for the index.
  • PRIQTY:( PRIQTY ) Specifies the minimum number of disk space in kilobytes to initially allocate for the index. The primary allocation should be large enough to handle the storage needs that you anticipate.
  • SECQTY: ( SECQTY ) Specifies the amount of disk space in kilobytes to allocate to the index when it reaches the size of the primary allocation. If the secondary allocation space is too small, the data set might have to be extended more times to satisfy those activities that need a large space. The default value of -1 indicates that DB2 should determine the size of the secondary allocation, which is usually 10% of the primary allocation.
  • Erase Data: ( ERASE ) If selected, specifies to erase the index data set when the index is deleted.
  • None: If selected, no USING claus is included in the CREATE INDEX statement.
  • Buffer Pool: ( BUFFERPOOL ) Identifies the buffer pool to be used for the index. The buffer pool used determines how the data blocks are cached in memory, in order to facilitate faster data access. BP0 is the default buffer pool for sorting.
  • Percent Free: ( PCTFREE ) Specifies the maximum percentage of space in each data block to reserve for future updates. This reserved space helps to avoid row migration and chaining caused by an update operation that extends a data row's length. Tables that will not be updated should have this value set to 0.
  • Free Page: ( FREEPAGE ) Specifies how often to leave a page of free space when index entries are created. One free page is left for every number of pages specified here in the range of 0 to 255.
  • Piece Size: ( PIECESIZE ) Specifies the maximum addressable space of each data set for a secondary index. Specify the size followed by K (kilobyte), M (Megabyte), of G (Gigabyte). If you do not specify the unit of measure, kilobytes is assumed. Sizes specified in gigabytes are converted to megabytes in the DDL.
  • GBP Cache: ( GPBCACHE ) In a data sharing environment, specifies what index pages are written to the group buffer pool. This option is ignored in a non-data-sharing environment unless the index is on a declared temporary table. Specify Changed to write updated pages to the group buffer pool, All to cache all pages to the group buffer pool as they are read in from DASD or None to indicate that the buffer pool is only to be used for cross-invalidation.

Informix Index Options

  • Unique: ( UNIQUE ) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created that lets table rows have duplicate values in the columns that define the index.
  • Cluster: ( CLUSTER ) Select to create a clustered index. A clustered index specifies that the rows of the table should be inserted sequentially on data pages, which generally requires less disk I/O to retrieve the data. Generally, the clustered index should be on a column that monotonically increases--such as an identity column, or some other column where the value is increasing--and is unique. This is especially true if the table is subject to many INSERTS, UPDATES, and DELETES. Clustered indexes can greatly increase access speed, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items are selected.

Interbase Index Options

  • Unique: ( UNIQUE ) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created that lets table rows have duplicate values in the columns that define the index.
  • Sort Order: Specify the sort order of the index, Ascending ( ASC ) or Descending ( DESC ).

Microsoft Access and SQL Server Index Options

The options available depend on the database platform and version.

  • Unique: ( UNIQUE ) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created that lets table rows have duplicate values in the columns that define the index.
  • Clustered: ( CLUSTERED or NONCLUSTERED ) Select to create a clustered index. A clustered index specifies that the rows of the table should be inserted sequentially on data pages, which generally requires less disk I/O to retrieve the data. Generally, the clustered index should be on a column that monotonically increases, such as an identity column, or some other column where the value is increasing, and is unique. This is especially true if the table is subject to many INSERTS, UPDATES, and DELETES. Clustered indexes can greatly increase access speed, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items are selected. If the index is not unique and the Clustered option is selected, the Non-Unique Clustered Options become available.
  • Ignore Dup Keys: ( IGNORE_DUP_KEY ) Specifies that if you attempt to create duplicate keys by adding or updating data that affects multiple rows (with the INSERT or UPDATE statement), the row that causes the duplicates is not added or, in the case of an update, is discarded.
  • Sorted Data: ( SORTED_DATA ) Specifies to sort the data when the index is rebuilt.
  • Non-Unique Clustered Options: Specifies the action to take when a duplicate row is added.

MySQL Index Options

  • Unique: ( UNIQUE ) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created that lets table rows have duplicate values in the columns that define the index.
  • Fulltext: Lets you set the index as a full-text index. Full-text indexes in MySQL are an index of type FULLTEXT. FULLTEXT indexes are used with MyISAM tables only and can be created from CHAR, VARCHAR, or TEXT columns at CREATE TABLE time or added later with ALTER TABLE or CREATE INDEX.

Teradata Index Options

  • Unique: ( UNIQUE ) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created that lets table rows have duplicate values in the columns that define the index.
  • Primary: ( PRIMARY ) If selected, specifies that this index is the primary index. If you do not define a primary index, by default the first column in the table becomes the primary index in the table.
  • Partitioning Expression: ( PARTITION BY expression ) Available if you selected Primary. Enter an expression to partition the data by a particular attribute, such as date by week, for some time. Partitioning the data enables you to more efficiently retrieve summarized data for a particular week because you only have to read the data for that week. Good candidates for a Partitioned Primary Index are tables where there is a high volume of daily inserts, so there is a bias toward partitioning on the data date. Partitioning an index by product_code or agent_id can also improve some queries. If you need more space than available in the text box to enter your expression, click More to launch the Partition Expression Editor where you can input a multi-line expression. The following is an example of a partitioning expression:
    PARTITION BY RANGE_N(CAPTURE_DATE BETWEEN DATE '2007-11-30' AND DATE '2008-2-30' EACH INTERVAL '1' MONTH).
  • Collect Statistics: ( COLLECT STATISTICS) Collects demographic data for one or more columns of a table, computes a statistical profile of the collected data, and stores the profile in the data dictionary. Collecting full statistics involves scanning the base table, and sorting to determine the number of occurrences for each unique value. The Teradata Optimizer uses this profiling data when it generates its table access and join plans. Full statistics is generally recommended for relevant columns and indexes on tables with less than 100 rows per Access Module Processor (AMP).

This is an example of the SQL generated when Collect Statistics has been selected in the Index Editor for an index in the table.

COLLECT STATISTICS ON table_1 INDEX unique_1;

  • Using Sample Collecting sampled full-table statistics is generally faster than collecting full table statistics, but may not produce the best table access and join plans. When you specify this option, Teradata will determine the appropriate percentage to the data to scan. The best choices for USING SAMPLE are columns or indexes that are unique or 95% unique.

This is an example of the SQL generated when Collect Statistics and Using Sample have been selected in the Index Editor for an index in the table.

COLLECT STATISTICS USING SAMPLE ON table_1 INDEX unique_1;

Oracle 7 Index Options

  • Unique: ( UNIQUE ) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created that lets table rows have duplicate values in the columns that define the index.
  • No Sort: ( NOSORT ) If selected, specifies to not sort the data when the index is rebuilt.

Postgre SQL Index Options

  • Unique: ( UNIQUE ) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created that lets table rows have duplicate values in the columns that define the index.
  • Clustered: ( CLUSTER ) Select to create a clustered index. A clustered index specifies that the rows of the table should be inserted sequentially on data pages, which generally requires less disk I/O to retrieve the data. Generally, the clustered index should be on a column that monotonically increases, such as an identity column, or some other column where the value is increasing, and is unique. This is especially true if the table is subject to many INSERTS, UPDATES, and DELETES. Clustered indexes can greatly increase access speed, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items are selected.

Sybase Index Options

The options available depend upon the database platform version.

  • Unique: ( UNIQUE ) If selected, specifies that the index key contains no duplicate values and therefore every row in the table is in some way unique. If not selected, a non-unique index is created where table rows can have duplicate values in the columns that define the index.
  • Type: Sybase IQ uses HG or LF indexes to generate better or faster query plans to execute the query. The HG or LF index may also be appropriate for a DATE, TIME, DATETIME, or TIMESTAMP column, if you are evaluating equality predicates against the column. An LF index is also recommended, if you frequently use the column in the GROUP BY clause and there are less than 1000 distinct values, for example, less than three years of dates.
  • HG: Use an HG (High Group) index when the column will be used in a join predicate or the column has more than 1,000 unique values. The High_Group index is commonly used for join columns with integer data types because it handles GROUP BY efficiently. Each foreign key columns requires its own HG index; however, if a join index exists, the same column cannot have both an explicitly created HG index and a foreign key constraint.
  • LF: LF is the fastest index in Sybase IQ. Use an LF (Low Fast) index when a column has less than 1,000 unique values or a column has less than 1,000 unique values and is used in a join predicate. This index is ideal for columns that have a few unique values (under 1,000) such as sex, Yes/No, True/False, number of dependents, and wage class.Never use an LF index for a column with 10,000 or more unique values. If the table has less than 25,000 rows, use an HG index, which requires fewer disk I/O operations for the same operation.
  • Clustered: ( CLUSTER ) Select to create a clustered index. A clustered index specifies that the rows of the table should be inserted sequentially on data pages, which generally requires less disk I/O to retrieve the data. Generally, the clustered index should be on a column that monotonically increases, such as an identity column, or some other column where the value is increasing, and is unique. This is especially true if the table is subject to many INSERTS, UPDATES, and DELETES. Clustered indexes can greatly increase access speed, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items are selected.
  • Ignore Dup Keys: ( IGNORE_DUP_KEY ) Specifies that if you attempt to create duplicate keys by adding or updating data that affects multiple rows (with the INSERT or UPDATE statement), the row that causes the duplicates is not added or, in the case of an update, is discarded.
  • Sorted or Sorted Data: ( SORTED_DATA ) Specifies to sort the data when the index is rebuilt.
  • Non-Unique Clustered Options: Choose how you want to handle duplicate rows.
  • Ignore Dup Rows: ( IGNORE_DUP_ROW ) Eliminates duplicate rows from a batch of data and cancels any insert or update that would create a duplicate row, but does not roll back the entire transaction.
  • Allow Dup Rows: ( ALLOW_DUP_ROW ) Allows you to create a new, non-unique clustered index on a table that includes duplicate rows. If a table has a non-unique clustered index that was created without the allow_dup_row option, you cannot create new duplicate rows using the insert or update command.
  • None: If neither Ignore Dup Rows or Allow Dup Rows has been selected, when you attempt to create an index on a table that has duplicate rows the creation fails, and the insertion fails If you try to insert a duplicate row to an indexed table.

Storage tab

Lets you select various storage options depending upon the selected database platform. For more information, see Defining Index Storage.

Partitions tab

Lets you select various storage options depending upon the selected database platform. For more information, see Partitioning a Table Index.

Compare Options tab

Select the differences you want the Compare and Merge Utility to ignore.

Attachment Bindings tab

Bind an external piece of information, or attachment to the index. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.

See Also

Retrieved from "http://docwiki.embarcadero.com/ERStudioDA/184/e/index.php?title=Creating_and_Editing_Indexes&oldid=5089"