Sybase – Index Property Precautions

I have a table in SYBASE, which has about 1 billion rows. There is currently no index created for this table, I want to create one now. My question is

>Before creating an index, what precautions should I take?
>Does this process need to allocate more table space?
>Any other performance considerations I should consider?

Cheers

Langes

>from manual

When to index

Use the following general guidelines:

>If you plan to insert manually in the IDENTITY column, please create
A unique index to ensure that the insert does not assign a value
has been used.
>Columns that are usually accessed in sorted order, that is, the columns specified in the order by clause, may be indexed
Adaptive Server can take advantage of the index order.
>Should always create an index on the columns that are frequently used in the connection, because if the column is listed, the system can perform the connection faster
In the sort order.
>The primary key of the storage table A column usually has a clustered index, especially if it is frequently connected to the column
other tables. Remember, there can only be one clustered index
table for each.
>columns with a range of values ​​are often searched May be a good choice for a clustered index. Once the row with the first value
is within the found range, the rows with subsequent values ​​are guaranteed to be adjacent to the body. The clustered index does not provide as many
The advantage of searching for a single value.

When not to index

In some cases, the index is useless:

>In the query rarely or never The referenced columns will not benefit
from the index, because the system rarely needs to search on the row
based on the values ​​in these columns.
>Columns with only two or three values, such as “male” And “female” or “yes” and “no”, there is no real advantage
index.

>try

sp_spaceused tablename,1

Here is the link to the document.
>Yes-update statistics about the index.

Here is the link to the document.

I am in SYBASE There is a table in, it has about 1 billion rows. This table does not currently create any index, I want to create one now. My question is

>Before creating an index, should I take What preventive measures?
>Does this process need to allocate more table space?
>Any other performance considerations I should consider?

Cheers

Langes

>From manual.

When to index

Use the following general guidelines:

>If you plan to manually insert in the IDENTITY column, please create
a unique index to ensure that the insert does not assign a value< br>has been used.
>Columns that are usually accessed in sorted order, that is, the columns specified in the order by clause, may be indexed
Adaptive Server can take advantage of the index order.
>Should always Create an index on the columns that are frequently used in the join, because if the column, the system can perform the join faster
arranged in sort order.
>The column storing the primary key of the table usually has a clustered index, especially if it is frequently Connect to other tables in the column
Remember that there can only be one clustered index
table for each.
>Columns that often search for a range of values ​​may be a good choice for a clustered index. Once you have the first Rows of one value
within the found range, rows with subsequent values ​​are guaranteed
the body is adjacent. A clustered index does not provide as many advantages as
searching for a single value.

When not to index

In some cases, the index is useless:

>Columns that are rarely or never referenced in the query will not benefit
from the index, Because the system rarely needs to search on the basis of the values ​​in the rows
in these columns.
>Columns with only two or three values, such as “male” and “female” or “yes” and “no” , There is no real advantage
index.

>try

sp_spaceused tablename,1

Here is the link to the document.
>Yes – Update statistics about the index.

Here is a link to the document.

Leave a Comment

Your email address will not be published.