473,241 Members | 1,602 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

Strange access plan

Hello.

W2K, db2 v7, fp11.

Given:

create table pays (
acode integer not null,
packno smallint not null,
sum decimal(15, 2) not null
);
create index pays1 on pays(packno);

create table abonents (
acode integer not null primary key,
name varchar(80) not null,
specauth smallint not null
);

create view abonents_spec (acode, name) as
select acode, case when specauth<2 then name else '*' end
from abonents;

I have to issue this query:

(1)
select p.acode, p.sum, a.name
from pays p
left join abonents_spec a on p.acode=a.acode
where p.packno=:packno;

In this case I have index scan of 'pays' (as expected) and TABLE scan of
'abonents', but in case of using in (1) inner join or left join with table
'abonents' instead of view 'abonents_spec' I get expected INDEX scan of
tabel 'abonents'!!!
Tables are about 200 000 rows. Statistics is OK. There is no special
distirbution of data in these tables.
I have tried all optimization levels.
I think it is very strange behavior of the optimizer...

I had to rewrite (1) with emulation of the left join to make optimizer use
index scan of table 'abonents':

(2)
with p (acode, sum) as (
select acode, sum
from pays where packno=:packno
)
select p.acode, p.sum, a.name as name
from p
join abonents_spec a on p.acode=a.acode
union all
select p.acode, p.sum, cast(NULL as varchar(80)) as name
from p
where not exists (select 1 from abonents_spec a on p.acode=a.acode);

With (2) I had ~ 2 or 3 times worse performance then (1) with using table
'abonents' instead of view 'abonents_spec', but it is definitely faster then
(1).
What do you think about this?

Thanks in advance,
Mark.
Nov 12 '05 #1
10 2043

"Mark Barinstein" <ma**@NO.SPAM.ANY.MOREcrk.vsi.ru> wrote in message
news:c6***********@serv.vrn.ru...
Hello.

W2K, db2 v7, fp11.

Given:

create table pays (
acode integer not null,
packno smallint not null,
sum decimal(15, 2) not null
);
create index pays1 on pays(packno);

create table abonents (
acode integer not null primary key,
name varchar(80) not null,
specauth smallint not null
);

create view abonents_spec (acode, name) as
select acode, case when specauth<2 then name else '*' end
from abonents;

I have to issue this query:

(1)
select p.acode, p.sum, a.name
from pays p
left join abonents_spec a on p.acode=a.acode
where p.packno=:packno;

In this case I have index scan of 'pays' (as expected) and TABLE scan of
'abonents', but in case of using in (1) inner join or left join with table 'abonents' instead of view 'abonents_spec' I get expected INDEX scan of
tabel 'abonents'!!!
Tables are about 200 000 rows. Statistics is OK. There is no special
distirbution of data in these tables.
I have tried all optimization levels.
I think it is very strange behavior of the optimizer...

I had to rewrite (1) with emulation of the left join to make optimizer use
index scan of table 'abonents':

(2)
with p (acode, sum) as (
select acode, sum
from pays where packno=:packno
)
select p.acode, p.sum, a.name as name
from p
join abonents_spec a on p.acode=a.acode
union all
select p.acode, p.sum, cast(NULL as varchar(80)) as name
from p
where not exists (select 1 from abonents_spec a on p.acode=a.acode);

With (2) I had ~ 2 or 3 times worse performance then (1) with using table
'abonents' instead of view 'abonents_spec', but it is definitely faster then (1).
What do you think about this?

Thanks in advance,
Mark.


Mark,

It appears that the unique index on abonets (implicitly created because of
the primary key definition) isn't seen by the optimizer through the view.

I was able to reproduce this bizarre behaviour on v72 FP11.
On v81 FP5 the correct behaviour (IXSCAN on abonents) is seen when using the
view.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab

Nov 12 '05 #2

"Mark Barinstein" <ma**@NO.SPAM.ANY.MOREcrk.vsi.ru> wrote in message
news:c6***********@serv.vrn.ru...
Hello.

W2K, db2 v7, fp11.

Given:

create table pays (
acode integer not null,
packno smallint not null,
sum decimal(15, 2) not null
);
create index pays1 on pays(packno);

create table abonents (
acode integer not null primary key,
name varchar(80) not null,
specauth smallint not null
);

create view abonents_spec (acode, name) as
select acode, case when specauth<2 then name else '*' end
from abonents;

I have to issue this query:

(1)
select p.acode, p.sum, a.name
from pays p
left join abonents_spec a on p.acode=a.acode
where p.packno=:packno;

In this case I have index scan of 'pays' (as expected) and TABLE scan of
'abonents', but in case of using in (1) inner join or left join with table 'abonents' instead of view 'abonents_spec' I get expected INDEX scan of
tabel 'abonents'!!!
Tables are about 200 000 rows. Statistics is OK. There is no special
distirbution of data in these tables.
I have tried all optimization levels.
I think it is very strange behavior of the optimizer...

I had to rewrite (1) with emulation of the left join to make optimizer use
index scan of table 'abonents':

(2)
with p (acode, sum) as (
select acode, sum
from pays where packno=:packno
)
select p.acode, p.sum, a.name as name
from p
join abonents_spec a on p.acode=a.acode
union all
select p.acode, p.sum, cast(NULL as varchar(80)) as name
from p
where not exists (select 1 from abonents_spec a on p.acode=a.acode);

With (2) I had ~ 2 or 3 times worse performance then (1) with using table
'abonents' instead of view 'abonents_spec', but it is definitely faster then (1).
What do you think about this?

Thanks in advance,
Mark.


Mark,

It appears that the unique index on abonets (implicitly created because of
the primary key definition) isn't seen by the optimizer through the view.

I was able to reproduce this bizarre behaviour on v72 FP11.
On v81 FP5 the correct behaviour (IXSCAN on abonents) is seen when using the
view.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab

Nov 12 '05 #3
It appears that the unique index on abonets (implicitly created because of
the primary key definition) isn't seen by the optimizer through the view.

I was able to reproduce this bizarre behaviour on v72 FP11.
On v81 FP5 the correct behaviour (IXSCAN on abonents) is seen when using the view.


Matt,

What shall I do, wait for fp12 or forget about v7 at all?
In our project there are a lot of such queries...

Mark.
Nov 12 '05 #4
It appears that the unique index on abonets (implicitly created because of
the primary key definition) isn't seen by the optimizer through the view.

I was able to reproduce this bizarre behaviour on v72 FP11.
On v81 FP5 the correct behaviour (IXSCAN on abonents) is seen when using the view.


Matt,

What shall I do, wait for fp12 or forget about v7 at all?
In our project there are a lot of such queries...

Mark.
Nov 12 '05 #5
If you have the choice you should go to V8.1 anyway.
V7.2 will go out of support pretty soon.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6
If you have the choice you should go to V8.1 anyway.
V7.2 will go out of support pretty soon.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #7
Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c6**********@hanover.torolab.ibm.com>...
If you have the choice you should go to V8.1 anyway.
V7.2 will go out of support pretty soon.

Cheers
Serge


Do you know when FP12 will come out for V7?
Scot
Nov 12 '05 #8
Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c6**********@hanover.torolab.ibm.com>...
If you have the choice you should go to V8.1 anyway.
V7.2 will go out of support pretty soon.

Cheers
Serge


Do you know when FP12 will come out for V7?
Scot
Nov 12 '05 #9
Probably late May or early June.

Scot wrote:
Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c6**********@hanover.torolab.ibm.com>...
If you have the choice you should go to V8.1 anyway.
V7.2 will go out of support pretty soon.

Cheers
Serge

Do you know when FP12 will come out for V7?
Scot


Nov 12 '05 #10
Probably late May or early June.

Scot wrote:
Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c6**********@hanover.torolab.ibm.com>...
If you have the choice you should go to V8.1 anyway.
V7.2 will go out of support pretty soon.

Cheers
Serge

Do you know when FP12 will come out for V7?
Scot


Nov 12 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Ryan | last post by:
Bit of an obscure one here, so please bear with me. I have two copies of a database which should be identical. Both have a complex view which is identical. I can open the views and the data is as...
2
by: Carlo Paccanoni | last post by:
I tried this: use northwind go SELECT OrderDate FROM Orders WHERE OrderDate > '19950101' see the query plan? ok
1
by: Vinny | last post by:
Can anyone help me with this strange problem please? I have a stored procedure, with a parameter defined as a uniqueidentifier. The procedure does a select with a number of joins, and filters...
5
by: Adam Kucharski | last post by:
Hello !!! IBM AIX 5.1 DB2 UDB WSE v.8.1.4 (with FP4) (Polish) DiagLevel 4 Client: Windows XP (Polish) Problem: 2003-12-17-17.58.50.853952 Instance:db2inst1 Node:000...
14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
5
by: sql-db2-dba | last post by:
We have DB2 UDB v8.1 fixpak3 on AIX 5. Production and Development configuarations (at least for DB2) are identical albeit production is a 2-way server while development has only one processor....
22
by: Marc Mones | last post by:
Hello, I'working with IBM DB2 V8.1 and CLI/ODBC. I've got a problem with the following statement: ******************************************************************************** SELECT...
6
by: Ryan | last post by:
I came across a situation that I've been unable to explain and was hoping somebody had an answer: I had written an update query which was taking about 8 seconds to run and considered it too...
4
by: Praveen_db2 | last post by:
Hi All I am getting strange errors in my db2diag.log can any one tell me what these errors mean?? Following is the code from my db2diag.log...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.