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.

Scan Count + OR Strategy

2 posts in General Discussion Last posting was on 2013-01-22 18:18:08.0Z
Karthik M Posted on 2013-01-22 06:33:56.0Z
Sender: 7a8e.50fe2b9a.1804289383@sybase.com
From: Karthik M
Newsgroups: sybase.public.ase.general
Subject: Scan Count + OR Strategy
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50fe32d4.7dae.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 21 Jan 2013 22:33:56 -0800
X-Trace: forums-1-dub 1358836436 172.20.134.41 (21 Jan 2013 22:33:56 -0800)
X-Original-Trace: 21 Jan 2013 22:33:56 -0800, 172.20.134.41
Lines: 70
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31667
Article PK: 1159210

All,

I found some wierd thing today when i ran the below two
version of queries.

select * from sub_fund
where sub_fund_id in (1,2,3,4,5,6,7,8,9,10)

scan count = 10

select * from sub_fund
where sub_fund_id in (1,2,3,4,5,6,7,8,9,10,11)

scan count = 1

Table has 118 records.

Adaptive Server Enterprise/15.5/EBF 19397 SMP
ESD#5/P/ia64/HP-UX B.11.23/asear155/2568/64-bit/FBO/Fri Dec
9 02:34:21 2011


index_name
sub_fund_13211047661

index_keys
sub_fund_id

index_description
clustered, unique


index_max_rows_per_page
0

index_fillfactor
0

index_reservepagegap
0


index_created
Dec 27 2012 12:18PM

index_local
Global Index

partition_name
sub_fund_1321104766

partition_id
1321104766


pages
2


row_count
118


segment
default

create_date
Dec 27 2012 12:18PM

why the scan count vary from 10 to 1 ?


Sherlock, Kevin [TeamSybase] Posted on 2013-01-22 18:18:08.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <50fe32d4.7dae.1681692777@sybase.com>
Subject: Re: Scan Count + OR Strategy
Lines: 76
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6157
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50fed7e0$1@forums-1-dub>
Date: 22 Jan 2013 10:18:08 -0800
X-Trace: forums-1-dub 1358878688 172.20.134.152 (22 Jan 2013 10:18:08 -0800)
X-Original-Trace: 22 Jan 2013 10:18:08 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31669
Article PK: 1159236

most likely due to the query plan chosen for each statement. Go ahead and
post the showplan output for both, and that will probably explain it.

<Karthik M> wrote in message news:50fe32d4.7dae.1681692777@sybase.com...
> All,
>
> I found some wierd thing today when i ran the below two
> version of queries.
>
> select * from sub_fund
> where sub_fund_id in (1,2,3,4,5,6,7,8,9,10)
>
> scan count = 10
>
> select * from sub_fund
> where sub_fund_id in (1,2,3,4,5,6,7,8,9,10,11)
>
> scan count = 1
>
> Table has 118 records.
>
> Adaptive Server Enterprise/15.5/EBF 19397 SMP
> ESD#5/P/ia64/HP-UX B.11.23/asear155/2568/64-bit/FBO/Fri Dec
> 9 02:34:21 2011
>
>
> index_name
> sub_fund_13211047661
>
> index_keys
> sub_fund_id
>
> index_description
> clustered, unique
>
>
> index_max_rows_per_page
> 0
>
> index_fillfactor
> 0
>
> index_reservepagegap
> 0
>
>
> index_created
> Dec 27 2012 12:18PM
>
> index_local
> Global Index
>
> partition_name
> sub_fund_1321104766
>
> partition_id
> 1321104766
>
>
> pages
> 2
>
>
> row_count
> 118
>
>
> segment
> default
>
> create_date
> Dec 27 2012 12:18PM
>
> why the scan count vary from 10 to 1 ?