recreate clustered indexes on huge table

[suznCB]

Dears,

we have a huge table with a clustered index and 6 non-clustered indexes, when we create this index in the past there is one of our processes -which executes on this table- took as an example about 10 minutes to complete, after a week later , the same process took about 30 minutes, even though we run update statistics on that table at midnight everyday,

kindly note that table is the most active table in the database with a lot of insert commands and also a lot off select ones (the delete operation is prevented)

Now we are thinking  to drop the clustered index and recreate it,

so my inquiries are:
If we drop it, what the affect to the non clustered indexes on the table?
as the data in the table is already sorted, what about specify sorted data while creating the index in order to speed up the time of creating it?
the current fill factor value for all indexes in that table is 0, how to calculate the best value to change to it, and what are the benefits on both insert and select commands on the table

kindly note that clustered index is composed of 3 columns

Regards

[XaviPolo]

I recommend you to change the post tag to SAP IQ. In Datasphere IQ is not used and there is no possibility to manage indexes.

[cardoso]

Definitively a post in wrong tag.

[RobertWaywell]

You have tagged this question as being related to SAP IQ but you have also added a tag for Sybase ASE. Clustered indexes suggest this question might be about ASE instead of IQ. Can you clarify if this is an ASE question? Thanks

[cholas90]

I think dropping and recreating a clustered index on a large table can impact performance and indexing strategies. Dropping the clustered index affects non-clustered indexes dependent on its order. While recreating it may improve performance for ordered data access

and also specify sorted data during index creation could aid in speed but depends on data distribution.

Adjusting the fill factor, especially from 0 balances space utilization and maintenance overhead and impact insert and select performance. hence experimenting with values is advisable and good for streaming on some platforms like DixMax which is available on dixmax.com.es, if you want to achieve some optimal performance.

[suznCB]

thanks for your reply could you please explain how fill factor impact both insert and select performance