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.

Can OR cause table scan ?

8 posts in General Discussion Last posting was on 2009-11-04 16:20:48.0Z
Franz S. Posted on 2009-11-04 11:24:52.0Z
Sender: 3e76.4af16226.846930886@sybase.com
From: Franz S.
Newsgroups: sybase.public.ase.general
Subject: Can OR cause table scan ?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4af16484.3ece.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Nov 2009 03:24:52 -0800
X-Trace: forums-1-dub 1257333892 10.22.241.41 (4 Nov 2009 03:24:52 -0800)
X-Original-Trace: 4 Nov 2009 03:24:52 -0800, 10.22.241.41
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28594
Article PK: 77837

My question is, if an OR-condition can cause a table scan ?
Let me explain it in a reduced example: my affected table
my_table has amongst its columns 2 columns (my_date,
my_time). There are index on both columns. I want to query
for rows with a time-stamp before a certain point in time
(e.g. 2009.11.03 10:00). My where condition is ... (my_date
< "2009.11.03") OR (my_date = "2009.11.04" AND my_time <
"10:00") ... Now I discern a table scan an table
my_table(detected with showplan ON)
Can this be avoided anyhow, and make sybase to use the index
?


Kon T. Amusse Posted on 2009-11-04 14:10:02.0Z
From: "Kon T. Amusse" <kon.t@amusse.invalid>
Newsgroups: sybase.public.ase.general
Subject: Re: Can OR cause table scan ?
Date: Wed, 4 Nov 2009 15:10:02 +0100
Organization: A noiseless patient Spider
Lines: 14
Message-ID: <hcs1vt$iqp$1@news.eternal-september.org>
References: <4af16484.3ece.1681692777@sybase.com>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Trace: news.eternal-september.org U2FsdGVkX18D24zOcJyM5BQjkRj7gNRRVTSbtD52qM2M1X0YQiJ4b9IUM3vzLRjFSVA7tH93OLsZWwKuCszCzW84LB1EJDtIxSapXamtYfIZXkpf+wUSU0LPNZCpdc3IfiCC9VbGB0sWj5HthcArfApZc6gcM7nE
X-Complaints-To: abuse@eternal-september.org
NNTP-Posting-Date: Wed, 4 Nov 2009 14:10:05 +0000 (UTC)
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
In-Reply-To: <4af16484.3ece.1681692777@sybase.com>
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-Auth-Sender: U2FsdGVkX18oJTkbN9qBSSDPwlujy8MFBfnMC2Ax6oSaAxvFQsxLLw==
Cancel-Lock: sha1:v2gc9+l6ON7blF6acZDTkpV6le0=
X-Priority: 3
X-MSMail-Priority: Normal
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!novia!feeder.news-service.com!188.40.43.213.MISMATCH!feeder.eternal-september.org!eternal-september.org!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28598
Article PK: 77840


"Franz S." wrote in message news:4af16484.3ece.1681692777@sybase.com...
> My question is, if an OR-condition can cause a table scan ?
> Let me explain it in a reduced example: my affected table
> my_table has amongst its columns 2 columns (my_date,
> my_time). There are index on both columns. I want to query
> for rows with a time-stamp before a certain point in time
> (e.g. 2009.11.03 10:00). My where condition is ... (my_date
> < "2009.11.03") OR (my_date = "2009.11.04" AND my_time <
> "10:00") ... Now I discern a table scan an table
> my_table(detected with showplan ON)
> Can this be avoided anyhow, and make sybase to use the index
> ?

Can you create an index on my_date,my_time?


Franz S. Posted on 2009-11-04 14:33:22.0Z
Sender: 3e76.4af16226.846930886@sybase.com
From: Franz S.
Newsgroups: sybase.public.ase.general
Subject: Re: Can OR cause table scan ?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4af190b2.4524.1681692777@sybase.com>
References: <hcs1vt$iqp$1@news.eternal-september.org>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Nov 2009 06:33:22 -0800
X-Trace: forums-1-dub 1257345202 10.22.241.41 (4 Nov 2009 06:33:22 -0800)
X-Original-Trace: 4 Nov 2009 06:33:22 -0800, 10.22.241.41
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28600
Article PK: 77842

Yes, I already have an index on these fields. But it is only
used when I have only one condition. When I use OR the index
is ignored an table scan is performed.

> "Franz S." wrote in message
> > news:4af16484.3ece.1681692777@sybase.com... My question
> > is, if an OR-condition can cause a table scan ? Let me
> > explain it in a reduced example: my affected table
> > my_table has amongst its columns 2 columns (my_date,
> > my_time). There are index on both columns. I want to
> query for rows with a time-stamp before a certain point in
> > time (e.g. 2009.11.03 10:00). My where condition is ...
> > (my_date < "2009.11.03") OR (my_date = "2009.11.04" AND
> > my_time < "10:00") ... Now I discern a table scan an
> > table my_table(detected with showplan ON)
> > Can this be avoided anyhow, and make sybase to use the
> > index ?
>
> Can you create an index on my_date,my_time?


"Mark A. Parsons" <iron_horse Posted on 2009-11-04 14:57:06.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: Can OR cause table scan ?
References: <hcs1vt$iqp$1@news.eternal-september.org> <4af190b2.4524.1681692777@sybase.com>
In-Reply-To: <4af190b2.4524.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091030-0, 10/30/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4af19642$1@forums-1-dub>
Date: 4 Nov 2009 06:57:06 -0800
X-Trace: forums-1-dub 1257346626 10.22.241.152 (4 Nov 2009 06:57:06 -0800)
X-Original-Trace: 4 Nov 2009 06:57:06 -0800, vip152.sybase.com
Lines: 57
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28601
Article PK: 77844

A few questions ...

- how many records in the table?

- how many records match the combined OR clauses?

- how many records match the separate OR clauses?

- which locking scheme (allpages, datapages, datarows) is the table using?

- is the desired index defined as clustered or non-clustered?

- are the statistics up to date on this table?

- if the table has millions of records, is 'update [index] statistics' using more than the default 20 histogram steps?

- what version of ASE are you using?

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

What happens if you break your query into 2 queries and test/validate the query plans one at a time?

What happens if you force the desired index ... for the current query ... and for the 2 individual queries (assuming
those also choose not to use the desired query)? I'd suggest enabling 'set statistics io on' prior to executing all
queries so that you can capture/compare IO statistics.

The general idea is to see if you can ascertain the problem by looking at the individual components (separate queries)
as well as the IO statistics with and without the desired index.

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

I'm assuming you want all records with a date/time combination that is less than or equal to 2009.11.03/10:00.

What happens if you modify the second OR clause to be (my_date = "2009.11.03" and my_time < "10:00")?

What happens if you use this new OR clause in the previous tests (eg, test 2 separate queries)?

Franz S. wrote:
> Yes, I already have an index on these fields. But it is only
> used when I have only one condition. When I use OR the index
> is ignored an table scan is performed.
>
>> "Franz S." wrote in message
>>> news:4af16484.3ece.1681692777@sybase.com... My question
>>> is, if an OR-condition can cause a table scan ? Let me
>>> explain it in a reduced example: my affected table
>>> my_table has amongst its columns 2 columns (my_date,
>>> my_time). There are index on both columns. I want to
>> query for rows with a time-stamp before a certain point in
>>> time (e.g. 2009.11.03 10:00). My where condition is ...
>>> (my_date < "2009.11.03") OR (my_date = "2009.11.04" AND
>>> my_time < "10:00") ... Now I discern a table scan an
>>> table my_table(detected with showplan ON)
>>> Can this be avoided anyhow, and make sybase to use the
>>> index ?
>> Can you create an index on my_date,my_time?


"Mark A. Parsons" <iron_horse Posted on 2009-11-04 15:31:06.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: Can OR cause table scan ?
References: <hcs1vt$iqp$1@news.eternal-september.org> <4af190b2.4524.1681692777@sybase.com> <4af19642$1@forums-1-dub>
In-Reply-To: <4af19642$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091030-0, 10/30/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4af19e3a$1@forums-1-dub>
Date: 4 Nov 2009 07:31:06 -0800
X-Trace: forums-1-dub 1257348666 10.22.241.152 (4 Nov 2009 07:31:06 -0800)
X-Original-Trace: 4 Nov 2009 07:31:06 -0800, vip152.sybase.com
Lines: 14
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28604
Article PK: 77846

Ahhh, misread the second OR clause ... scratch/forget this part ...

Mark A. Parsons wrote:
> ----------------------
>
> I'm assuming you want all records with a date/time combination that is
> less than or equal to 2009.11.03/10:00.
>
> What happens if you modify the second OR clause to be (my_date =
> "2009.11.03" and my_time < "10:00")?
>
> What happens if you use this new OR clause in the previous tests (eg,
> test 2 separate queries)?


Sherlock, Kevin [TeamSybase] Posted on 2009-11-04 15:08:33.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <hcs1vt$iqp$1@news.eternal-september.org> <4af190b2.4524.1681692777@sybase.com>
Subject: Re: Can OR cause table scan ?
Lines: 57
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4af198f1$1@forums-1-dub>
Date: 4 Nov 2009 07:08:33 -0800
X-Trace: forums-1-dub 1257347313 10.22.241.152 (4 Nov 2009 07:08:33 -0800)
X-Original-Trace: 4 Nov 2009 07:08:33 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28603
Article PK: 77845

Whether or not an index is chosen as the access path depends on the
statistics and the estimated cost of using the index versus a table scan.
However, you can re-write the query to use a valid search argument which
_might_ persuade the optimizer to choose the index. Consider:

where ( my_date < "2009.11.03" OR ( my_date = "2009.11.04" AND my_time <
"10:00" ) )
AND my_date <= "2009.11.04"

The above sets an upper limit on my_date which can be used for key
positioning on an index on my_date. This upper limit is derived from the
union of both conditions and doesn't change the logic of the original
predicate. But, it provides a search argument suitable for key positioning
on the index at the expense of having to access and read all of the
"2009.11.03" and ALL of the "2009.11.04" index entries (regardless of
my_time values) that won't qualify for your OR conditions.

There is no upper limit on my_time since the union of both conditions
include all times, so including that column in the index has no benefit for
this query.

Another alternative (which eliminates the wasteful reads above) is to write
your query using "union all" :

select ..
from ..
where my_date < "2009.11.03"
UNION ALL
select ..
from ..
where my_date = "2009.11.04" AND my_time < "10:00"

The first SELECT stmnt can key position on my_date, and the second SELECT
stmnt can key postition on my_date and my_time.

<Franz S.> wrote in message news:4af190b2.4524.1681692777@sybase.com...
> Yes, I already have an index on these fields. But it is only
> used when I have only one condition. When I use OR the index
> is ignored an table scan is performed.
>
>> "Franz S." wrote in message
>> > news:4af16484.3ece.1681692777@sybase.com... My question
>> > is, if an OR-condition can cause a table scan ? Let me
>> > explain it in a reduced example: my affected table
>> > my_table has amongst its columns 2 columns (my_date,
>> > my_time). There are index on both columns. I want to
>> query for rows with a time-stamp before a certain point in
>> > time (e.g. 2009.11.03 10:00). My where condition is ...
>> > (my_date < "2009.11.03") OR (my_date = "2009.11.04" AND
>> > my_time < "10:00") ... Now I discern a table scan an
>> > table my_table(detected with showplan ON)
>> > Can this be avoided anyhow, and make sybase to use the
>> > index ?
>>
>> Can you create an index on my_date,my_time?


Carl Kayser Posted on 2009-11-04 15:00:24.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4af16484.3ece.1681692777@sybase.com>
Subject: Re: Can OR cause table scan ?
Lines: 29
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3598
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4af19708$1@forums-1-dub>
Date: 4 Nov 2009 07:00:24 -0800
X-Trace: forums-1-dub 1257346824 10.22.241.152 (4 Nov 2009 07:00:24 -0800)
X-Original-Trace: 4 Nov 2009 07:00:24 -0800, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28602
Article PK: 77843


<Franz S.> wrote in message news:4af16484.3ece.1681692777@sybase.com...
> My question is, if an OR-condition can cause a table scan ?
> Let me explain it in a reduced example: my affected table
> my_table has amongst its columns 2 columns (my_date,
> my_time). There are index on both columns. I want to query
> for rows with a time-stamp before a certain point in time
> (e.g. 2009.11.03 10:00). My where condition is ... (my_date
> < "2009.11.03") OR (my_date = "2009.11.04" AND my_time <
> "10:00") ... Now I discern a table scan an table
> my_table(detected with showplan ON)
> Can this be avoided anyhow, and make sybase to use the index
> ?

"There are index on both columns." Do you mean "There is one composite
index on the two columns." or "There are two indexes; one on each column."?
If the former, then what is the index column order?

Is the table APL, DPL, or DRL?

Are your index statistics reasonably new?

Which ASE version is this on?

As a first guess it looks like you actually are looking at most of the
table - everything befor 10:00 today. Unless there is ONLY very, very
recent data in the table a table scan makes sense.


Franz S. Posted on 2009-11-04 16:20:48.0Z
Sender: 3e76.4af16226.846930886@sybase.com
From: Franz S.
Newsgroups: sybase.public.ase.general
Subject: Re: Can OR cause table scan ?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4af1a9e0.488a.1681692777@sybase.com>
References: <4af16484.3ece.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Nov 2009 08:20:48 -0800
X-Trace: forums-1-dub 1257351648 10.22.241.41 (4 Nov 2009 08:20:48 -0800)
X-Original-Trace: 4 Nov 2009 08:20:48 -0800, 10.22.241.41
Lines: 6
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28607
Article PK: 77850

The test during which I discerned the table scan were on a
DB with only a few entries. When I switched to a DB with a
lifelike number of entries the index was used (as expected).
So I'm not worried any longer...

Thanks to all of you for your effort and assistant input.