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.

How to force use of index

5 posts in General Discussion Last posting was on 2005-02-14 21:26:21.0Z
Frank Vestjens Posted on 2005-02-10 16:07:33.0Z
From: "Frank Vestjens" <FVestjens@InforIT.nl>
Newsgroups: ianywhere.public.general
Subject: How to force use of index
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
NNTP-Posting-Host: 62.58.186.102
X-Original-NNTP-Posting-Host: 62.58.186.102
Message-ID: <420b86c5@forums-1-dub>
Date: 10 Feb 2005 08:07:33 -0800
X-Trace: forums-1-dub 1108051653 62.58.186.102 (10 Feb 2005 08:07:33 -0800)
X-Original-Trace: 10 Feb 2005 08:07:33 -0800, 62.58.186.102
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4125
Article PK: 8011

I'm running SQLAnywhere 9.00.02 build 2451 and I've got a query that's slow.

I have added an index on the fields Plant, Status and Identifier (primary
key)
The query is ordered by Identifier.
In the where clause a filter is done on Plant and Status.
How can I force the query to use this index. If I change the order by to
Plant, Status, Identifier, then the query uses the index and is fast.
But I've to get this query ordered by Identifier.

The query:

Select *
from Orders
where Plant in (1170, 1171, 1911) and (Status > 0 or Status = 0 and
TimeSlotDate is not null)
order by identifier

Does somebody hava a tip for me.

Thanks,

Frank Vestjens
InforIT bv


Reg Domaratzki (iAnywhere Solutions) Posted on 2005-02-10 16:21:33.0Z
From: "Reg Domaratzki \(iAnywhere Solutions\)" <Spam_bad_rdomarat@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <420b86c5@forums-1-dub>
Subject: Re: How to force use of index
Lines: 47
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Original-NNTP-Posting-Host: rdomarat-pc.sybase.com
Message-ID: <420b8a09$1@forums-2-dub>
X-Original-Trace: 10 Feb 2005 08:21:29 -0800, rdomarat-pc.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 10 Feb 2005 08:21:30 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 10 Feb 2005 08:21:33 -0800
X-Trace: forums-1-dub 1108052493 10.22.108.75 (10 Feb 2005 08:21:33 -0800)
X-Original-Trace: 10 Feb 2005 08:21:33 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4126
Article PK: 8006

There currently isn't an index that can be used, since the only index that
includes the Identifier column is a multi-column index, where Identifier is
NOT the first column in the index. I think if you create a separate index
on the Identifier column, the optimizer will use it.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Frank Vestjens" <FVestjens@InforIT.nl> wrote in message
news:420b86c5@forums-1-dub...
> I'm running SQLAnywhere 9.00.02 build 2451 and I've got a query that's
slow.
>
> I have added an index on the fields Plant, Status and Identifier (primary
> key)
> The query is ordered by Identifier.
> In the where clause a filter is done on Plant and Status.
> How can I force the query to use this index. If I change the order by to
> Plant, Status, Identifier, then the query uses the index and is fast.
> But I've to get this query ordered by Identifier.
>
> The query:
>
> Select *
> from Orders
> where Plant in (1170, 1171, 1911) and (Status > 0 or Status = 0 and
> TimeSlotDate is not null)
> order by identifier
>
> Does somebody hava a tip for me.
>
> Thanks,
>
> Frank Vestjens
> InforIT bv
>
>


Dmitriy Popov <none Posted on 2005-02-14 20:32:49.0Z
Newsgroups: ianywhere.public.general
Subject: Re: How to force use of index
From: Dmitriy Popov <none@none.>
References: <420b86c5@forums-1-dub> <420b8a09$1@forums-2-dub>
Organization: Tenmast
User-Agent: Xnews/5.04.25
X-Original-NNTP-Posting-Host: 216.248.61.21
Message-ID: <42110aee@forums-2-dub>
X-Original-Trace: 14 Feb 2005 12:32:46 -0800, 216.248.61.21
Lines: 15
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 14 Feb 2005 12:32:48 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 14 Feb 2005 12:32:49 -0800
X-Trace: forums-1-dub 1108413169 10.22.108.75 (14 Feb 2005 12:32:49 -0800)
X-Original-Trace: 14 Feb 2005 12:32:49 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4135
Article PK: 8016

Why can't it use the index to order?

The first two fields can be used for filtering, and when it's done
filtering all that's left is Identifier, which it can then use for sorting.

"Reg Domaratzki \(iAnywhere Solutions\)"

<Spam_bad_rdomarat@ianywhere.com> wrote in news:420b8a09$1@forums-2-dub:

> There currently isn't an index that can be used, since the only index
> that includes the Identifier column is a multi-column index, where
> Identifier is NOT the first column in the index. I think if you
> create a separate index on the Identifier column, the optimizer will
> use it.
>


Glenn Paulley Posted on 2005-02-14 21:26:21.0Z
Newsgroups: ianywhere.public.general
Subject: Re: How to force use of index
From: Glenn Paulley <paulley@ianywhere.com>
References: <420b86c5@forums-1-dub> <420b8a09$1@forums-2-dub> <42110aee@forums-2-dub>
Organization: iAnywhere Solutions
Message-ID: <Xns95FDA73A8CA15paulleyianywherecom@10.22.241.106>
User-Agent: Xnews/5.04.25
NNTP-Posting-Host: paulley-t41.sybase.com
X-Original-NNTP-Posting-Host: paulley-t41.sybase.com
Date: 14 Feb 2005 13:26:21 -0800
X-Trace: forums-1-dub 1108416381 10.25.99.149 (14 Feb 2005 13:26:21 -0800)
X-Original-Trace: 14 Feb 2005 13:26:21 -0800, paulley-t41.sybase.com
Lines: 41
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4136
Article PK: 8015

It isn't possible, in a single scan of the index, to guarantee that the
rows matching the predicate on Plant (assuming Plant is the first column
of the index) will also be ordered by Identifier, so the server has to
perform a final sort.

Glenn

Dmitriy Popov <none@none.> wrote in news:42110aee@forums-2-dub:

> Why can't it use the index to order?
>
> The first two fields can be used for filtering, and when it's done
> filtering all that's left is Identifier, which it can then use for
> sorting.
>
> "Reg Domaratzki \(iAnywhere Solutions\)"
> <Spam_bad_rdomarat@ianywhere.com> wrote in
> news:420b8a09$1@forums-2-dub:
>
>> There currently isn't an index that can be used, since the only index
>> that includes the Identifier column is a multi-column index, where
>> Identifier is NOT the first column in the index. I think if you
>> create a separate index on the Identifier column, the optimizer will
>> use it.
>>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288


Glenn Paulley Posted on 2005-02-10 18:44:33.0Z
Newsgroups: ianywhere.public.general
Subject: Re: How to force use of index
From: Glenn Paulley <paulley@ianywhere.com>
References: <420b86c5@forums-1-dub>
Organization: iAnywhere Solutions
Message-ID: <Xns95F98BC8EDB24paulleyianywherecom@10.22.241.106>
User-Agent: Xnews/5.04.25
X-Original-NNTP-Posting-Host: paulley-t41.sybase.com
X-Original-Trace: 10 Feb 2005 10:44:29 -0800, paulley-t41.sybase.com
Lines: 53
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 10 Feb 2005 10:44:29 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 10 Feb 2005 10:44:33 -0800
X-Trace: forums-1-dub 1108061073 10.22.108.75 (10 Feb 2005 10:44:33 -0800)
X-Original-Trace: 10 Feb 2005 10:44:33 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4127
Article PK: 8009

I would recommend executing the ASA Index Consultant, out of Sybase
Central, which will recommend indexes for an entire workload of your
application. You can also execute the index consultant for a specific
query in DBISQL, by using the index consultant capabilities added to
DBISQL in the 9.x releases.

Glenn

"Frank Vestjens" <FVestjens@InforIT.nl> wrote in
news:420b86c5@forums-1-dub:

> I'm running SQLAnywhere 9.00.02 build 2451 and I've got a query that's
> slow.
>
> I have added an index on the fields Plant, Status and Identifier
> (primary key)
> The query is ordered by Identifier.
> In the where clause a filter is done on Plant and Status.
> How can I force the query to use this index. If I change the order by
> to Plant, Status, Identifier, then the query uses the index and is
> fast. But I've to get this query ordered by Identifier.
>
> The query:
>
> Select *
> from Orders
> where Plant in (1170, 1171, 1911) and (Status > 0 or Status = 0 and
> TimeSlotDate is not null)
> order by identifier
>
> Does somebody hava a tip for me.
>
> Thanks,
>
> Frank Vestjens
> InforIT bv
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288