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 not used

4 posts in General Discussion Last posting was on 2012-04-19 15:18:47.0Z
Cips Posted on 2012-04-18 10:59:58.0Z
Sender: 5d65.4f8e9d1b.1804289383@sybase.com
From: cips
Newsgroups: sybase.public.iq
Subject: Index not used
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f8e9eae.5d8e.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 18 Apr 2012 03:59:58 -0700
X-Trace: forums-1-dub 1334746798 172.20.134.41 (18 Apr 2012 03:59:58 -0700)
X-Original-Trace: 18 Apr 2012 03:59:58 -0700, 172.20.134.41
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4487
Article PK: 246624

Hello experts!

I have a question: I have a query with an exist statement

select FIELD1,FIELD2
from OUTTAB OT
where exists
( SELECT 1
FROM TAB1 t1
INNER JOIN TAB2 T2
ON t1.ID = T2.ID
WHERE 1=1
AND T1.DAY = XXXXXX
AND T1.TIME = YYYYYY
.....
)

Looking at the query plan, it looks that the index on T1.DAY
and T1.TIME is not used in the EXISTS. If I execute the
query outside of the EXISTS, the indexes are correctly used.

Any suggestion to avoid this?

Thanks,

cips


Leonid Gvirtz Posted on 2012-04-18 12:39:52.0Z
From: Leonid Gvirtz <leonid@gvirtz-consulting.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20120327 Thunderbird/11.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.iq
Subject: Re: Index not used
References: <4f8e9eae.5d8e.1681692777@sybase.com>
In-Reply-To: <4f8e9eae.5d8e.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: <4f8eb618$1@forums-1-dub>
Date: 18 Apr 2012 05:39:52 -0700
X-Trace: forums-1-dub 1334752792 10.22.241.152 (18 Apr 2012 05:39:52 -0700)
X-Original-Trace: 18 Apr 2012 05:39:52 -0700, vip152.sybase.com
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4488
Article PK: 246628

Hi

Without a detailed query plan, preferably HTML one, it will be difficult
to help you.
In addition, I don't see any reference to OUTTAB inside the EXISTS
subquery. If so, it is not clear why EXISTS clause is required at all.

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

On 4/18/2012 1:59 PM, cips wrote:
> Hello experts!
>
> I have a question: I have a query with an exist statement
>
> select FIELD1,FIELD2
> from OUTTAB OT
> where exists
> ( SELECT 1
> FROM TAB1 t1
> INNER JOIN TAB2 T2
> ON t1.ID = T2.ID
> WHERE 1=1
> AND T1.DAY = XXXXXX
> AND T1.TIME = YYYYYY
> .....
> )
>
> Looking at the query plan, it looks that the index on T1.DAY
> and T1.TIME is not used in the EXISTS. If I execute the
> query outside of the EXISTS, the indexes are correctly used.
>
> Any suggestion to avoid this?
>
> Thanks,
>
> cips


Cips Posted on 2012-04-18 13:01:34.0Z
Sender: 6238.4f8eb8c5.1804289383@sybase.com
From: cips
Newsgroups: sybase.public.iq
Subject: Re: Index not used
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f8ebb2e.6296.1681692777@sybase.com>
References: <4f8eb618$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 18 Apr 2012 06:01:34 -0700
X-Trace: forums-1-dub 1334754094 172.20.134.41 (18 Apr 2012 06:01:34 -0700)
X-Original-Trace: 18 Apr 2012 06:01:34 -0700, 172.20.134.41
Lines: 56
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4489
Article PK: 246629

You are right, I missed something in the query:

select FIELD1,FIELD2
from OUTTAB OT
where exists
( SELECT 1
FROM TAB1 t1
INNER JOIN TAB2 T2
ON t1.ID = T2.ID
WHERE 1=1
AND T1.DAY = OT.DAY
AND T1.TIME = OT.TIME
.....
)

I'll try to get a clean query plan...

> Hi
>
> Without a detailed query plan, preferably HTML one, it
> will be difficult to help you.
> In addition, I don't see any reference to OUTTAB inside
> the EXISTS subquery. If so, it is not clear why EXISTS
> clause is required at all.
>
> Hope it helps
> Leonid Gvirtz
> http://www.gvirtz-consulting.com
>
> On 4/18/2012 1:59 PM, cips wrote:
> > Hello experts!
> >
> > I have a question: I have a query with an exist
> statement >
> > select FIELD1,FIELD2
> > from OUTTAB OT
> > where exists
> > ( SELECT 1
> > FROM TAB1 t1
> > INNER JOIN TAB2 T2
> > ON t1.ID = T2.ID
> > WHERE 1=1
> > AND T1.DAY = XXXXXX
> > AND T1.TIME = YYYYYY
> > .....
> > )
> >
> > Looking at the query plan, it looks that the index on
> > T1.DAY and T1.TIME is not used in the EXISTS. If I
> > execute the query outside of the EXISTS, the indexes are
> correctly used. >
> > Any suggestion to avoid this?
> >
> > Thanks,
> >
> > cips
>


dew Posted on 2012-04-19 15:18:47.0Z
Sender: 18e1.4f9023be.1804289383@sybase.com
From: dew
Newsgroups: sybase.public.iq
Subject: Re: Index not used
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f902cd7.1a31.1681692777@sybase.com>
References: <4f8ebb2e.6296.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 19 Apr 2012 08:18:47 -0700
X-Trace: forums-1-dub 1334848727 172.20.134.41 (19 Apr 2012 08:18:47 -0700)
X-Original-Trace: 19 Apr 2012 08:18:47 -0700, 172.20.134.41
Lines: 64
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.iq:4490
Article PK: 246630

As a side note, unlike some DBMSes, "WHERE 1=1" has no
effect in IQ - it is unconditionally optimized away.

If you look in the list of local predicates executed
(conditions in the Leaf nodes or filter nodes) it will not
be there.

> You are right, I missed something in the query:
>
> select FIELD1,FIELD2
> from OUTTAB OT
> where exists
> ( SELECT 1
> FROM TAB1 t1
> INNER JOIN TAB2 T2
> ON t1.ID = T2.ID
> WHERE 1=1
> AND T1.DAY = OT.DAY
> AND T1.TIME = OT.TIME
> .....
> )
>
> I'll try to get a clean query plan...
> > Hi
> >
> > Without a detailed query plan, preferably HTML one, it
> > will be difficult to help you.
> > In addition, I don't see any reference to OUTTAB inside
> > the EXISTS subquery. If so, it is not clear why EXISTS
> > clause is required at all.
> >
> > Hope it helps
> > Leonid Gvirtz
> > http://www.gvirtz-consulting.com
> >
> > On 4/18/2012 1:59 PM, cips wrote:
> > > Hello experts!
> > >
> > > I have a question: I have a query with an exist
> > statement >
> > > select FIELD1,FIELD2
> > > from OUTTAB OT
> > > where exists
> > > ( SELECT 1
> > > FROM TAB1 t1
> > > INNER JOIN TAB2 T2
> > > ON t1.ID = T2.ID
> > > WHERE 1=1
> > > AND T1.DAY = XXXXXX
> > > AND T1.TIME = YYYYYY
> > > .....
> > > )
> > >
> > > Looking at the query plan, it looks that the index on
> > > T1.DAY and T1.TIME is not used in the EXISTS. If I
> > > execute the query outside of the EXISTS, the indexes
> > are correctly used. >
> > > Any suggestion to avoid this?
> > >
> > > Thanks,
> > >
> > > cips
> >