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.

Positioning by key

6 posts in Performance and Tuning Last posting was on 2012-07-11 02:20:59.0Z
Raj Posted on 2012-07-07 11:02:20.0Z
Sender: 2e8a.4ff81641.1804289383@sybase.com
From: Raj
Newsgroups: sybase.public.ase.performance+tuning
Subject: Positioning by key
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ff8173c.2fd0.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 7 Jul 2012 04:02:20 -0700
X-Trace: forums-1-dub 1341658940 172.20.134.41 (7 Jul 2012 04:02:20 -0700)
X-Original-Trace: 7 Jul 2012 04:02:20 -0700, 172.20.134.41
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13177
Article PK: 91668

Hi,

When does query plan show "positioning by key" or
"positioning at index start".
what do they mean ?
What changes should we make to change the plan to
"positioning by key" from "positioning at index start" ?
(Sybase 15.0.3ESD#1)

Thanks in advance,
Raj


Rob V Posted on 2012-07-07 18:06:43.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:13.0) Gecko/20120614 Thunderbird/13.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Positioning by key
References: <4ff8173c.2fd0.1681692777@sybase.com>
In-Reply-To: <4ff8173c.2fd0.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ff87ab3$1@forums-1-dub>
Date: 7 Jul 2012 11:06:43 -0700
X-Trace: forums-1-dub 1341684403 172.20.134.152 (7 Jul 2012 11:06:43 -0700)
X-Original-Trace: 7 Jul 2012 11:06:43 -0700, vip152.sybase.com
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13178
Article PK: 91667


On 07-Jul-2012 13:02, Raj wrote:
> Hi,
>
> When does query plan show "positioning by key" or
> "positioning at index start".
> what do they mean ?
> What changes should we make to change the plan to
> "positioning by key" from "positioning at index start" ?
> (Sybase 15.0.3ESD#1)
>
> Thanks in advance,
> Raj
>

See the P&T documentation: http://tinyurl.com/cy6lzlf
'Positioning by key' is much better in principle than "at index start".
The latter means that the search argument is not selective enough to
directly go to the position in the index where the searched value is
located (=positioning by key), and it has to scan the entire index in
order to find the value being searched for.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Raj Posted on 2012-07-08 01:37:22.0Z
Sender: 4884.4ff8e0e3.1804289383@sybase.com
From: Raj
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Positioning by key
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ff8e452.4901.1681692777@sybase.com>
References: <4ff87ab3$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 7 Jul 2012 18:37:22 -0700
X-Trace: forums-1-dub 1341711442 172.20.134.41 (7 Jul 2012 18:37:22 -0700)
X-Original-Trace: 7 Jul 2012 18:37:22 -0700, 172.20.134.41
Lines: 54
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13179
Article PK: 91673

Thanks Rob, But I couldn't find the answer for my last
question, to change the plan from "positioning at index
start" to "positioning by key" ?
Would you mind to help me in that ?

Thanks,
Raj

> On 07-Jul-2012 13:02, Raj wrote:
> > Hi,
> >
> > When does query plan show "positioning by key" or
> > "positioning at index start".
> > what do they mean ?
> > What changes should we make to change the plan to
> > "positioning by key" from "positioning at index start" ?
> > (Sybase 15.0.3ESD#1)
> >
> > Thanks in advance,
> > Raj
> >
>
> See the P&T documentation: http://tinyurl.com/cy6lzlf
> 'Positioning by key' is much better in principle than "at
> index start". The latter means that the search argument
> is not selective enough to directly go to the position in
> the index where the searched value is located
> (=positioning by key), and it has to scan the entire index
> in order to find the value being searched for.
>
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide"
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>
>


Rob V Posted on 2012-07-08 08:07:34.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:13.0) Gecko/20120614 Thunderbird/13.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Positioning by key
References: <4ff87ab3$1@forums-1-dub> <4ff8e452.4901.1681692777@sybase.com>
In-Reply-To: <4ff8e452.4901.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ff93fc6$1@forums-1-dub>
Date: 8 Jul 2012 01:07:34 -0700
X-Trace: forums-1-dub 1341734854 172.20.134.152 (8 Jul 2012 01:07:34 -0700)
X-Original-Trace: 8 Jul 2012 01:07:34 -0700, vip152.sybase.com
Lines: 89
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13180
Article PK: 91675

That's a strange thing to do, since you'll make things run slower.
Anyway, you can achieve that by making sure the index is not usable for
the query but it does contain the required columns. A convering index is
a good example.
So if you have a query "select a,b from t where c=123", make sure there
is an index on all three columns a,b,c, but column 'c' should *not* be
the first column in the index: if it is, the query will position by key.
Note that this assumes you'll hit only one row (or few) for c=123. If
you hit many rows, ASE may choose a table scan instead.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------

On 08-Jul-2012 03:37, Raj wrote:
> Thanks Rob, But I couldn't find the answer for my last
> question, to change the plan from "positioning at index
> start" to "positioning by key" ?
> Would you mind to help me in that ?
>
> Thanks,
> Raj
>
>> On 07-Jul-2012 13:02, Raj wrote:
>>> Hi,
>>>
>>> When does query plan show "positioning by key" or
>>> "positioning at index start".
>>> what do they mean ?
>>> What changes should we make to change the plan to
>>> "positioning by key" from "positioning at index start" ?
>>> (Sybase 15.0.3ESD#1)
>>>
>>> Thanks in advance,
>>> Raj
>>>
>>
>> See the P&T documentation: http://tinyurl.com/cy6lzlf
>> 'Positioning by key' is much better in principle than "at
>> index start". The latter means that the search argument
>> is not selective enough to directly go to the position in
>> the index where the searched value is located
>> (=positioning by key), and it has to scan the entire index
>> in order to find the value being searched for.
>>
>> --
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Professional DBA for Sybase ASE, IQ, Replication
>> Server
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
>> ASE" "The Complete Sybase IQ Quick Reference Guide"
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference
>> Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>
>>


Raj Posted on 2012-07-10 02:09:32.0Z
Sender: 4485.4ffb8eb6.1804289383@sybase.com
From: Raj
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Positioning by key
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ffb8edc.448d.1681692777@sybase.com>
References: <4ff93fc6$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 9 Jul 2012 19:09:32 -0700
X-Trace: forums-1-dub 1341886172 172.20.134.41 (9 Jul 2012 19:09:32 -0700)
X-Original-Trace: 9 Jul 2012 19:09:32 -0700, 172.20.134.41
Lines: 97
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13181
Article PK: 91686

Thanks Rob.

> That's a strange thing to do, since you'll make things run
> slower. Anyway, you can achieve that by making sure the
> index is not usable for the query but it does contain the
> required columns. A convering index is a good example.
> So if you have a query "select a,b from t where c=123",
> make sure there is an index on all three columns a,b,c,
> but column 'c' should *not* be the first column in the
> index: if it is, the query will position by key. Note that
> this assumes you'll hit only one row (or few) for c=123.
> If you hit many rows, ASE may choose a table scan
> instead.
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide"
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>
>
> On 08-Jul-2012 03:37, Raj wrote:
> > Thanks Rob, But I couldn't find the answer for my last
> > question, to change the plan from "positioning at index
> > start" to "positioning by key" ?
> > Would you mind to help me in that ?
> >
> > Thanks,
> > Raj
> >
> >> On 07-Jul-2012 13:02, Raj wrote:
> >>> Hi,
> >>>
> >>> When does query plan show "positioning by key" or
> >>> "positioning at index start".
> >>> what do they mean ?
> >>> What changes should we make to change the plan to
> >>> "positioning by key" from "positioning at index start"
> ? >>> (Sybase 15.0.3ESD#1)
> >>>
> >>> Thanks in advance,
> >>> Raj
> >>>
> >>
> >> See the P&T documentation: http://tinyurl.com/cy6lzlf
> >> 'Positioning by key' is much better in principle than
> "at >> index start". The latter means that the search
> argument >> is not selective enough to directly go to the
> position in >> the index where the searched value is
> located >> (=positioning by key), and it has to scan the
> entire index >> in order to find the value being searched
> for. >>
> >> --
> >> HTH,
> >>
> >> Rob V.
> >>
> ----------------------------------------------------------
> >> ------- Rob Verschoor >>
> >> Certified Professional DBA for Sybase ASE, IQ,
> Replication >> Server
> >>
> >> Author of Sybase books (order online at
> >> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> >> ASE" "The Complete Sybase IQ Quick Reference Guide"
> >> "The Complete Sybase ASE Quick Reference Guide"
> >> "The Complete Sybase Replication Server Quick Reference
> >> Guide"
> >>
> >> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> >> @rob_verschoor Sypron B.V., The Netherlands | Chamber
> of >> Commerce 27138666
> >>
> ----------------------------------------------------------
> >> ------- >>
> >>
>
>
>
>
>


NJS Posted on 2012-07-11 02:20:59.0Z
From: NJS <NJS@optonline.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Positioning by key
References: <4ff93fc6$1@forums-1-dub> <4ffb8edc.448d.1681692777@sybase.com>
In-Reply-To: <4ffb8edc.448d.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ffce30b$1@forums-1-dub>
Date: 10 Jul 2012 19:20:59 -0700
X-Trace: forums-1-dub 1341973259 172.20.134.152 (10 Jul 2012 19:20:59 -0700)
X-Original-Trace: 10 Jul 2012 19:20:59 -0700, vip152.sybase.com
Lines: 117
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13186
Article PK: 91688

Just to augment what Rob stated:

this is one question many new Sybase DBAs never get.. that you always
want to see 'position by index key' and never want to see 'position at
index start'.

In MS-SQL speak its index scan vs index seek.

If you are able to tune and read query-plans you will make a fine DBA.

I remember in the current 100TB and complex MPP (SQL of course) space,
we get to tune thousands of machine generated SQL, and knowing what
push-down joins, range-restrictive scans etc.. make a database tuning a
fun task!

On 7/9/2012 10:09 PM, Raj wrote:
> Thanks Rob.
>
>> That's a strange thing to do, since you'll make things run
>> slower. Anyway, you can achieve that by making sure the
>> index is not usable for the query but it does contain the
>> required columns. A convering index is a good example.
>> So if you have a query "select a,b from t where c=123",
>> make sure there is an index on all three columns a,b,c,
>> but column 'c' should *not* be the first column in the
>> index: if it is, the query will position by key. Note that
>> this assumes you'll hit only one row (or few) for c=123.
>> If you hit many rows, ASE may choose a table scan
>> instead.
>>
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Professional DBA for Sybase ASE, IQ, Replication
>> Server
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" "The Complete Sybase IQ Quick Reference Guide"
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference
>> Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>
>>
>> On 08-Jul-2012 03:37, Raj wrote:
>>> Thanks Rob, But I couldn't find the answer for my last
>>> question, to change the plan from "positioning at index
>>> start" to "positioning by key" ?
>>> Would you mind to help me in that ?
>>>
>>> Thanks,
>>> Raj
>>>
>>>> On 07-Jul-2012 13:02, Raj wrote:
>>>>> Hi,
>>>>>
>>>>> When does query plan show "positioning by key" or
>>>>> "positioning at index start".
>>>>> what do they mean ?
>>>>> What changes should we make to change the plan to
>>>>> "positioning by key" from "positioning at index start"
>> ?>>> (Sybase 15.0.3ESD#1)
>>>>>
>>>>> Thanks in advance,
>>>>> Raj
>>>>>
>>>>
>>>> See the P&T documentation: http://tinyurl.com/cy6lzlf
>>>> 'Positioning by key' is much better in principle than
>> "at>> index start". The latter means that the search
>> argument>> is not selective enough to directly go to the
>> position in>> the index where the searched value is
>> located>> (=positioning by key), and it has to scan the
>> entire index>> in order to find the value being searched
>> for.>>
>>>> --
>>>> HTH,
>>>>
>>>> Rob V.
>>>>
>> ----------------------------------------------------------
>>>> ------- Rob Verschoor>>
>>>> Certified Professional DBA for Sybase ASE, IQ,
>> Replication>> Server
>>>>
>>>> Author of Sybase books (order online at
>>>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>>>> ASE" "The Complete Sybase IQ Quick Reference Guide"
>>>> "The Complete Sybase ASE Quick Reference Guide"
>>>> "The Complete Sybase Replication Server Quick Reference
>>>> Guide"
>>>>
>>>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>>>> @rob_verschoor Sypron B.V., The Netherlands | Chamber
>> of>> Commerce 27138666
>>>>
>> ----------------------------------------------------------
>>>> ------->>
>>>>
>>
>>
>>
>>
>>