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.

update index statistics table_name

2 posts in General Discussion Last posting was on 2009-09-09 20:14:42.0Z
Kaboum Posted on 2009-09-09 19:19:13.0Z
From: Kaboum <yvesmiron@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: update index statistics table_name
Date: Wed, 9 Sep 2009 12:19:13 -0700 (PDT)
Organization: http://groups.google.com
Lines: 21
Message-ID: <716b332c-64fc-403f-9b6d-7fb343231f7f@e12g2000yqi.googlegroups.com>
NNTP-Posting-Host: 64.86.141.133
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1252523953 17284 127.0.0.1 (9 Sep 2009 19:19:13 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 9 Sep 2009 19:19:13 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: e12g2000yqi.googlegroups.com; posting-host=64.86.141.133; posting-account=wFnfIwoAAAA77vcHGM_rC3YQpeOEmq8U
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; MS-RTC LM 8),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!e12g2000yqi.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28286
Article PK: 77530

ASE 12.5.4

Hi,

I am executing a update index statistics table_name on a table where
there is no index/primary key.
The table has 20 millions rows. I was under the impression that the
update index statistics table_name
won't do any statistics at all where there is no indexes, which seems
to be the case since there is no data inserted in sysstatisics table
for the specific table. But, strangely it takes 12 minutes for the
update index statistics to return and a lot of io is done ?

Anyone could explain waht is going on.

Thank you


Sherlock, Kevin [TeamSybase] Posted on 2009-09-09 20:14:42.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <716b332c-64fc-403f-9b6d-7fb343231f7f@e12g2000yqi.googlegroups.com>
Subject: Re: update index statistics table_name
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa80cb2$1@forums-1-dub>
Date: 9 Sep 2009 13:14:42 -0700
X-Trace: forums-1-dub 1252527282 10.22.241.152 (9 Sep 2009 13:14:42 -0700)
X-Original-Trace: 9 Sep 2009 13:14:42 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28287
Article PK: 77531

Yes, but "update index statistics table_name" involves collecting table (and
index if they exist) level statistics. These are kept in systabstats which
is what is updated in your case.

In your case, there are no _column_ level stats generated because there are
no indexes. However, there is the heap table itself which has statistics at
the table level (including CR counts, page counts, average row size, etc).

for example (from optdiag output):

Statistics for table: "mytable"

Data page count: 9345
Empty data page count: 0
Data row count: 100000.0000000000000000
Forwarded row count: 0.0000000000000000
Deleted row count: 0.0000000000000000
Data page CR count: 1169.0000000000000000
OAM + allocation page count: 42
First extent data pages: 0
Data row size: 177.7312400000000139
Pages in largest partition: 9345

"Kaboum" <yvesmiron@gmail.com> wrote in message
news:716b332c-64fc-403f-9b6d-7fb343231f7f@e12g2000yqi.googlegroups.com...
>
> ASE 12.5.4
>
> Hi,
>
> I am executing a update index statistics table_name on a table where
> there is no index/primary key.
> The table has 20 millions rows. I was under the impression that the
> update index statistics table_name
> won't do any statistics at all where there is no indexes, which seems
> to be the case since there is no data inserted in sysstatisics table
> for the specific table. But, strangely it takes 12 minutes for the
> update index statistics to return and a lot of io is done ?
>
> Anyone could explain waht is going on.
>
> Thank you
>
>
>
>