Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Index R-build rather than drop/recreate

3 posts in Product Futures Discussion Last posting was on 2002-11-21 16:44:23.0Z
Matt Rogish Posted on 2002-11-18 14:39:08.0Z
From: "Matt Rogish" <matt@fanhome.com>
Subject: Index R-build rather than drop/recreate
Date: Mon, 18 Nov 2002 09:39:08 -0500
Lines: 11
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <2$VS49wjCHA.259@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: woh-166-196-2.woh.rr.com 24.166.196.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:240
Article PK: 93413

On-line index rebuilds for APL tables would be great. Often one does not
have the availability window to completely drop and recreate an index.
Further, benchmarks in other DBMS's show that an online rebuild is *many*
times faster and "almost as good" as dropping and re-creating.

Thanks,

--
Matt


Roland Posted on 2002-11-21 08:21:35.0Z
From: Roland <REMOVEZroland.Zvan.Zveen@Zsybase.Zcom>
Subject: Re: Index R-build rather than drop/recreate
Date: Thu, 21 Nov 2002 09:21:35 +0100
Message-ID: <MPG.18441cd662c39fde9896bf@forums.sybase.com>
References: <2$VS49wjCHA.259@forums.sybase.com>
Organization: Sybase, Inc.
X-Newsreader: MicroPlanet Gravity v2.50
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 21
NNTP-Posting-Host: 158.76.4.53
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:236
Article PK: 93409

In article <2$VS49wjCHA.259@forums.sybase.com>, matt@fanhome.com says...

> On-line index rebuilds for APL tables would be great. Often one does not
> have the availability window to completely drop and recreate an index.
> Further, benchmarks in other DBMS's show that an online rebuild is *many*
> times faster and "almost as good" as dropping and re-creating.
>
> Thanks,
>
> --
> Matt
>
>
>

I did this on APL tables that are not too big to rebuild indexes
whenever I had no index DDL scripts. There must be about 120% freespace
for the largest table size available in the database.


allow select/into/bcp/pllsort in database,

alter table ..lock datapages
alter table ..lock allpages
update statistics
sp_recompile


--
Roland van Veen [http://www.sybase.nl/solutions/consulting.html]


Matt Rogish Posted on 2002-11-21 16:44:23.0Z
From: "Matt Rogish" <matt@fanhome.com>
References: <2$VS49wjCHA.259@forums.sybase.com> <MPG.18441cd662c39fde9896bf@forums.sybase.com>
Subject: Re: Index Rebuild rather than drop/recreate
Date: Thu, 21 Nov 2002 11:44:23 -0500
Lines: 44
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <MTCxsxXkCHA.309@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: woh-166-196-2.woh.rr.com 24.166.196.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:235
Article PK: 93410

That seems like a worse solution which would cost far more I/O than simply
dropping and re-creating indexes, but I can see if you have lost the DDL you
would be forced to do it that way.

Note: I use primarily APL tables (and APL is assumed unless otherwise
stated) and do not have much experience with datarows locking, so perhaps
some of this does not apply to datarows.

My reasons for online index rebuilds are two-fold:
1) Locking - dropping and re-creating an index is painful. Dropping results
in queries now running without indexes causing table scans! Also if I
recall correctly while the index is being built it places locks on the
table, severely limiting concurrency. An in-place, online rebuild would be
able to utilize page (or row locking) to maximize concurrency while each
page is rebuilt. Also some pages may not need to be rebuilt which helps
speed the process along.
2) Total I/O and time requirements. Reorganizing non-clustered indexes
should not require near as much I/O as dropping, reading *every* table data
page, and then writing index pages. The index already has allocated many
extents which can certainly be reused if necessary. As I said before some
index pages may not even need to be touched and can be skipped, saving time
and I/O.

Thanks,

--
Matt

"Roland" <REMOVEZroland.Zvan.Zveen@Zsybase.Zcom> wrote in message
news:MPG.18441cd662c39fde9896bf@forums.sybase.com...
> I did this on APL tables that are not too big to rebuild indexes
> whenever I had no index DDL scripts. There must be about 120% freespace
> for the largest table size available in the database.
> allow select/into/bcp/pllsort in database,
>
> alter table ..lock datapages
> alter table ..lock allpages
> update statistics
> sp_recompile
>
> --
> Roland van Veen [http://www.sybase.nl/solutions/consulting.html]