469,273 Members | 1,760 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,273 developers. It's quick & easy.

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 1846

"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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Ryan | last post: by
2 posts views Thread by Carlo Paccanoni | last post: by
5 posts views Thread by Adam Kucharski | last post: by
14 posts views Thread by Sean C. | last post: by
4 posts views Thread by Praveen_db2 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.