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.

table partition

4 posts in Windows NT Last posting was on 2000-03-29 21:39:21.0Z
Stephen Harris Posted on 2000-03-10 16:20:04.0Z
Reply-To: "Stephen Harris" <sharris1@hboc.com>
From: "Stephen Harris" <sharris1@hboc.com>
Subject: table partition
Date: Fri, 10 Mar 2000 11:20:04 -0500
Lines: 21
Organization: McKesson HBOC
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <7U2xz0qi$GA.251@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: hboc.com 139.177.224.128
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2430
Article PK: 1089885

Hello all,

hope someone can help with this. I have a database with one table that
holds itemized charges for another table, unfortunately, due to a lack of
forsight this table has become 14 times as large as the parent. Performance
when this table is touched has become really really slow. I have a unique
clustered index on the table. I was thinking of partitioning the table to
speed up the i/o; however, I haven't found anything that tells me what
effect this will have on my index. Will I have to drop and recreate the
index? Will this make the index smaller or larger? Also is the index
structure significantly different on partitioned vs. non-partitioned?
Lastly, for performance reasons (I have lots of rows in the table that are
deleted and replaced) I have to drop and recreate the index once every month
and a half or so. I have to have a ton of free data space to do this, will
partitioning help with this procedure? Any suggestions will be appreciated.

Thanks,
Steve Harris
McKesson HBOC


Eric McGrane Posted on 2000-03-14 16:16:50.0Z
Message-ID: <38CE65F2.5C7F5D37@sybase.com>
Date: Tue, 14 Mar 2000 11:16:50 -0500
From: Eric McGrane <mcgrane@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.7 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Stephen Harris <sharris1@hboc.com>
Subject: Re: table partition
References: <7U2xz0qi$GA.251@forums.sybase.com>
Content-Type: multipart/mixed; boundary="------------36837A7BEFFD8BD3CD26D9F7"
Newsgroups: sybase.public.sqlserver.nt
Lines: 58
NNTP-Posting-Host: mcgrane-nt.sybase.com 157.133.126.171
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2416
Article PK: 1089871

Stephen,

When you partition a table that already ahs data in it the data will all go to
the first partition. In order to get the data spread out over the partitions
"evenly" you will need to dro pand recreate the clustered index. As far as the
index structure goes, it is the same for partitioned vs non-partitioned tables.
Will this help your reindexing? I honestly don't know whether it will or not.
Sorry.

E

Stephen Harris wrote:

> Hello all,
>
> hope someone can help with this. I have a database with one table that
> holds itemized charges for another table, unfortunately, due to a lack of
> forsight this table has become 14 times as large as the parent. Performance
> when this table is touched has become really really slow. I have a unique
> clustered index on the table. I was thinking of partitioning the table to
> speed up the i/o; however, I haven't found anything that tells me what
> effect this will have on my index. Will I have to drop and recreate the
> index? Will this make the index smaller or larger? Also is the index
> structure significantly different on partitioned vs. non-partitioned?
> Lastly, for performance reasons (I have lots of rows in the table that are
> deleted and replaced) I have to drop and recreate the index once every month
> and a half or so. I have to have a ton of free data space to do this, will
> partitioning help with this procedure? Any suggestions will be appreciated.
>
> Thanks,
> Steve Harris
> McKesson HBOC


Download VCard mcgrane.vcf


Ahmad AbdelHady Posted on 2000-03-21 12:08:42.0Z
Reply-To: "Ahmad AbdelHady" <ahady@generation.ca>
From: "Ahmad AbdelHady" <ahady@generation.ca>
References: <7U2xz0qi$GA.251@forums.sybase.com> <38CE65F2.5C7F5D37@sybase.com>
Subject: Re: table partition
Date: Tue, 21 Mar 2000 14:08:42 +0200
Lines: 59
Organization: Generation Systems Inc.
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <DWW3v6yk$GA.290@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 60-15.idsc.gov.eg 163.121.15.60
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2391
Article PK: 1089847

partioning does not help "select" scenarios but it speeds up "write"
scenarios (update,delete and insert) ..
do not use clustered index at all use NonClustered ones and use them over
foreign column(s) as well ...
bind master table primary index and details table foreign index to a rpivate
cache ..
if you insist on using partioning use them with segments over different
harddisk partions ..

Eric McGrane <mcgrane@sybase.com> wrote in message
news:38CE65F2.5C7F5D37@sybase.com...
> Stephen,
>
> When you partition a table that already ahs data in it the data will all
go to
> the first partition. In order to get the data spread out over the
partitions
> "evenly" you will need to dro pand recreate the clustered index. As far
as the
> index structure goes, it is the same for partitioned vs non-partitioned
tables.
> Will this help your reindexing? I honestly don't know whether it will or
not.
> Sorry.
>
> E
>
> Stephen Harris wrote:
>
> > Hello all,
> >
> > hope someone can help with this. I have a database with one table that
> > holds itemized charges for another table, unfortunately, due to a lack
of
> > forsight this table has become 14 times as large as the parent.
Performance
> > when this table is touched has become really really slow. I have a
unique
> > clustered index on the table. I was thinking of partitioning the table
to
> > speed up the i/o; however, I haven't found anything that tells me what
> > effect this will have on my index. Will I have to drop and recreate the
> > index? Will this make the index smaller or larger? Also is the index
> > structure significantly different on partitioned vs. non-partitioned?
> > Lastly, for performance reasons (I have lots of rows in the table that
are
> > deleted and replaced) I have to drop and recreate the index once every
month
> > and a half or so. I have to have a ton of free data space to do this,
will
> > partitioning help with this procedure? Any suggestions will be
appreciated.
> >
> > Thanks,
> > Steve Harris
> > McKesson HBOC
>


Eric McGrane Posted on 2000-03-29 21:39:21.0Z
Message-ID: <38E27809.8C29C6C1@sybase.com>
Date: Wed, 29 Mar 2000 16:39:21 -0500
From: Eric McGrane <mcgrane@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.7 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Ahmad AbdelHady <ahady@generation.ca>
Subject: Re: table partition
References: <7U2xz0qi$GA.251@forums.sybase.com> <38CE65F2.5C7F5D37@sybase.com> <DWW3v6yk$GA.290@forums.sybase.com>
Content-Type: multipart/mixed; boundary="------------0D9016AD7318665714F92760"
Newsgroups: sybase.public.sqlserver.nt
Lines: 86
NNTP-Posting-Host: mcgrane-nt.sybase.com 157.133.126.171
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2371
Article PK: 1089795

Actually, partitioning can "help" select scenarios in that there is the
possiblity of using parallelism when a table is partitioned. You can also use
parallelism when a table is not partitioned, but the fact that a table is
partitioned gives you a couple of extra ways to perform the select, partition
based table scan or partition based clustered index scan.

Just thought I'd throw it out there.

E

Ahmad AbdelHady wrote:

> partioning does not help "select" scenarios but it speeds up "write"
> scenarios (update,delete and insert) ..
> do not use clustered index at all use NonClustered ones and use them over
> foreign column(s) as well ...
> bind master table primary index and details table foreign index to a rpivate
> cache ..
> if you insist on using partioning use them with segments over different
> harddisk partions ..
> Eric McGrane <mcgrane@sybase.com> wrote in message
> news:38CE65F2.5C7F5D37@sybase.com...
> > Stephen,
> >
> > When you partition a table that already ahs data in it the data will all
> go to
> > the first partition. In order to get the data spread out over the
> partitions
> > "evenly" you will need to dro pand recreate the clustered index. As far
> as the
> > index structure goes, it is the same for partitioned vs non-partitioned
> tables.
> > Will this help your reindexing? I honestly don't know whether it will or
> not.
> > Sorry.
> >
> > E
> >
> > Stephen Harris wrote:
> >
> > > Hello all,
> > >
> > > hope someone can help with this. I have a database with one table that
> > > holds itemized charges for another table, unfortunately, due to a lack
> of
> > > forsight this table has become 14 times as large as the parent.
> Performance
> > > when this table is touched has become really really slow. I have a
> unique
> > > clustered index on the table. I was thinking of partitioning the table
> to
> > > speed up the i/o; however, I haven't found anything that tells me what
> > > effect this will have on my index. Will I have to drop and recreate the
> > > index? Will this make the index smaller or larger? Also is the index
> > > structure significantly different on partitioned vs. non-partitioned?
> > > Lastly, for performance reasons (I have lots of rows in the table that
> are
> > > deleted and replaced) I have to drop and recreate the index once every
> month
> > > and a half or so. I have to have a ton of free data space to do this,
> will
> > > partitioning help with this procedure? Any suggestions will be
> appreciated.
> > >
> > > Thanks,
> > > Steve Harris
> > > McKesson HBOC
> >


Download VCard mcgrane.vcf