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.

max_rows_per_page

4 posts in General Discussion Last posting was on 2012-12-26 11:04:54.0Z
karthik Posted on 2012-12-24 09:40:28.0Z
Sender: 3e56.50d8220b.1804289383@sybase.com
From: Karthik
Newsgroups: sybase.public.ase.general
Subject: max_rows_per_page
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50d8230c.3e94.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 24 Dec 2012 01:40:28 -0800
X-Trace: forums-1-dub 1356342028 172.20.134.41 (24 Dec 2012 01:40:28 -0800)
X-Original-Trace: 24 Dec 2012 01:40:28 -0800, 172.20.134.41
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31598
Article PK: 1158595

Hi,

There is a table with 85 rows along with the below
properties.

fill factor = 0
max_rows_per_page = 0
Locking Schema : All Page
Index : Clustered index on monotonically increasing column
(No Composite index)

I just changed the attributes as below

sp_chgattribute sf, "max_rows_per_page", 80

sp_chgattribute sf, "fillfactor", 80

When I execute sp_help sf, I can see 3 Datapages for 85 rows
eventhough I changed the attribute "max_rows_per_page" to
80.
Technically it should be 2 right. why the new change is not
reflecting ?

Ihave also ran "update statistics sf" to see is there any
change. But No Luck.

what i am missing here?


Rob V Posted on 2012-12-24 18:53:04.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: max_rows_per_page
References: <50d8230c.3e94.1681692777@sybase.com>
In-Reply-To: <50d8230c.3e94.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50d8a490$1@forums-1-dub>
Date: 24 Dec 2012 10:53:04 -0800
X-Trace: forums-1-dub 1356375184 172.20.134.152 (24 Dec 2012 10:53:04 -0800)
X-Original-Trace: 24 Dec 2012 10:53:04 -0800, vip152.sybase.com
Lines: 69
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31600
Article PK: 1158600


On 24-Dec-2012 10:40, Karthik wrote:
> Hi,
>
> There is a table with 85 rows along with the below
> properties.
>
> fill factor = 0
> max_rows_per_page = 0
> Locking Schema : All Page
> Index : Clustered index on monotonically increasing column
> (No Composite index)
>
> I just changed the attributes as below
>
> sp_chgattribute sf, "max_rows_per_page", 80
>
> sp_chgattribute sf, "fillfactor", 80
>
> When I execute sp_help sf, I can see 3 Datapages for 85 rows
> eventhough I changed the attribute "max_rows_per_page" to
> 80.
> Technically it should be 2 right. why the new change is not
> reflecting ?
>
> Ihave also ran "update statistics sf" to see is there any
> change. But No Luck.
>
> what i am missing here?
>

1:
For a table that already contains rows, these rows are not affected when
you change max_rows_per_page. Only when new rows are inserted (or
updated) will the new setting start to take effect. Pages that have more
than max_rows_per_page rows will not have their rowcount automatically
reduced to max_rows_per_page, this only happens when you delete rows, do
deferred updates or when you run reorg rebuild.

2:
max_rows_per_page defines a *maximum* #rows per page. Theoretically it
is possible that you have a very fragmented table with only one row per
data page so you'd have 85 pages and 85 rows. That still satisfies
max_rows_per_page=80.

3:
update statistics has nothing to do with max_rows_per_page.

4:
if you want to enforce the new max_rows_per_page setting, run 'reorg
rebuild sf'


HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


karthik Posted on 2012-12-26 05:25:09.0Z
Sender: 7da.50da88e5.1804289383@sybase.com
From: Karthik
Newsgroups: sybase.public.ase.general
Subject: Re: max_rows_per_page
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50da8a35.885.1681692777@sybase.com>
References: <50d8a490$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 25 Dec 2012 21:25:09 -0800
X-Trace: forums-1-dub 1356499509 172.20.134.41 (25 Dec 2012 21:25:09 -0800)
X-Original-Trace: 25 Dec 2012 21:25:09 -0800, 172.20.134.41
Lines: 91
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31602
Article PK: 1158604

yes. After running "reorg rebuild", I am able to see the
effect. I can see 2 pages now.

As the locking schema is "ALL PAGE" , I thought reorg won't
help me. :)

what is the effect of max_rows_per_page = 0 vs
max_rows_per_page = 80 ?

what is the effect of fill_factor = 0 vs fill_factor = 80 ?

> On 24-Dec-2012 10:40, Karthik wrote:
> > Hi,
> >
> > There is a table with 85 rows along with the below
> > properties.
> >
> > fill factor = 0
> > max_rows_per_page = 0
> > Locking Schema : All Page
> > Index : Clustered index on monotonically increasing
> > column (No Composite index)
> >
> > I just changed the attributes as below
> >
> > sp_chgattribute sf, "max_rows_per_page", 80
> >
> > sp_chgattribute sf, "fillfactor", 80
> >
> > When I execute sp_help sf, I can see 3 Datapages for 85
> > rows eventhough I changed the attribute
> > "max_rows_per_page" to 80.
> > Technically it should be 2 right. why the new change is
> > not reflecting ?
> >
> > Ihave also ran "update statistics sf" to see is there
> > any change. But No Luck.
> >
> > what i am missing here?
> >
>
> 1:
> For a table that already contains rows, these rows are not
> affected when you change max_rows_per_page. Only when new
> rows are inserted (or updated) will the new setting start
> to take effect. Pages that have more than
> max_rows_per_page rows will not have their rowcount
> automatically reduced to max_rows_per_page, this only
> happens when you delete rows, do deferred updates or when
> you run reorg rebuild.
>
> 2:
> max_rows_per_page defines a *maximum* #rows per page.
> Theoretically it is possible that you have a very
> fragmented table with only one row per data page so you'd
> have 85 pages and 85 rows. That still satisfies
> max_rows_per_page=80.
>
> 3:
> update statistics has nothing to do with
> max_rows_per_page.
>
> 4:
> if you want to enforce the new max_rows_per_page setting,
> run 'reorg rebuild sf'
>
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide"
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------


Rob V Posted on 2012-12-26 11:04:54.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: max_rows_per_page
References: <50d8a490$1@forums-1-dub> <50da8a35.885.1681692777@sybase.com>
In-Reply-To: <50da8a35.885.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50dad9d6$1@forums-1-dub>
Date: 26 Dec 2012 03:04:54 -0800
X-Trace: forums-1-dub 1356519894 172.20.134.152 (26 Dec 2012 03:04:54 -0800)
X-Original-Trace: 26 Dec 2012 03:04:54 -0800, vip152.sybase.com
Lines: 99
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31607
Article PK: 1158611

You can find these answers in the documentation at sybooks.sybase.com.

R.

On 26-Dec-2012 06:25, Karthik wrote:
> yes. After running "reorg rebuild", I am able to see the
> effect. I can see 2 pages now.
>
> As the locking schema is "ALL PAGE" , I thought reorg won't
> help me. :)
>
> what is the effect of max_rows_per_page = 0 vs
> max_rows_per_page = 80 ?
>
> what is the effect of fill_factor = 0 vs fill_factor = 80 ?
>
>
>
>
>> On 24-Dec-2012 10:40, Karthik wrote:
>>> Hi,
>>>
>>> There is a table with 85 rows along with the below
>>> properties.
>>>
>>> fill factor = 0
>>> max_rows_per_page = 0
>>> Locking Schema : All Page
>>> Index : Clustered index on monotonically increasing
>>> column (No Composite index)
>>>
>>> I just changed the attributes as below
>>>
>>> sp_chgattribute sf, "max_rows_per_page", 80
>>>
>>> sp_chgattribute sf, "fillfactor", 80
>>>
>>> When I execute sp_help sf, I can see 3 Datapages for 85
>>> rows eventhough I changed the attribute
>>> "max_rows_per_page" to 80.
>>> Technically it should be 2 right. why the new change is
>>> not reflecting ?
>>>
>>> Ihave also ran "update statistics sf" to see is there
>>> any change. But No Luck.
>>>
>>> what i am missing here?
>>>
>>
>> 1:
>> For a table that already contains rows, these rows are not
>> affected when you change max_rows_per_page. Only when new
>> rows are inserted (or updated) will the new setting start
>> to take effect. Pages that have more than
>> max_rows_per_page rows will not have their rowcount
>> automatically reduced to max_rows_per_page, this only
>> happens when you delete rows, do deferred updates or when
>> you run reorg rebuild.
>>
>> 2:
>> max_rows_per_page defines a *maximum* #rows per page.
>> Theoretically it is possible that you have a very
>> fragmented table with only one row per data page so you'd
>> have 85 pages and 85 rows. That still satisfies
>> max_rows_per_page=80.
>>
>> 3:
>> update statistics has nothing to do with
>> max_rows_per_page.
>>
>> 4:
>> if you want to enforce the new max_rows_per_page setting,
>> run 'reorg rebuild sf'
>>
>>
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Professional DBA for Sybase ASE, IQ, Replication
>> Server
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
>> ASE" "The Complete Sybase IQ Quick Reference Guide"
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference
>> Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------