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.

Forcing index in delete statement

6 posts in General Discussion Last posting was on 2009-07-03 11:08:07.0Z
Vojislav Depalov <vojislav.depalov Posted on 2009-07-02 16:07:59.0Z
From: Vojislav Depalov <vojislav.depalov@remove_this_to_reach_me_dba-sybase.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Forcing index in delete statement
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: <4a4cdb5f@forums-3-dub.sybase.com>
Date: 2 Jul 2009 09:07:59 -0700
X-Trace: forums-3-dub.sybase.com 1246550879 10.22.241.152 (2 Jul 2009 09:07:59 -0700)
X-Original-Trace: 2 Jul 2009 09:07:59 -0700, vip152.sybase.com
Lines: 16
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27948
Article PK: 77194

What am I missing when I try to force an index in delete statement and
getting syntax error?

Here is the original and "forced index" statement:
delete from ARCH_MEAS where ((PR_TIME<=@l_upper_time) and
(PR_TIME>@l_lower_time))

delete from ARCH_MEAS (index ARCH_MEAS_I_PR_TIME_ID) where
((PR_TIME<=@l_upper_time) and (PR_TIME>@l_lower_time))


Sybase ASE, 12.5.3 version.

Thank you.

Vojislav Depalov


Carl Kayser Posted on 2009-07-02 16:42:00.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4a4cdb5f@forums-3-dub.sybase.com>
Subject: Re: Forcing index in delete statement
Lines: 23
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a4ce358@forums-3-dub.sybase.com>
Date: 2 Jul 2009 09:42:00 -0700
X-Trace: forums-3-dub.sybase.com 1246552920 10.22.241.152 (2 Jul 2009 09:42:00 -0700)
X-Original-Trace: 2 Jul 2009 09:42:00 -0700, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27949
Article PK: 77196

"Vojislav Depalov" <vojislav.depalov@remove_this_to_reach_me_dba-sybase.com>

wrote in message news:4a4cdb5f@forums-3-dub.sybase.com...
> What am I missing when I try to force an index in delete statement and
> getting syntax error?
>
> Here is the original and "forced index" statement:
> delete from ARCH_MEAS where ((PR_TIME<=@l_upper_time) and
> (PR_TIME>@l_lower_time))
>
> delete from ARCH_MEAS (index ARCH_MEAS_I_PR_TIME_ID) where
> ((PR_TIME<=@l_upper_time) and (PR_TIME>@l_lower_time))
>
>
> Sybase ASE, 12.5.3 version.
>
> Thank you.
>
> Vojislav Depalov

And the exact syntax error is ....?


Vojislav Depalov <vojislav.depalov Posted on 2009-07-02 16:54:04.0Z
From: Vojislav Depalov <vojislav.depalov@remove_this_to_reach_me_dba-sybase.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Forcing index in delete statement
References: <4a4cdb5f@forums-3-dub.sybase.com> <4a4ce358@forums-3-dub.sybase.com>
In-Reply-To: <4a4ce358@forums-3-dub.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: <4a4ce62c$1@forums-3-dub.sybase.com>
Date: 2 Jul 2009 09:54:04 -0700
X-Trace: forums-3-dub.sybase.com 1246553644 10.22.241.152 (2 Jul 2009 09:54:04 -0700)
X-Original-Trace: 2 Jul 2009 09:54:04 -0700, vip152.sybase.com
Lines: 40
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27950
Article PK: 77202


Carl Kayser wrote:
> "Vojislav Depalov" <vojislav.depalov@remove_this_to_reach_me_dba-sybase.com>
> wrote in message news:4a4cdb5f@forums-3-dub.sybase.com...
>> What am I missing when I try to force an index in delete statement and
>> getting syntax error?
>>
>> Here is the original and "forced index" statement:
>> delete from ARCH_MEAS where ((PR_TIME<=@l_upper_time) and
>> (PR_TIME>@l_lower_time))
>>
>> delete from ARCH_MEAS (index ARCH_MEAS_I_PR_TIME_ID) where
>> ((PR_TIME<=@l_upper_time) and (PR_TIME>@l_lower_time))
>>
>>
>> Sybase ASE, 12.5.3 version.
>>
>> Thank you.
>>
>> Vojislav Depalov
>
> And the exact syntax error is ....?
>
>

Syntax error is

"Incorrect syntax near the keyword 'index'."


The same syntax is ok when it is used with select statement like:


select count(*) from ARCH_MEAS (index ARCH_MEAS_I_PR_TIME_ID) where
((PR_TIME<=@l_upper_time) and (PR_TIME>@l_lower_time))

but from some reason, it does not work with delete.

I presume that delete with forcing index is not supported, but I've
wanted to check this.

Vojislav Depalov


Bret Halford [Sybase] Posted on 2009-07-02 17:26:39.0Z
From: "Bret Halford [Sybase]" <bret@sybase.com>
Organization: Sybase, Inc.
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Forcing index in delete statement
References: <4a4cdb5f@forums-3-dub.sybase.com> <4a4ce358@forums-3-dub.sybase.com> <4a4ce62c$1@forums-3-dub.sybase.com>
In-Reply-To: <4a4ce62c$1@forums-3-dub.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: <4a4cedcf@forums-3-dub.sybase.com>
Date: 2 Jul 2009 10:26:39 -0700
X-Trace: forums-3-dub.sybase.com 1246555599 10.22.241.152 (2 Jul 2009 10:26:39 -0700)
X-Original-Trace: 2 Jul 2009 10:26:39 -0700, vip152.sybase.com
Lines: 24
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27951
Article PK: 77198


> Syntax error is
>
> "Incorrect syntax near the keyword 'index'."
>
>
> The same syntax is ok when it is used with select statement like:
>
>
> select count(*) from ARCH_MEAS (index ARCH_MEAS_I_PR_TIME_ID) where
> ((PR_TIME<=@l_upper_time) and (PR_TIME>@l_lower_time))
>
> but from some reason, it does not work with delete.
>
> I presume that delete with forcing index is not supported, but I've
> wanted to check this.

It isn't supported; when you INSERT or DELETE a row, ASE needs to access
every index to find and add/remove the index entry for the row. With
SELECT, ASE only needs to access one index (or table scan) to find the
row, so it is then possible to specify a single index to be used.

-bret


"Mark A. Parsons" <iron_horse Posted on 2009-07-02 18:31:08.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: Forcing index in delete statement
References: <4a4cdb5f@forums-3-dub.sybase.com>
In-Reply-To: <4a4cdb5f@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090702-0, 07/02/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a4cfcec$2@forums-3-dub.sybase.com>
Date: 2 Jul 2009 11:31:08 -0700
X-Trace: forums-3-dub.sybase.com 1246559468 10.22.241.152 (2 Jul 2009 11:31:08 -0700)
X-Original-Trace: 2 Jul 2009 11:31:08 -0700, vip152.sybase.com
Lines: 27
X-Authenticated-User: TeamSybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27953
Article PK: 77199

Try:


delete ARCH_MEAS
from ARCH_MEAS (index ARCH_MEAS_I_PR_TIME_ID)
where ((PR_TIME<=@l_upper_time) and (PR_TIME>@l_lower_time))

Vojislav Depalov wrote:
> What am I missing when I try to force an index in delete statement and
> getting syntax error?
>
> Here is the original and "forced index" statement:
> delete from ARCH_MEAS where ((PR_TIME<=@l_upper_time) and
> (PR_TIME>@l_lower_time))
>
> delete from ARCH_MEAS (index ARCH_MEAS_I_PR_TIME_ID) where
> ((PR_TIME<=@l_upper_time) and (PR_TIME>@l_lower_time))
>
>
> Sybase ASE, 12.5.3 version.
>
> Thank you.
>
> Vojislav Depalov


Vojislav Depalov <vojislav.depalov Posted on 2009-07-03 11:08:07.0Z
From: Vojislav Depalov <vojislav.depalov@remove_this_to_reach_me_dba-sybase.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Forcing index in delete statement
References: <4a4cdb5f@forums-3-dub.sybase.com> <4a4cfcec$2@forums-3-dub.sybase.com>
In-Reply-To: <4a4cfcec$2@forums-3-dub.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: <4a4de697$1@forums-3-dub.sybase.com>
Date: 3 Jul 2009 04:08:07 -0700
X-Trace: forums-3-dub.sybase.com 1246619287 10.22.241.152 (3 Jul 2009 04:08:07 -0700)
X-Original-Trace: 3 Jul 2009 04:08:07 -0700, vip152.sybase.com
Lines: 34
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27959
Article PK: 77209

Mark, thank you, it works :-)

Best regards,
Vojislav Depalov

Mark A. Parsons wrote:
> Try:
>
>
> delete ARCH_MEAS
> from ARCH_MEAS (index ARCH_MEAS_I_PR_TIME_ID)
> where ((PR_TIME<=@l_upper_time) and (PR_TIME>@l_lower_time))
>
>
>
>
> Vojislav Depalov wrote:
>> What am I missing when I try to force an index in delete statement and
>> getting syntax error?
>>
>> Here is the original and "forced index" statement:
>> delete from ARCH_MEAS where ((PR_TIME<=@l_upper_time) and
>> (PR_TIME>@l_lower_time))
>>
>> delete from ARCH_MEAS (index ARCH_MEAS_I_PR_TIME_ID) where
>> ((PR_TIME<=@l_upper_time) and (PR_TIME>@l_lower_time))
>>
>>
>> Sybase ASE, 12.5.3 version.
>>
>> Thank you.
>>
>> Vojislav Depalov