Taker
unread,Apr 18, 2012, 1:47:53 PM4/18/12You do not have permission to delete messages in this group
Either email addresses are anonymous for this group or you need the view member email addresses permission to view the original message
to
Hi Gurus,
I have an 7*24 OLTP banking system which run a purge job weekly.
The purge job simply:
a) drop all indices from a large table (10G data)
b) delete many records from the large table
c) re-create all indices
The table has 11 non-clustered indices, there's no clustered index.
Application team complained that the purge job takes too long to complete (more than 3 hours) we (DBA) identified that the bottleneck is the step c of the job -- re-create all indices
Some facts:
- Sybase ASE version 15.0.3 ESD #1
- It's a vendor system, vendor doesn't support table partitioning at the moment
- The purge job is encapsulated in vendor's program, so it's not feasible to change the sql to create index in parallel (currently it's created one by one)
- All user tables' lock scheme are DOL
- All indices on that table are non-clustered, so can't use "with sort_data" option
- The @@maxpagesize of the server is 4096
- We've got a 15 G default data cache (10G for 4K pool and 5G for 16k pool)
My question is: how to reduce non-clustered index creation time?