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.

sp_iqrebuildindex and sp_iqcardinality_analysis

6 posts in General Discussion Last posting was on 2013-01-08 22:30:05.0Z
Jose M. Torres Posted on 2012-12-18 14:48:41.0Z
Sender: 2d61.50d08035.1804289383@sybase.com
From: Jose M. Torres
Newsgroups: sybase.public.iq
Subject: sp_iqrebuildindex and sp_iqcardinality_analysis
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50d08249.2da4.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 18 Dec 2012 06:48:41 -0800
X-Trace: forums-1-dub 1355842121 172.20.134.41 (18 Dec 2012 06:48:41 -0800)
X-Original-Trace: 18 Dec 2012 06:48:41 -0800, 172.20.134.41
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4572
Article PK: 1158392

Hi,
Running sp_iq_cardinality_analysis on a table gives :
--Column 'xxx' has no One Byte FP index and cardinality is
less than 256. --One Byte FP index can be created. --Call
the stored procedure: sp_iqrebuildindex ''<table>'',''column
<col> 255'''.

Then running sp_iqrebuildindex does not do anything
regarding creating a Onebyte FP
The column involved is a varchar(1) with two values ( 'Y' /
'N')
So:
- Does sp_iqrebuildindex creates a new FPindex( no as far as
I know)? Then why does sp_iqcardinality_analysis recommends
running sp_iqrebuildindex
- Does sp_iqrebuildindex physically recreates an FP index (
as far as I know just recreating the column/table will work)

thank you
regards
JM


Abhijit Jadhav Posted on 2012-12-20 09:32:35.0Z
Sender: 4ed2.50d2da01.1804289383@sybase.com
From: Abhijit Jadhav
Newsgroups: sybase.public.iq
Subject: Re: sp_iqrebuildindex and sp_iqcardinality_analysis
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50d2db33.4f38.1681692777@sybase.com>
References: <50d08249.2da4.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 20 Dec 2012 01:32:35 -0800
X-Trace: forums-1-dub 1355995955 172.20.134.41 (20 Dec 2012 01:32:35 -0800)
X-Original-Trace: 20 Dec 2012 01:32:35 -0800, 172.20.134.41
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4573
Article PK: 1158511

JM,

sp_iqrebuildindex does rebuild the FP indexes. if run
sp_iqrowdensity 'table TABLE_NAME'; will provide you
existing FP index type. You have valid point here as
sp_iq_cardinality_analysis should not report this
recommendation for anything which is already of 1 Byte.
There is no difference in storage for Flat FP for char(1)
and One Byte FP. Both requires same storage, so you will
always see Flat FP index after sp_iqrebuild index on
anything on 1 byte column.

Hope this helps.

Regards,
Abhijit

> Hi,
> Running sp_iq_cardinality_analysis on a table gives :
> --Column 'xxx' has no One Byte FP index and cardinality is
> less than 256. --One Byte FP index can be created. --Call
> the stored procedure: sp_iqrebuildindex ''<table>''
> ,''column <col> 255'''.
>
> Then running sp_iqrebuildindex does not do anything
> regarding creating a Onebyte FP
> The column involved is a varchar(1) with two values ( 'Y'
> / 'N')
> So:
> - Does sp_iqrebuildindex creates a new FPindex( no as far
> as I know)? Then why does sp_iqcardinality_analysis
> recommends running sp_iqrebuildindex
> - Does sp_iqrebuildindex physically recreates an FP index
> ( as far as I know just recreating the column/table will
> work)
>
> thank you
> regards
> JM


Jose M. Torres Posted on 2012-12-20 21:09:09.0Z
Sender: 1024.50d37d71.1804289383@sybase.com
From: Jose M. Torres
Newsgroups: sybase.public.iq
Subject: Re: sp_iqrebuildindex and sp_iqcardinality_analysis
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50d37e75.112c.1681692777@sybase.com>
References: <50d2db33.4f38.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 20 Dec 2012 13:09:09 -0800
X-Trace: forums-1-dub 1356037749 172.20.134.41 (20 Dec 2012 13:09:09 -0800)
X-Original-Trace: 20 Dec 2012 13:09:09 -0800, 172.20.134.41
Lines: 47
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4577
Article PK: 1158542

Abhijit,
Thank you. So we can say that running sp_rebuildindex
should physycally recreate any FP index?..Usually droping7
recreating the column works for byte storage..

Regards
JM

> JM,
>
> sp_iqrebuildindex does rebuild the FP indexes. if run
> sp_iqrowdensity 'table TABLE_NAME'; will provide you
> existing FP index type. You have valid point here as
> sp_iq_cardinality_analysis should not report this
> recommendation for anything which is already of 1 Byte.
> There is no difference in storage for Flat FP for char(1)
> and One Byte FP. Both requires same storage, so you will
> always see Flat FP index after sp_iqrebuild index on
> anything on 1 byte column.
>
> Hope this helps.
>
> Regards,
> Abhijit
>
>
> > Hi,
> > Running sp_iq_cardinality_analysis on a table gives :
> > --Column 'xxx' has no One Byte FP index and cardinality
> > is less than 256. --One Byte FP index can be created.
> > --Call the stored procedure: sp_iqrebuildindex
> > ''<table>'' ,''column <col> 255'''.
> >
> > Then running sp_iqrebuildindex does not do anything
> > regarding creating a Onebyte FP
> > The column involved is a varchar(1) with two values (
> > 'Y' / 'N')
> > So:
> > - Does sp_iqrebuildindex creates a new FPindex( no as
> > far as I know)? Then why does sp_iqcardinality_analysis
> > recommends running sp_iqrebuildindex
> > - Does sp_iqrebuildindex physically recreates an FP
> > index ( as far as I know just recreating the
> > column/table will work)
> >
> > thank you
> > regards
> > JM


www Posted on 2013-01-03 16:45:59.0Z
Sender: 3b14.50e5b57a.1804289383@sybase.com
From: www
Newsgroups: sybase.public.iq
Subject: Re: sp_iqrebuildindex and sp_iqcardinality_analysis
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50e5b5c7.3b39.1681692777@sybase.com>
References: <50d37e75.112c.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 3 Jan 2013 08:45:59 -0800
X-Trace: forums-1-dub 1357231559 172.20.134.41 (3 Jan 2013 08:45:59 -0800)
X-Original-Trace: 3 Jan 2013 08:45:59 -0800, 172.20.134.41
Lines: 2
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4583
Article PK: 1158713

how can I rebuild a table the all index?


Abhijit Jadhav Posted on 2013-01-08 22:28:08.0Z
Sender: 6813.50ec7b2b.1804289383@sybase.com
From: Abhijit Jadhav
Newsgroups: sybase.public.iq
Subject: Re: sp_iqrebuildindex and sp_iqcardinality_analysis
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50ec9d78.731b.1681692777@sybase.com>
References: <50e5b5c7.3b39.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 8 Jan 2013 14:28:08 -0800
X-Trace: forums-1-dub 1357684088 172.20.134.41 (8 Jan 2013 14:28:08 -0800)
X-Original-Trace: 8 Jan 2013 14:28:08 -0800, 172.20.134.41
Lines: 5
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4586
Article PK: 1158826

Not in single command. you need to script it out.

Rgds,
Abhijit
> how can I rebuild a table the all index?


Abhijit Jadhav Posted on 2013-01-08 22:30:05.0Z
Sender: 6813.50ec7b2b.1804289383@sybase.com
From: Abhijit Jadhav
Newsgroups: sybase.public.iq
Subject: Re: sp_iqrebuildindex and sp_iqcardinality_analysis
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50ec9ded.7343.1681692777@sybase.com>
References: <50d37e75.112c.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 8 Jan 2013 14:30:05 -0800
X-Trace: forums-1-dub 1357684205 172.20.134.41 (8 Jan 2013 14:30:05 -0800)
X-Original-Trace: 8 Jan 2013 14:30:05 -0800, 172.20.134.41
Lines: 54
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4587
Article PK: 1158829

What do you mean by dropping and recreating the column works
for byte storage?

example?

Reagrds,
Abhijit

> Abhijit,
> Thank you. So we can say that running sp_rebuildindex
> should physycally recreate any FP index?..Usually
> droping7 recreating the column works for byte storage..
>
> Regards
> JM
> > JM,
> >
> > sp_iqrebuildindex does rebuild the FP indexes. if run
> > sp_iqrowdensity 'table TABLE_NAME'; will provide you
> > existing FP index type. You have valid point here as
> > sp_iq_cardinality_analysis should not report this
> > recommendation for anything which is already of 1 Byte.
> > There is no difference in storage for Flat FP for
> > char(1) and One Byte FP. Both requires same storage, so
> > you will always see Flat FP index after sp_iqrebuild
> > index on anything on 1 byte column.
> >
> > Hope this helps.
> >
> > Regards,
> > Abhijit
> >
> >
> > > Hi,
> > > Running sp_iq_cardinality_analysis on a table gives :
> > > --Column 'xxx' has no One Byte FP index and
> > > cardinality is less than 256. --One Byte FP index can
> > > be created. --Call the stored procedure:
> > > sp_iqrebuildindex ''<table>'' ,''column <col> 255'''.
> > >
> > > Then running sp_iqrebuildindex does not do anything
> > > regarding creating a Onebyte FP
> > > The column involved is a varchar(1) with two values (
> > > 'Y' / 'N')
> > > So:
> > > - Does sp_iqrebuildindex creates a new FPindex( no as
> > > far as I know)? Then why does
> > > sp_iqcardinality_analysis recommends running
> > > sp_iqrebuildindex - Does sp_iqrebuildindex physically
> > > recreates an FP index ( as far as I know just
> > > recreating the column/table will work)
> > >
> > > thank you
> > > regards
> > > JM