Non clustered index query

[marcmcconnell]

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)

 

[MadHatterCH]

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.