Strange access plan

[Mark Barinstein]

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.

[Matt Emmerton]

"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

[Matt Emmerton]

"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

[Mark Barinstein]

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.

[Mark Barinstein]

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.

[Serge Rielau]

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

[Serge Rielau]

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

[Scot]

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

[Scot]

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

[Blair Adamache]

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

[Blair Adamache]

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