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.

dynamic index

5 posts in General Discussion Last posting was on 2009-05-26 13:15:53.0Z
vtpcnk Posted on 2009-05-26 10:38:00.0Z
Sender: 45ff.4a1bc5cf.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: dynamic index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a1bc688.461b.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 26 May 2009 03:38:00 -0700
X-Trace: forums-1-dub 1243334280 10.22.241.41 (26 May 2009 03:38:00 -0700)
X-Original-Trace: 26 May 2009 03:38:00 -0700, 10.22.241.41
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27786
Article PK: 77043

is this where multiple indexes are used to create a dynamic
index as the situation requires?

supposed to be used in "in" or "or" clause.

i can understand its use with the "or" clause where multiple
columns are used. for eg : where col1 = '1' and col2 = '2',
where a dynamic index integrating the two columns from
multiple indexes can be derived and thus help the query.

but in the "in" clause? for the same column? why should two
indexes be used for this?

appreciate the feedback.


"Mark A. Parsons" <iron_horse Posted on 2009-05-26 11:58:15.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: dynamic index
References: <4a1bc688.461b.1681692777@sybase.com>
In-Reply-To: <4a1bc688.461b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090523-0, 05/23/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a1bd957$2@forums-1-dub>
Date: 26 May 2009 04:58:15 -0700
X-Trace: forums-1-dub 1243339095 10.22.241.152 (26 May 2009 04:58:15 -0700)
X-Original-Trace: 26 May 2009 04:58:15 -0700, vip152.sybase.com
Lines: 36
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27790
Article PK: 77040

What is an 'in' clause?

Consider:

id in (1,15, 37)

Isn't this the same as:

id = 1
or id = 15
or id = 37

If the table has an index on the id column, the optimizer has the option of accessing the table 3 times using said index
(id = 1 : id = 15 : id = 37).

-------------

If this doesn't answer your question then please provide more details of the situation you're trying to address (eg,
sample query, sample query plan).

vtpcnk wrote:
> is this where multiple indexes are used to create a dynamic
> index as the situation requires?
>
> supposed to be used in "in" or "or" clause.
>
> i can understand its use with the "or" clause where multiple
> columns are used. for eg : where col1 = '1' and col2 = '2',
> where a dynamic index integrating the two columns from
> multiple indexes can be derived and thus help the query.
>
> but in the "in" clause? for the same column? why should two
> indexes be used for this?
>
> appreciate the feedback.


vtpcnk Posted on 2009-05-26 12:53:12.0Z
Sender: 4b42.4a1be5dd.1804289383@sybase.com
From: vtpcnk
Newsgroups: sybase.public.ase.general
Subject: Re: dynamic index
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a1be638.4b54.1681692777@sybase.com>
References: <4a1bd957$2@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 26 May 2009 05:53:12 -0700
X-Trace: forums-1-dub 1243342392 10.22.241.41 (26 May 2009 05:53:12 -0700)
X-Original-Trace: 26 May 2009 05:53:12 -0700, 10.22.241.41
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27792
Article PK: 77045

but what is the reference to a dynamic index below?

> What is an 'in' clause?
>
> Consider:
>
> id in (1,15, 37)
>
> Isn't this the same as:
>
> id = 1
> or id = 15
> or id = 37
>
> If the table has an index on the id column, the optimizer
> has the option of accessing the table 3 times using said
> index
> (id = 1 : id = 15 : id = 37).
>
> -------------
>
> If this doesn't answer your question then please provide
> more details of the situation you're trying to address (eg
> , sample query, sample query plan).
>
>
> vtpcnk wrote:
> > is this where multiple indexes are used to create a
> > dynamic index as the situation requires?
> >
> > supposed to be used in "in" or "or" clause.
> >
> > i can understand its use with the "or" clause where
> > multiple columns are used. for eg : where col1 = '1' and
> > col2 = '2', where a dynamic index integrating the two
> > columns from multiple indexes can be derived and thus
> > help the query.
> > but in the "in" clause? for the same column? why should
> > two indexes be used for this?
> >
> > appreciate the feedback.


"Mark A. Parsons" <iron_horse Posted on 2009-05-26 13:06:16.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: dynamic index
References: <4a1bd957$2@forums-1-dub> <4a1be638.4b54.1681692777@sybase.com>
In-Reply-To: <4a1be638.4b54.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090523-0, 05/23/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a1be948$1@forums-1-dub>
Date: 26 May 2009 06:06:16 -0700
X-Trace: forums-1-dub 1243343176 10.22.241.152 (26 May 2009 06:06:16 -0700)
X-Original-Trace: 26 May 2009 06:06:16 -0700, vip152.sybase.com
Lines: 51
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27793
Article PK: 77044

You're the one who brought up the term 'dynamic index'.

Yes, I'm aware of the term 'dynamic index' in ASE ... I'm just not sure what you're looking for ... a definition of
dynamic index (have you looked in the ASE manuals?) ... or why the compiler is choosing to use a dynamic index for a
particular query (what's the query and associated query plan?) ... or something else ... ??

vtpcnk wrote:
> but what is the reference to a dynamic index below?
>
>> What is an 'in' clause?
>>
>> Consider:
>>
>> id in (1,15, 37)
>>
>> Isn't this the same as:
>>
>> id = 1
>> or id = 15
>> or id = 37
>>
>> If the table has an index on the id column, the optimizer
>> has the option of accessing the table 3 times using said
>> index
>> (id = 1 : id = 15 : id = 37).
>>
>> -------------
>>
>> If this doesn't answer your question then please provide
>> more details of the situation you're trying to address (eg
>> , sample query, sample query plan).
>>
>>
>> vtpcnk wrote:
>>> is this where multiple indexes are used to create a
>>> dynamic index as the situation requires?
>>>
>>> supposed to be used in "in" or "or" clause.
>>>
>>> i can understand its use with the "or" clause where
>>> multiple columns are used. for eg : where col1 = '1' and
>>> col2 = '2', where a dynamic index integrating the two
>>> columns from multiple indexes can be derived and thus
>>> help the query.
>>> but in the "in" clause? for the same column? why should
>>> two indexes be used for this?
>>>
>>> appreciate the feedback.


"Mark A. Parsons" <iron_horse Posted on 2009-05-26 13:15:53.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: dynamic index
References: <4a1bd957$2@forums-1-dub> <4a1be638.4b54.1681692777@sybase.com> <4a1be948$1@forums-1-dub>
In-Reply-To: <4a1be948$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090523-0, 05/23/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a1beb89$1@forums-1-dub>
Date: 26 May 2009 06:15:53 -0700
X-Trace: forums-1-dub 1243343753 10.22.241.152 (26 May 2009 06:15:53 -0700)
X-Original-Trace: 26 May 2009 06:15:53 -0700, vip152.sybase.com
Lines: 56
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27795
Article PK: 77046

It would also help to know what version of ASE you're working with. (select @@version)

Mark A. Parsons wrote:
> You're the one who brought up the term 'dynamic index'.
>
> Yes, I'm aware of the term 'dynamic index' in ASE ... I'm just not sure
> what you're looking for ... a definition of dynamic index (have you
> looked in the ASE manuals?) ... or why the compiler is choosing to use a
> dynamic index for a particular query (what's the query and associated
> query plan?) ... or something else ... ??
>
>
>
>
> vtpcnk wrote:
>> but what is the reference to a dynamic index below?
>>
>>> What is an 'in' clause?
>>>
>>> Consider:
>>>
>>> id in (1,15, 37)
>>>
>>> Isn't this the same as:
>>>
>>> id = 1
>>> or id = 15
>>> or id = 37
>>>
>>> If the table has an index on the id column, the optimizer
>>> has the option of accessing the table 3 times using said
>>> index
>>> (id = 1 : id = 15 : id = 37).
>>>
>>> -------------
>>>
>>> If this doesn't answer your question then please provide
>>> more details of the situation you're trying to address (eg
>>> , sample query, sample query plan).
>>>
>>>
>>> vtpcnk wrote:
>>>> is this where multiple indexes are used to create a
>>>> dynamic index as the situation requires?
>>>>
>>>> supposed to be used in "in" or "or" clause.
>>>>
>>>> i can understand its use with the "or" clause where
>>>> multiple columns are used. for eg : where col1 = '1' and
>>>> col2 = '2', where a dynamic index integrating the two
>>>> columns from multiple indexes can be derived and thus
>>>> help the query.
>>>> but in the "in" clause? for the same column? why should
>>>> two indexes be used for this?
>>>>
>>>> appreciate the feedback.