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.

index name not found issue

5 posts in General Discussion Last posting was on 2009-06-26 16:18:49.0Z
P.L. Posted on 2009-06-23 01:36:23.0Z
From: "P.L." <abc@abc.com>
Newsgroups: sybase.public.ase.general
Subject: index name not found issue
X-Newsreader: newsgroupstats.hk
X-HTTP-Posting-Host: 203.31.32.92
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a403197@forums-3-dub.sybase.com>
Date: 22 Jun 2009 18:36:23 -0700
X-Trace: forums-3-dub.sybase.com 1245720983 10.22.241.152 (22 Jun 2009 18:36:23 -0700)
X-Original-Trace: 22 Jun 2009 18:36:23 -0700, vip152.sybase.com
Lines: 74
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27903
Article PK: 77151

Dear all,

I've met a issue that I cannot found the answer from the internet. Hope you can help me.

Environemnt : ASE 12.5 version database

I've write a Store procedure to do house keeping task (copy records from table a to table b)

I use temp table to hold those target records before copy to table b.

Part of the Logic in the SP :-


create table #fac_room_schm
(
fvr_id numeric(10,0) ,
fat_id numeric(5,0) ,
peak_flag numeric(5,0) ,
period_start_time smallint ,
period_end_time smallint ,
duration smallint ,
num_of_ssn numeric(5,0) ,
session_date datetime
)

Create index #pk_fac_room_schm on #fac_room_schm ( session_date )



Insert into #fac_room_schm
(
fvr_id ,
fat_id ,
peak_flag ,
period_start_time ,
period_end_time ,
duration ,
num_of_ssn ,
session_date
)
Select
fvr_id ,
fat_id ,
peak_flag ,
period_start_time ,
period_end_time ,
duration ,
num_of_ssn ,
session_date
from fac_room_schm
where
session_date >= @start_date
and session_date < @end_date

From query plan, it shows that fac_room_schm's index file was found and used. But for #fac_room_schm the query plan shows "Index name not found".

I've created the temp table index file, but why query plan said "index name not found" ?

Can someone tell me why ?

Thanks!

Rgds,

P.L.


--
Free News Reader
http://put.hk
http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html


P.L. Posted on 2009-06-23 03:25:58.0Z
From: "P.L." <abc@abc.com>
Newsgroups: sybase.public.ase.general
Subject: Re: index name not found issue
References: <4a403197@forums-3-dub.sybase.com>
X-Newsreader: newsgroupstats.hk
X-HTTP-Posting-Host: 203.31.32.92
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a404b46@forums-3-dub.sybase.com>
Date: 22 Jun 2009 20:25:58 -0700
X-Trace: forums-3-dub.sybase.com 1245727558 10.22.241.152 (22 Jun 2009 20:25:58 -0700)
X-Original-Trace: 22 Jun 2009 20:25:58 -0700, vip152.sybase.com
Lines: 70
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27904
Article PK: 77152

All,

I've found the root cause of it. No need to focus on my problem. Thanks

P.L.



--
Free News Reader
http://put.hk
http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html

P.L. <abc@abc.com> wrote:
> Dear all,
> I've met a issue that I cannot found the answer from the internet. Hope you can help me.
> Environemnt : ASE 12.5 version database
> I've write a Store procedure to do house keeping task (copy records from table a to table b)
> I use temp table to hold those target records before copy to table b.
> Part of the Logic in the SP :-
> create table #fac_room_schm
> (
> fvr_id numeric(10,0) ,
> fat_id numeric(5,0) ,
> peak_flag numeric(5,0) ,
> period_start_time smallint ,
> period_end_time smallint ,
> duration smallint ,
> num_of_ssn numeric(5,0) ,
> session_date datetime
> )
>
> Create index #pk_fac_room_schm on #fac_room_schm ( session_date )
>
> Insert into #fac_room_schm
> (
> fvr_id ,
> fat_id ,
> peak_flag ,
> period_start_time ,
> period_end_time ,
> duration ,
> num_of_ssn ,
> session_date
> )
> Select
> fvr_id ,
> fat_id ,
> peak_flag ,
> period_start_time ,
> period_end_time ,
> duration ,
> num_of_ssn ,
> session_date
> from fac_room_schm
> where
> session_date >= @start_date
> and session_date < @end_date
> From query plan, it shows that fac_room_schm's index file was found and used. But for #fac_room_schm the query plan shows "Index name not found".
> I've created the temp table index file, but why query plan said "index name not found" ?
> Can someone tell me why ?
> Thanks!
> Rgds,
> P.L.
> --
> Free News Reader
> http://put.hk
> http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html


ThanksButNo Posted on 2009-06-23 05:43:46.0Z
From: ThanksButNo <no.no.thanks@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: index name not found issue
Date: Mon, 22 Jun 2009 22:43:46 -0700 (PDT)
Organization: http://groups.google.com
Lines: 11
Message-ID: <03219591-0686-4196-9f6f-a2a2f0034d12@g19g2000yql.googlegroups.com>
References: <4a403197@forums-3-dub.sybase.com> <4a404b46@forums-3-dub.sybase.com>
NNTP-Posting-Host: 71.165.35.183
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1245735826 23613 127.0.0.1 (23 Jun 2009 05:43:46 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 23 Jun 2009 05:43:46 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: g19g2000yql.googlegroups.com; posting-host=71.165.35.183; posting-account=wjKAPwoAAABtEbTff5o9OO7GYdigbDts
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.11) Gecko/2009060215 Firefox/3.0.11 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!g19g2000yql.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27905
Article PK: 77153


On Jun 22, 8:25 pm, "P.L." <a...@abc.com> wrote:
> All,
>
> I've found the root cause of it.   No need to focus on my problem. Thanks
>

You really should explain what the solution is, in case
someone else with a similar problem searches here.

\:-\


P.L. Posted on 2009-06-23 07:30:40.0Z
From: "P.L." <abc@abc.com>
Newsgroups: sybase.public.ase.general
Subject: Re: index name not found issue
References: <4a403197@forums-3-dub.sybase.com> <4a404b46@forums-3-dub.sybase.com> <03219591-0686-4196-9f6f-a2a2f0034d12@g19g2000yql.googlegroups.com>
X-Newsreader: newsgroupstats.hk
X-HTTP-Posting-Host: 203.31.32.92
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a4084a0@forums-3-dub.sybase.com>
Date: 23 Jun 2009 00:30:40 -0700
X-Trace: forums-3-dub.sybase.com 1245742240 10.22.241.152 (23 Jun 2009 00:30:40 -0700)
X-Original-Trace: 23 Jun 2009 00:30:40 -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:27906
Article PK: 77154

The issue is below syntax

delete tbl_a
from #tbl_b
where #tbl_b.session_date = tbl_a.session_date


tbl_a is physical table
and tbl_b is temp table
Both had index

Table b is temp table, on query plan show index name not found for temp table #tbl_b

But I change the syntax as below, query plan show all the related index that use in the joinging


delete tbl_a
from #tbl_b b , tbl_a a
where b.session_date = a.session_date





--
Free News Reader
http://put.hk
http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html

ThanksButNo <no.no.thanks@gmail.com> wrote:
> On Jun 22, 8:25 pm, "P.L." <a...@abc.com> wrote:
> > All,
> >
> > I've found the root cause of it. ?No need to focus on my problem. Thanks
> >
> You really should explain what the solution is, in case
> someone else with a similar problem searches here.
> \:-\


Leonid Gvirtz Posted on 2009-06-26 16:18:49.0Z
From: Leonid Gvirtz <lgvirtz@yahoo.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: index name not found issue
References: <4a403197@forums-3-dub.sybase.com> <4a404b46@forums-3-dub.sybase.com> <03219591-0686-4196-9f6f-a2a2f0034d12@g19g2000yql.googlegroups.com> <4a4084a0@forums-3-dub.sybase.com>
In-Reply-To: <4a4084a0@forums-3-dub.sybase.com>
Content-Type: multipart/alternative; boundary="------------080708010603090108080806"
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a44f4e9$3@forums-3-dub.sybase.com>
Date: 26 Jun 2009 09:18:49 -0700
X-Trace: forums-3-dub.sybase.com 1246033129 10.22.241.152 (26 Jun 2009 09:18:49 -0700)
X-Original-Trace: 26 Jun 2009 09:18:49 -0700, vip152.sybase.com
Lines: 146
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27923
Article PK: 77170

I faced with a very similar problem just recently. It appeared the "number of open indexes" configuration parameter was configured too low and ASE was reusing index descriptors, which led to "Index not found" messages in execution plans. You can verify if you hit the same problem by running sp_sysmon checking if index descriptors were reused (which should not normally happen). Increasing of "number of open indexes" solved the problem immediately in my case (ASE 12.5.4 ESD#9.1).

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com


P.L. wrote:

The issue is below syntax delete tbl_a from #tbl_b where #tbl_b.session_date = tbl_a.session_date tbl_a is physical table and tbl_b is temp table Both had index Table b is temp table, on query plan show index name not found for temp table #tbl_b But I change the syntax as below, query plan show all the related index that use in the joinging delete tbl_a from #tbl_b b , tbl_a a where b.session_date = a.session_date -- Free News Reader http://put.hk http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html ThanksButNo <no.no.thanks@gmail.com> wrote:
On Jun 22, 8:25 pm, "P.L." <a...@abc.com> wrote:
All, I've found the root cause of it. ?No need to focus on my problem. Thanks
You really should explain what the solution is, in case someone else with a similar problem searches here. \:-\