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.

Query Plan

2 posts in General Discussion Last posting was on 2009-09-18 18:37:49.0Z
Anna Posted on 2009-09-18 17:57:08.0Z
Sender: 76d2.4ab3c743.1804289383@sybase.com
From: Anna
Newsgroups: sybase.public.ase.general
Subject: Query Plan
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ab3c9f4.7740.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Sep 2009 10:57:08 -0700
X-Trace: forums-1-dub 1253296628 10.22.241.41 (18 Sep 2009 10:57:08 -0700)
X-Original-Trace: 18 Sep 2009 10:57:08 -0700, 10.22.241.41
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28330
Article PK: 77573

Hi all! why does the query plan for the ff sql in my stored
procedure use table scan to select from WE_PO_ITEM table
instead of using an index? It uses index for WE_POI_REC.

TIA.

insert #match_total
select DISTINCT POIR_PROJECT, POIR_PO_NO, POIR_PO_REC_NO,
convert(decimal(11, 4), sum(case UOM_CODE when 'L' then
(case when POIR_QTY_RECV < 0 then POIR_COST * -1
when POIR_QTY_RECV = 0 then 0 else POIR_COST end )
whenen 'M' then (POIR_QTY_RECV * POIR_COST *
UOM_DEC_EXT_FACTOR )
when 'D' then (( POIR_QTY_RECV * POIR_COST )/
UOM_DEC_EXT_FACTOR )
else (POIR_QTY_RECV * POIR_COST) end ) ) ,
convert(decimal(11, 4), sum(case UOM_CODE
when 'L' then (case when POIR_OSD_QTY < 0 then POIR_COST *
-1
when POIR_OSD_QTY = 0 then 0 else POIR_COST end )
when 'M' then (POIR_OSD_QTY * POIR_COST *
UOM_DEC_EXT_FACTOR )
when 'D' then (( POIR_OSD_QTY * POIR_COST )/
UOM_DEC_EXT_FACTOR )
else (POIR_OSD_QTY * POIR_COST) end ) )
from WE_PO_ITEM, WE_POI_REC, WE_UOM
where POI_PROJECT = POIR_PROJECT
and POI_PO_NO = POIR_PO_NO
and POI_LINE_ITEM = POIR_LINE_ITEM
and isnull(POIR_UOM, POI_UOM)*= UOM_UOM
group by POIR_PROJECT, POIR_PO_NO, POIR_PO_REC_NO


Sherlock, Kevin [TeamSybase] Posted on 2009-09-18 18:37:49.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4ab3c9f4.7740.1681692777@sybase.com>
Subject: Re: Query Plan
Lines: 43
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
x-mimeole: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ab3d37d$1@forums-1-dub>
Date: 18 Sep 2009 11:37:49 -0700
X-Trace: forums-1-dub 1253299069 10.22.241.152 (18 Sep 2009 11:37:49 -0700)
X-Original-Trace: 18 Sep 2009 11:37:49 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28331
Article PK: 77574

there are no search arguments in your query. What index would you expect it
to use, and how would it position on a key in that index when you don't
search for any records in WE_PO_ITEM ?

You should read up on query optimization in the manuals.

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc20020.1502/html/basics/title.htm

<Anna> wrote in message news:4ab3c9f4.7740.1681692777@sybase.com...
> Hi all! why does the query plan for the ff sql in my stored
> procedure use table scan to select from WE_PO_ITEM table
> instead of using an index? It uses index for WE_POI_REC.
>
> TIA.
>
> insert #match_total
> select DISTINCT POIR_PROJECT, POIR_PO_NO, POIR_PO_REC_NO,
> convert(decimal(11, 4), sum(case UOM_CODE when 'L' then
> (case when POIR_QTY_RECV < 0 then POIR_COST * -1
> when POIR_QTY_RECV = 0 then 0 else POIR_COST end )
> whenen 'M' then (POIR_QTY_RECV * POIR_COST *
> UOM_DEC_EXT_FACTOR )
> when 'D' then (( POIR_QTY_RECV * POIR_COST )/
> UOM_DEC_EXT_FACTOR )
> else (POIR_QTY_RECV * POIR_COST) end ) ) ,
> convert(decimal(11, 4), sum(case UOM_CODE
> when 'L' then (case when POIR_OSD_QTY < 0 then POIR_COST *
> -1
> when POIR_OSD_QTY = 0 then 0 else POIR_COST end )
> when 'M' then (POIR_OSD_QTY * POIR_COST *
> UOM_DEC_EXT_FACTOR )
> when 'D' then (( POIR_OSD_QTY * POIR_COST )/
> UOM_DEC_EXT_FACTOR )
> else (POIR_OSD_QTY * POIR_COST) end ) )
> from WE_PO_ITEM, WE_POI_REC, WE_UOM
> where POI_PROJECT = POIR_PROJECT
> and POI_PO_NO = POIR_PO_NO
> and POI_LINE_ITEM = POIR_LINE_ITEM
> and isnull(POIR_UOM, POI_UOM)*= UOM_UOM
> group by POIR_PROJECT, POIR_PO_NO, POIR_PO_REC_NO