problem with different access plan (slow & fast) for the same SQL-Statement

[Marc Mones]

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

[Serge Rielau]

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

[Ian]

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 > ?)

[mmones]

> 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

[mmones]

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

[mmones]

yes

[mmones]

I've got still one question. Who is Bernard and why will he love this
post?

[mmones]

Still one question? Who ist Bernard and why will he love this post?

[mmones]

I've got still one question. Who is Bernard and why will he love this
post?

[Mark A]

<mm****@web.de> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

I've got still one question. Who is Bernard and why will he love this
post?

We have seen the question and you don't need to keep repeating it.
Occasionally, the people in the IBM lab in Toronto have other work to do
beside immediately answering your questions.

Bernard probably works in the IBM lab in Toronto where DB2 is developed.

[Serge Rielau]

Mark A wrote:

<mm****@web.de> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

I've got still one question. Who is Bernard and why will he love this
post?

We have seen the question and you don't need to keep repeating it.
Occasionally, the people in the IBM lab in Toronto have other work to do
beside immediately answering your questions.

Bernard probably works in the IBM lab in Toronto where DB2 is developed.

Bernard is a consultant psoting here, who is trying to convince DB2 Dev
since many years to support row-comparsions like:
(c1, c2, c3) >= (?, ?, ?) or
(c1, c2, c3) BETWEEN (?, ?, ?) AND (?, ?, ?)

If I'm not mistaken his argument matches yours and he'll be glad to have
another customer requesting it.

Cheers
Serge

PS: Please let me know through this group or by email whether the
workaround does it. I have great difficulty judging this priority of
this feature request.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[ian.bjorhovde]

Does using the syntax I give result in consistent access times?

[Bernard Dhooghe]

Hello,

The construct row-value constructors (c1,c2,c3) >= (?,?,?) is part of
SQl-92 and can help in what I call human-search (compared to the
ultimate query) when logical keys are composite.

The posting here illustrates it, the row-value clause allows to express
semantically what is needed with a direct hint I would say for the
optimizer.

After all these years I would be very glad to see it in DB2 UDB LUW.
Indeed.

But after all these years I have come to the conclusion that it would
be only a partial solution (I would already be glad with and a lot of
other developers and end-users also), it is related to scrollable
cursors and cursor positioning.

In the first SQL standard cursors where only fetch forward cursors.

Then in SQL-92 scrollable cursors where introduced but why should the
cursor be at the beginning of the set when scrollable cursors are
available? This is logic from non-scrollable cursors time, if I can
scroll backwards, it must bee possible to do it (without return-code
100) directly after open cursor.

So I see the general solution as:

declare cursor cu1 for select ... from ... where ... order
by ... (the where can contain a row-value clause, where and order by
are optional as alwas, multiple tables can be involved)

open cursur cu1 position cursor where <row-value
constructor clause>

(where clause optional, must match the order by in the
select)

fetch cu1 (next but also previous possible directly after
open, as the cursor is somewhere in the set after open).

A subset of this capability would allow to solve the posting:
select .. from SY0001_00005 where (S_ART , S_SPRACHE,
S_MANDANT, S_NR ,) >= (?,?,?,?,?) order by S_ART , S_SPRACHE,
S_MANDANT, S_NR ...
(just 1 table, optimezer sees what is wanted)

open cursor
(no cursor positioning)

fetch
(beginning of set as no cursor positioning in open)
Not all programmers write in the newsgroup, the question occured more
than once (search in comp.databases.ibm-db2 on: tuning browse
queries,DB2 is not using the index), the row-value constructor clause
is part of the SQL-92 standard, it allows to write interactive search
capabilities when logical indexes are composite, it is just part of the
users world and programmers must be able to map the reality when using
DB2 UDB.
Bernard Dhooghe

[Marc Mones]

Hello Serge,

I have tested your suggested statement with the AND-Case and the () of
the
OR-Cases. But the execution time didn't change. It is still very slow.
I did not yet understand, how I can cause the faster access plan.
Sometime, DB2
chooses this access plan. So it must exist a better solution. It is
possible,
that I can send you the whole access plan of the slow (old), slow
(new, your suggestion)and fast access plan by mail?

It is possible, that some database parameters of db2 affect the choice
of access plan?

Thanks!

Marc

P.S.: MS SQL-Server runs articulately faster with your suggested
SQL-Statement.
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<3g************@individual.net>...

Mark A wrote:

<mm****@web.de> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

I've got still one question. Who is Bernard and why will he love this
post?

We have seen the question and you don't need to keep repeating it.
Occasionally, the people in the IBM lab in Toronto have other work to do
beside immediately answering your questions.

Bernard probably works in the IBM lab in Toronto where DB2 is developed.

Bernard is a consultant psoting here, who is trying to convince DB2 Dev
since many years to support row-comparsions like:
(c1, c2, c3) >= (?, ?, ?) or
(c1, c2, c3) BETWEEN (?, ?, ?) AND (?, ?, ?)

If I'm not mistaken his argument matches yours and he'll be glad to have
another customer requesting it.

Cheers
Serge

PS: Please let me know through this group or by email whether the
workaround does it. I have great difficulty judging this priority of
this feature request.

[Marc Mones]

Hello Serge,

I've tested your suggested access plan with the additional AND-Case.
But it is still slow and doesn't change the execution time. I can not
yet understand, what affects the choice of access plan and there must
exist a possibility to speed up the access plan. Sometimes the same
statement was very fast.

It is possible, that some database parameters affects the choice of
access plan?

It is possbile to send you the whole access plan for slow (old), slow
(new, your suggested SQL-Statement) and fast version by mail?

Thanks!

Marc

P.S.: the following point is funny: your suggested SQL-Statement
speeds up
MS SQL Server articulately. Sorry! But we need DB2 as well.
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<3g************@individual.net>...

Mark A wrote:

<mm****@web.de> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

I've got still one question. Who is Bernard and why will he love this
post?

We have seen the question and you don't need to keep repeating it.
Occasionally, the people in the IBM lab in Toronto have other work to do
beside immediately answering your questions.

Bernard probably works in the IBM lab in Toronto where DB2 is developed.

Bernard is a consultant psoting here, who is trying to convince DB2 Dev
since many years to support row-comparsions like:
(c1, c2, c3) >= (?, ?, ?) or
(c1, c2, c3) BETWEEN (?, ?, ?) AND (?, ?, ?)

If I'm not mistaken his argument matches yours and he'll be glad to have
another customer requesting it.

Cheers
Serge

PS: Please let me know through this group or by email whether the
workaround does it. I have great difficulty judging this priority of
this feature request.

[mmones]

thank you for your these informations

[Serge Rielau]

Marc Mones wrote:

Hello Serge,

I've tested your suggested access plan with the additional AND-Case.
But it is still slow and doesn't change the execution time. I can not
yet understand, what affects the choice of access plan and there must
exist a possibility to speed up the access plan. Sometimes the same
statement was very fast.

It is possible, that some database parameters affects the choice of
access plan?

It is possbile to send you the whole access plan for slow (old), slow
(new, your suggested SQL-Statement) and fast version by mail?

Thanks!

Marc

P.S.: the following point is funny: your suggested SQL-Statement
speeds up
MS SQL Server articulately. Sorry! But we need DB2 as well.
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<3g************@individual.net>...

Mark A wrote:

<mm****@web.de> wrote in message
news:11*********************@g43g2000cwa.google groups.com...
I've got still one question. Who is Bernard and why will he love this
post?
We have seen the question and you don't need to keep repeating it.
Occasionally, the people in the IBM lab in Toronto have other work to do
beside immediately answering your questions.

Bernard probably works in the IBM lab in Toronto where DB2 is developed.

Bernard is a consultant psoting here, who is trying to convince DB2 Dev
since many years to support row-comparsions like:
(c1, c2, c3) >= (?, ?, ?) or
(c1, c2, c3) BETWEEN (?, ?, ?) AND (?, ?, ?)

If I'm not mistaken his argument matches yours and he'll be glad to have
another customer requesting it.

Cheers
Serge

PS: Please let me know through this group or by email whether the
workaround does it. I have great difficulty judging this priority of
this feature request.

Sure you can send it by mail, as long as you understand that my answre
is on a best efford basis. If I get pre-empted or simply forget because
I'm swamped.. that'll be life... PMR is a different (tracked) matter.
Anyway, I did soem poking with backstage and I'm told teh issue apears
to be within "index OR-ing" in teh optimizer. It was proposed to ensure
you have "column-group statistics" collected to ensure the optimizer is
aware of correlation between the index members.

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

[mmones]

Hello Serge,

ok, I will send the 3 extractions from db2exfmt by mail (to
sr*****@ca.ibm.com).

I built in the "column-group statistics", but there is now effect. Is
there anything else to consider with making
statistics?
Thank you for your efforts in advance!

Marc

[mmones]

Another point is peculiar, too. The plans, which are slow in reality,
have got a better TOTAL-COST approximation in EXPLAIN-informations.

Marc

[mmones]

Repeating this mail was a little mistake in using the
GOOGLE-Group-Menu. I didn't really want this. sorry!

Marc

[Serge Rielau]

mm****@web.de wrote:

Another point is peculiar, too. The plans, which are slow in reality,
have got a better TOTAL-COST approximation in EXPLAIN-informations.

Marc

I got someone form the optimizer to help. Staring at the beast by trick
should have stabilized the plan.
I am someone confused why you are using a scrollabel cursor (for 1 row!)
Is this puprose or are you the victim of some client default (Rumours
have it JDBC has the nasty habit to default every cursor to scrollable)

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

[mmones]

Makes it sense, to contact the official german IBM DB2 support
concerning this problem? but I think they don't have your know-how, or?

Thank you for your efforts in advance!

Marc

[Serge Rielau]

mm****@web.de wrote:

Makes it sense, to contact the official german IBM DB2 support
concerning this problem? but I think they don't have your know-how, or?

*lol*As always when you call any customer support anywhere. You roll
your dice, you take your chances.
We DO have "darn good" (the words of the optimizer team lead) folks in
European support.
Given that the plans have wildly different system specs you'll have to
overcome that hurdle of "you're comparing oranges with apples.. go
away!" first.
Let's run this through "L0" support for now and see where it goes.
As long as I can keep backstage interested enough to not point to the
virtual clock.

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