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.

Use of index

3 posts in General Discussion Last posting was on 2012-03-14 10:54:29.0Z
RGS Posted on 2012-03-13 18:51:40.0Z
Sender: 45ee.4f5f95af.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Use of index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f5f973c.462a.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 13 Mar 2012 10:51:40 -0800
X-Trace: forums-1-dub 1331664700 172.20.134.41 (13 Mar 2012 10:51:40 -0800)
X-Original-Trace: 13 Mar 2012 10:51:40 -0800, 172.20.134.41
Lines: 23
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30987
Article PK: 73875

I have a stored procedure (ASE 15.5) with the sentence:

select *
from table1
where (field1 = @i_field1 or @i_field1 is null)

table1 has a index on field1 field. @i_field1 is a null
parameter (e.d. is optional)

When I execute the SP with value for @i_field1, ASE don't
use the index, it apply a TABLE SCAN

I have to chage the SP in the form?:

if @i_field1 is null
select *
from table1
else
select *
from table1
where field1 = @i_field1

Thanks!


hy Posted on 2012-03-14 03:23:44.0Z
From: "hy" <nospam_harrylhy@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4f5f973c.462a.1681692777@sybase.com>
In-Reply-To: <4f5f973c.462a.1681692777@sybase.com>
Subject: Re: Use of index
Lines: 4
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="big5"; reply-type=original
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8117.416
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8117.416
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f600f40$1@forums-1-dub>
Date: 13 Mar 2012 19:23:44 -0800
X-Trace: forums-1-dub 1331695424 10.22.241.152 (13 Mar 2012 19:23:44 -0800)
X-Original-Trace: 13 Mar 2012 19:23:44 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30988
Article PK: 73882

Have you try to specify the index in select syntax explicitly ?

select *
from table1 ( index idx_name )
where (field1 = @i_field1 or @i_field1 is null)



"RGS" 礎b繞l瞼籀簣i繞K瞻繙簧e瞼D礎簧 4f5f973c.462a.1681692777@sybase.com 瞻瞻翹繞翹g...

> I have a stored procedure (ASE 15.5) with the sentence:
>
> select *
> from table1
> where (field1 = @i_field1 or @i_field1 is null)
>
> table1 has a index on field1 field. @i_field1 is a null
> parameter (e.d. is optional)
>
> When I execute the SP with value for @i_field1, ASE don't
> use the index, it apply a TABLE SCAN
>
> I have to chage the SP in the form?:
>
> if @i_field1 is null
> select *
> from table1
> else
> select *
> from table1
> where field1 = @i_field1
>
> Thanks!


peta62 Posted on 2012-03-14 10:54:29.0Z
From: peta62 <no@mail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Use of index
References: <4f5f973c.462a.1681692777@sybase.com> <4f600f40$1@forums-1-dub>
In-Reply-To: <4f600f40$1@forums-1-dub>
Content-Type: text/plain; charset=Big5
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f6078e5@forums-1-dub>
Date: 14 Mar 2012 02:54:29 -0800
X-Trace: forums-1-dub 1331722469 10.22.241.152 (14 Mar 2012 02:54:29 -0800)
X-Original-Trace: 14 Mar 2012 02:54:29 -0800, vip152.sybase.com
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30990
Article PK: 73877

Hello,
I would strongly discourage forcing index where is a natural way of
using it like RGS shows.
Just my opinion,
Peter

On 3/14/2012 4:23 AM, hy wrote:
> Have you try to specify the index in select syntax explicitly ?
>
> select *
> from table1 ( index idx_name )
> where (field1 = @i_field1 or @i_field1 is null)
>
>
>
> "RGS" 礎b繞l瞼籀簣i繞K瞻繙簧e瞼D礎簧 4f5f973c.462a.1681692777@sybase.com 瞻瞻翹繞翹g...
>> I have a stored procedure (ASE 15.5) with the sentence:
>>
>> select *
>> from table1
>> where (field1 = @i_field1 or @i_field1 is null)
>>
>> table1 has a index on field1 field. @i_field1 is a null
>> parameter (e.d. is optional)
>>
>> When I execute the SP with value for @i_field1, ASE don't
>> use the index, it apply a TABLE SCAN
>>
>> I have to chage the SP in the form?:
>>
>> if @i_field1 is null
>> select *
>> from table1
>> else
>> select *
>> from table1
>> where field1 = @i_field1
>>
>> Thanks!
>