Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Non clustered index query

Status
Not open for further replies.

marcmcconnell

Technical User
Apr 19, 2001
25
GB
I have been asked to create a non clustered index for our sybase database to speed up query response times. This is the first time I have done such a thing. The command I am using is :

use DMS_STA
go
create nonclustered index DMS_STA_TRANSAC_HIST_I1
on DMS_STA_TRANSAC_HIST
(
transaction_id1
)
go

The problem is that we are running out of space on the database server. It is an old system (AIX 4.2.1).The database is 87.5% full (total capacity is 2.6GB).

The questions I have are :

How much space (if any) does the indexing need?
Roughly how long will indexing take?
Can the database be used during the indexing?

For information - we have no space left to add more devices (eg external disk) - all vdevno numbers have been used (checked sp_helpdevice command)

 
Hi,
I can answer a couple of your points:

1. Space - how many rows have you got * how big is the field to be indexed ( + 10%)
as a guide - for each index

3. used during - wouldn't advise it.

Have you considered just generating your statistics more often?
This would ensure that you don't add more indexes but use what you have in a more efficient manner.
and if you're running at over 80% you should consider a new disk.

Hope that helps,
Paul.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top