Sybase - performance considerations when indexing an existing table

Question

I have a table in SYBASE that has about 1mio rows. This table currently has no index, and I would like to create it now. My questions

• What precautions should be taken before creating an index?
• Does this process require more table space?
• Any other performance considerations I should take care of?

Greetings

Ranjith

Answer 1

• From manual .

  When to index

  Use the following general guidelines:

  • If you plan to enter manual inserts in the IDENTITY column, create a unique index to ensure that the inserts do not assign a value that is already in use.

  • A column that is often accessed in sorted order, that is, specified in an order by clause, should probably be indexed so that Adaptive Server can take advantage of the indexed order.

  • Columns that are regularly used in joins should always be indexed, because the system can complete the join faster if the columns are in sorted order.

  • The column in which the primary key of the table is stored often has a clustered index, especially if it is often joined with columns in other tables. Remember that there can only be one clustered index per table.

  • A column that often searches for ranges of values ​​may be a good choice for a clustered index. As soon as a row with the first value within the range, rows with subsequent values ​​are guaranteed to be physically adjacent. The clustered index does not offer so much advantage when searching for single values.

  If not indexed

  In some cases, indexes are not useful:

  • Columns that rarely or never refer in queries do not help from indexes, since the system rarely has to search for rows based on the values ​​in these columns.

  • Columns that can have only two or three values, for example, “man” and “woman” or “yes” and “no”, do not get real benefits from indexes.

• Try

  sp_spaceused tablename, 1

  Here is a link to the documentation.

• Yes - Updating statistics about indexes .

  Here is a link to the documentation.