同一SQL语句的不同访问计划（慢和快）的问题 [英] problem with different access plan (slow & fast) for the same SQL-Statement

问题描述

您好，


我正在使用IBM DB2 V8.1和CLI / ODBC。

声明之后我有一个问题

：


********** **************************************** ********** ********************

SELECT S_ART，S_SPRACHE，S_MANDANT，S_NR，S_SUB，S_OWNER，S_SATZ

来自SY0001_00005

WHERE S_ART =？ AND S_SPRACHE =？和S_MANDANT =？ AND S_NR =？ AND

S_SUB =

？

或S_ART =？ AND S_SPRACHE =？和S_MANDANT =？ AND S_NR =？ AND

S_SUB> ？

或S_ART =？ AND S_SPRACHE =？和S_MANDANT =？ AND S_NR> ？

或S_ART =？ AND S_SPRACHE =？和S_MANDANT> ？

或S_ART =？ AND S_SPRACHE> ？

或S_ART> ？


订购S_ART，S_SPRACHE，S_MANDANT，S_NR，S_SUB FETCH首选1行

仅

***** ********************************************* ***** *************************


表有两个索引表：


第一个索引SQL0505250858084与

S_ART，S_SPRACHE，S_MANDANT，S_NR，S_SUB：

- 这是表的主要索引）

- 此索引相同ORDER-BY-Clause


第二个索引SY0001_00005_KEY与

S_ART，S_MANDANT，S_NR，S_SUB，S_SPRACHE

- 使用CREATE UNIQUE INDEX创建


有时声明运行速度很快，有时运行速度非常慢

（因子20-30）

和EXPLAIN-informations（使用db2exfmt）显示不同的访问权限

计划

快速和慢速版本。

如果我分析EXPLAIN的执行树，我可以看到，快速

版本使用第一个键，它与

ORDER-BY-Clause相同。


但是慢版本使用第二个键并使用

SORT-Operations

表示语句的每个konjunction / AND条件。这似乎很慢。


有人有想法吗？我不知道，哪个因素会影响这些选择

的访问计划。如果有人感兴趣，我可以通过邮件发送

执行树（由db2exfmt生成）。但这些编辑框的树木很大。


以下是访问计划的不同部分的两个提取
< br $>
*********************************

FAST-计划


访问计划：

-----------

总费用：5174,84 < br $>
查询度：1

解决方案

Marc Mones写道：

你好，

我正在使用IBM DB2 V8.1和CLI / ODBC。
以下
声明我有问题：

********************** **************************** ********************** ********
SELECT S_ART，S_SPRACHE，S_MANDANT，S_NR，S_SUB，S_OWNER，S_SATZ
FROM SY0001_00005
WHERE S_ART =？ AND S_SPRACHE =？和S_MANDANT =？ AND S_NR =？ AND
S_SUB =
？
或S_ART =？ AND S_SPRACHE =？和S_MANDANT =？ AND S_NR =？ AND
S_SUB> ？
或S_ART =？ AND S_SPRACHE =？和S_MANDANT =？ AND S_NR> ？
或S_ART =？ AND S_SPRACHE =？和S_MANDANT> ？
或S_ART =？ AND S_SPRACHE> ？
或S_ART> ？

按S_ART，S_SPRACHE，S_MANDANT，S_NR，S_SUB FETCH排序第1行仅

您可以更改查询吗？

SELECT S_ART，S_SPRACHE，S_MANDANT，S_NR，S_SUB，S_OWNER，S_SATZ

来自SY0001_00005

WHERE S_ART> =？ AND

（S_ART =？AND S_SPRACHE =？AND S_MANDANT =？AND S_NR =？AND

S_SUB =？

或S_ART =？AND S_SPRACHE =？AND S_MANDANT =？AND S_NR =？AND

S_SUB>？

OR S_ART =？AND S_SPRACHE =？AND S_MANDANT =？AND S_NR>？

或S_ART =？AND S_SPRACHE =？AND S_MANDANT>？

或S_ART =？AND S_SPRACHE>？

或S_ART>？ ）


订购S_ART，S_SPRACHE，S_MANDANT，S_NR，S_SUB FETCH首选1行

仅


Bernard我会很喜欢这个帖子....


干杯

Serge

-

Serge Rielau

DB2 SQL编译器开发

IBM多伦多实验室

Marc Mones写道：

您好，

我正在使用IBM DB2 V8.1和CLI / ODBC。
以下
声明我有问题：

********************** **************************** ********************** ********
SELECT S_ART，S_SPRACHE，S_MANDANT，S_NR，S_SUB，S_OWNER，S_SATZ
FROM SY0001_00005
WHERE S_ART =？ AND S_SPRACHE =？和S_MANDANT =？ AND S_NR =？ AND
S_SUB =
？
或S_ART =？ AND S_SPRACHE =？和S_MANDANT =？ AND S_NR =？ AND
S_SUB> ？
或S_ART =？ AND S_SPRACHE =？和S_MANDANT =？ AND S_NR> ？
或S_ART =？ AND S_SPRACHE =？和S_MANDANT> ？
或S_ART =？ AND S_SPRACHE> ？
或S_ART> ？

按S_ART，S_SPRACHE，S_MANDANT，S_NR，S_SUB FETCH排序首先1行

***************** ********************************* ***************** *************

不确定，但感觉你的谓词并没有达到预期的效果。

（了解运营商优先权）

这是什么意思？


WHERE

（S_ART =？ AND S_SPRACHE =？AND S_MANDANT =？AND S_NR =？AND

S_SUB =？）

OR

（S_ART =？AND S_SPRACHE =？ AND S_MANDANT =？AND S_NR =？AND

S_SUB =？）

OR

（S_ART =？AND S_SPRACHE =？AND S_MANDANT =？ AND S_NR>？）

或

（S_ART =？AND S_SPRACHE =？AND S_MANDANT>？）

或

（S_ART =？AND S_SPRACHE>？）

或

（S_ART>？）

>你能改变查询吗？

SELECT S_ART，S_SPRACHE，S_MANDANT，S_NR，S_SUB，S_OWNER，S_SATZ
FROM SY0001_00005
WHERE S_ART> =？ AND
（S_ART =？AND S_SPRACHE =？AND S_MANDANT =？AND S_NR =？AND
S_SUB =？
OR S_ART =？AND S_SPRACHE =？AND S_MANDANT =？AND S_NR =？AND
S_SUB>？
或者S_ART =？AND S_SPRACHE =？AND S_MANDANT =？AND S_NR>？
或者S_ART =？AND S_SPRACHE =？AND S_MANDANT>？
或S_ART =？AND S_SPRACHE>？
或S_ART>？）

按S_ART，S_SPRACHE，S_MANDANT，S_NR，S_SUB FETCH排序第1行仅
Bernard会喜欢这个帖子....

干杯
Serge
-
Serge Rielau
DB2 SQL编译器开发
IBM多伦多实验室

我会尝试更改它。在我们的例子中，我们也使用了

ISAM / Btrieve-Database。我们需要这个陈述来模仿一个B B B B B B B B B B B B G G G G G G G G G G G G G在串联的字节索引上

由字段S_ART，S_SPRACHE，S_MANDANT，S_NR，S_SUB组成。我们

需要这个

的应用程序，它们构建在ISAM接口上。这些

应用程序

向我们发送GET_GREATER_EQUAL - 关键值请求。我们必须将这些

请求转换为SQL或者更确切地说是关系数据库。

我希望这个解释是可以理解的。


谢谢！


Marc

Hello,

I''working with IBM DB2 V8.1 and CLI/ODBC. I''ve got a problem with the
following
statement:

************************************************** ******************************
SELECT S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB, S_OWNER, S_SATZ
FROM SY0001_00005
WHERE S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
S_SUB =
?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
S_SUB > ?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR > ?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT > ?
OR S_ART = ? AND S_SPRACHE > ?
OR S_ART > ?

ORDER BY S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB FETCH FIRST 1 ROWS
ONLY
************************************************** ******************************

There are two indexes for the table concerned table:

first index "SQL0505250858084" with
S_ART,S_SPRACHE,S_MANDANT,S_NR,S_SUB:
- this is the primary index of the table)
- this index is identical to the ORDER-BY-Clause

second index "SY0001_00005_KEY" with
S_ART,S_MANDANT,S_NR,S_SUB,S_SPRACHE
- created with "CREATE UNIQUE INDEX"

Sometimes the statement runs fast and sometimes it runs very slow
(factor 20-30)
and the EXPLAIN-informations (with db2exfmt) shows different access
plans for
fast and slow version.
If I analyse the execution-tree of EXPLAIN, i can see, that the fast
version only uses the first key, which is identical to the
ORDER-BY-Clause.

But the slow version uses the second key and makes a IXSCAN with
SORT-Operations
for each konjunction/AND-condition of the statement. This seems to be
very slow.

Has anybody an idea? I don''t know, which factor affects these choice
of access plans. If anybody is interested, I can send the
execution-trees (generated by db2exfmt) by mail. But the trees are to
big for these edit-box.

Here are two extractions with the different part of the access-plans

*********************************
FAST-PLAN

Access Plan:
-----------
Total Cost: 5174,84
Query Degree: 1

解决方案

Marc Mones wrote:

Hello,

I''working with IBM DB2 V8.1 and CLI/ODBC. I''ve got a problem with the
following
statement:

************************************************** ******************************
SELECT S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB, S_OWNER, S_SATZ
FROM SY0001_00005
WHERE S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
S_SUB =
?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
S_SUB > ?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR > ?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT > ?
OR S_ART = ? AND S_SPRACHE > ?
OR S_ART > ?

ORDER BY S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB FETCH FIRST 1 ROWS
ONLY

Can you change the query?
SELECT S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB, S_OWNER, S_SATZ
FROM SY0001_00005
WHERE S_ART >= ? AND
(S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
S_SUB = ?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
S_SUB > ?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR > ?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT > ?
OR S_ART = ? AND S_SPRACHE > ?
OR S_ART > ?)

ORDER BY S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB FETCH FIRST 1 ROWS
ONLY

Bernard is going to love this post....

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Marc Mones wrote:

Hello,

I''working with IBM DB2 V8.1 and CLI/ODBC. I''ve got a problem with the
following
statement:

************************************************** ******************************
SELECT S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB, S_OWNER, S_SATZ
FROM SY0001_00005
WHERE S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
S_SUB =
?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
S_SUB > ?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR > ?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT > ?
OR S_ART = ? AND S_SPRACHE > ?
OR S_ART > ?

ORDER BY S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB FETCH FIRST 1 ROWS
ONLY
************************************************** ******************************

Not sure, but it feels like your predicate isn''t doing what you expect.
(Read about operator precedence)
Is this what you mean?

WHERE
(S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
S_SUB = ?)
OR
(S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
S_SUB = ?)
OR
(S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR > ?)
OR
(S_ART = ? AND S_SPRACHE = ? AND S_MANDANT > ?)
OR
(S_ART = ? AND S_SPRACHE > ?)
OR
(S_ART > ?)

> Can you change the query?

SELECT S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB, S_OWNER, S_SATZ
FROM SY0001_00005
WHERE S_ART >= ? AND
(S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
S_SUB = ?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
S_SUB > ?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR > ?
OR S_ART = ? AND S_SPRACHE = ? AND S_MANDANT > ?
OR S_ART = ? AND S_SPRACHE > ?
OR S_ART > ?)

ORDER BY S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB FETCH FIRST 1 ROWS
ONLY

Bernard is going to love this post....

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

I will try to change it. In our case we work with a
ISAM/Btrieve-Database, too. We need this statement to emulate an
Btrieve-Operation "GET-GREATER OR EQUAL" on an concatenated Byte-Index
consisting of the fields S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB. We
need this
for applications, which are builded on the ISAM-Interface. These
applications
send us "GET_GREATER_EQUAL"-Requests for Key-Values. We have to
transform these
request to SQL or rather relational database.
I hope, that this explanation is comprehensible.

Thank you!

Marc

这篇关于同一SQL语句的不同访问计划（慢和快）的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！