奇怪的访问计划 [英] Strange access plan
问题描述
你好。
W2K，db2 v7，fp11。
鉴于：
create table pays（
acode integer not null，
packno smallint not null，
sum decimal（15,2）not null
）;
创建索引pays1 on pays（packno）;
创建表格abonents（
acode整数not null主键，
名称varchar（80）not null，
specauth smallint not null
）;
创建视图abonents_spec（acode，name）为
选择acode，例如当specauth< 2然后命名为''*''结束
来自abonents;
我必须发出这个查询：
（1）
选择p.acode，p.sum ，a.name
来自支付p
左边加入abonents_spec a on p.acode = a.acode
其中p.packno =：packno ;
在这种情况下，我有付费的索引扫描（按预期）和
''abonents''的TABLE扫描，但是案件 在（1）内连接或左连接中使用表
''abonents''而不是查看''abonents_spec''我得到预期的INDEX扫描
tabel' 'abonents''!!!
表约有20万行。统计还可以。这些表中没有特殊的数据分配。
我已经尝试了所有优化级别。
我认为这是非常奇怪的行为优化器......
我必须重写（1）模拟左连接以使优化器使用
索引扫描表''abonents' '：
（2）
，p（acode，sum）为（
select acode，sum
来自payno = packno =：packno
）
选择p.acode，p.sum，a.name作为名称
来自p
加入abonents_spec a on p.acode = a.acode
union all
select p.acode，p.sum，cast（ NULL作为varchar（80））作为名称
来自p
哪里不存在（从p.acode = a.acode上的abonents_spec a中选择1）;
随着（2）我的表现差得多2或3倍然后（1）使用表格
''abonents''而非查看''abonents_spec''，但它绝对比
（1）更快。
您如何看待这个？
提前致谢，
马克。
" Mark Barinstein" <毫安** @ NO.SPAM.ANY.MOREcrk.vsi.ru>在消息中写道
新闻：c6 *********** @ serv.vrn.ru ...你好。
W2K，db2 v7，fp11。
给定：
创建表支付（
acode整数不为null，
packno smallint not null，
和十进制（15,2）不为
）;
创建索引pays1 on pays（packno）;
创建表格abonents（
acode integer not not null主键，
名称varchar（80）不为null，
specauth smallint not null
）;
创建视图abonents_spec（acode，name）为
选择acode，当specauth< 2然后命名为''*''结束
来自abonents;
我必须发出此查询：
（1 ）
选择p.acode，p.sum，a.name
从支付p
左连接abonents_spec a on p.acode = a.acode
其中p.packno =： packno;
在这种情况下，我有''pays''的索引扫描（如预期的那样）和
''abonents''的TABLE扫描，但是在（1）中使用的情况下内连接或左边j使用
表''abonents''而不是查看''abonents_spec''我得到预期的INDEX扫描
tabel''abonents''!!!
表大约是20万行。统计还可以。这些表中没有特别的数据分布。
我已经尝试了所有优化级别。
我认为优化器的行为非常奇怪......
我不得不重写（1）模拟左连接以使优化器使用
索引扫描表''abonents''：
（2）
with p（ acode，sum）as（
select acode，sum
from pays where packno =：packno
）
选择p.acode，p.sum，a.name作为名称
从p
加入abonents_spec a on p.acode = a.acode
union all
选择p.acode，p.sum，cast（NULL作为varchar（80））作为名称<来自p
哪里不存在（从p.acode = a.acode上的abonents_spec a中选择1）;
随着（2）我的性能下降了~2或3倍（1）使用表
''abonents''而不是查看''abonents_spec''，但它肯定更快
然后（1）。
您如何看待这个？
提前致谢，
Mark。
Mark，
似乎优化器没有看到abonets上的唯一索引（由于
主键定义而隐式创建）视图。
我能够在v72 FP11上重现这种奇怪的行为。
在v81 FP5上，使用时可以看到正确的行为（使用上的IXSCAN）
视图。
-
Matt Emmerton
DB2 OLTP性能
IBM多伦多实验室
" Mark Barinstein" <毫安** @ NO.SPAM.ANY.MOREcrk.vsi.ru>在消息中写道
新闻：c6 *********** @ serv.vrn.ru ...你好。
W2K，db2 v7，fp11。
给定：
创建表支付（
acode整数不为null，
packno smallint not null，
和十进制（15,2）不为
）;
创建索引pays1 on pays（packno）;
创建表格abonents（
acode integer not not null主键，
名称varchar（80）不为null，
specauth smallint not null
）;
创建视图abonents_spec（acode，name）为
选择acode，当specauth< 2然后命名为''*''结束
来自abonents;
我必须发出此查询：
（1 ）
选择p.acode，p.sum，a.name
从支付p
左连接abonents_spec a on p.acode = a.acode
其中p.packno =： packno;
在这种情况下，我有''pays''的索引扫描（如预期的那样）和
''abonents''的TABLE扫描，但是在（1）中使用的情况下内连接或左边j使用
表''abonents''而不是查看''abonents_spec''我得到预期的INDEX扫描
tabel''abonents''!!!
表大约是20万行。统计还可以。这些表中没有特别的数据分布。
我已经尝试了所有优化级别。
我认为优化器的行为非常奇怪......
我不得不重写（1）模拟左连接以使优化器使用
索引扫描表''abonents''：
（2）
with p（ acode，sum）as（
select acode，sum
from pays where packno =：packno
）
选择p.acode，p.sum，a.name作为名称
从p
加入abonents_spec a on p.acode = a.acode
union all
选择p.acode，p.sum，cast（NULL作为varchar（80））作为名称<来自p
哪里不存在（从p.acode = a.acode上的abonents_spec a中选择1）;
随着（2）我的性能下降了~2或3倍（1）使用表
''abonents''而不是查看''abonents_spec''，但它肯定更快
然后（1）。
您如何看待这个？
提前致谢，
Mark。
Mark，
似乎优化器没有看到abonets上的唯一索引（由于
主键定义而隐式创建）视图。
我能够在v72 FP11上重现这种奇怪的行为。
在v81 FP5上，使用时可以看到正确的行为（使用上的IXSCAN）
视图。
-
Matt Emmerton
DB2 OLTP性能
IBM多伦多实验室
似乎abonets上的唯一索引（由于
主键定义。
我能够在v72 FP11上重现这种奇怪的行为。
在v81 FP5上正确的行为（IXSCAN）在使用
视图时可以看到。）
马特，
我该怎么做，等对于fp12还是忘记了v7？
在我们的项目中有很多这样的问题...
马克。
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 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" <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
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.
这篇关于奇怪的访问计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
