Db2 - L

Laila,

This is a difficult one to give an accurate anwer to, without seeing more explain details.
E.g., are you sure both join variants (with the two different table orders) are both nested loop joins (METHOD=1 in plan_table)?
If it's a merge-scan join, the join table order makes (almost) no difference since in that case both tables are pre-sorted (or accessed in a pre-sorted way through an index) and then both are traversed just a single time (no loop inside a loop) in parallel.

Anyhow, if the optimizer has all necessary information (specifically, up-to-date runstats) it will pick the best possible access path. Which *could* indeed be different on different subsystems since e.g. the size of the sort pool could be different.

If you really want to avoid pre-sorting, hence force a nested-loop join, the simplest way to do this is by adding "optimize for 1 row" at the end of the join query. But only do this after a thorough performance analysis, and after having verified there are no stale runstats data in the catalog!

------------------------------
Peter Vanroose
ABIS Training & Consulting
https://www.abis.be/html/enDB2Calendar.html
------------------------------

Original Message:
Sent: Jun 17, 2022 10:00 PM
From: Leila Hosseini
Subject: Trying to change order to join tables by using Runstat

Hi

I have two table those contributing in one select statement.

One table has 15000 rows , other one has 88 million rows and has 15 partitions,

Both tables have proper indices those can be used in this query.

The issue is Db2 optimizer using wrong order to join the tables. It starts with bigger table and scanning small table for 88 million times! It uses unappreciated indices.

I have tried Runstat to make optimizer aware of indices, and also tables already have statistics.

In one of the Db2 subsystem RUNSTA INdex worked, to change order of joining tables.but for the rest of the Db2 subsystems not worked.All subsystems look the same.

I am confuse why Db2 behavior is different?

Thanks for any help 

Regards 

Leila 

Sent from Yahoo Mail for iPhone

Original Message:
Sent: 6/17/2022 10:00:00 PM
From: Leila Hosseini
Subject: Trying to change order to join tables by using Runstat

Hi

I have two table those contributing in one select statement.

One table has 15000 rows , other one has 88 million rows and has 15 partitions,

Both tables have proper indices those can be used in this query.

The issue is Db2 optimizer using wrong order to join the tables. It starts with bigger table and scanning small table for 88 million times! It uses unappreciated indices.

I have tried Runstat to make optimizer aware of indices, and also tables already have statistics.

In one of the Db2 subsystem RUNSTA INdex worked, to change order of joining tables.but for the rest of the Db2 subsystems not worked.All subsystems look the same.

I am confuse why Db2 behavior is different?

Thanks for any help 

Regards 

Leila 

Sent from Yahoo Mail for iPhone

You might try running runstats with the USE PROFILE option. We've had good results in badly performing sql after runstats with USE PROFILE.

------------------------------
RussellPetersCentral Technology Services
------------------------------

Original Message:
Sent: Jun 17, 2022 10:00 PM
From: Leila Hosseini
Subject: Trying to change order to join tables by using Runstat

Hi

I have two table those contributing in one select statement.

One table has 15000 rows , other one has 88 million rows and has 15 partitions,

Both tables have proper indices those can be used in this query.

The issue is Db2 optimizer using wrong order to join the tables. It starts with bigger table and scanning small table for 88 million times! It uses unappreciated indices.

I have tried Runstat to make optimizer aware of indices, and also tables already have statistics.

In one of the Db2 subsystem RUNSTA INdex worked, to change order of joining tables.but for the rest of the Db2 subsystems not worked.All subsystems look the same.

I am confuse why Db2 behavior is different?

Thanks for any help 

Regards 

Leila 

Sent from Yahoo Mail for iPhone

If you are at a tipping point then I would try an inline CTE hint... I have used them loads of times and they work really really well. For details check out my blog about how it works:

https://www.segus.com/2021-02-hidden-features-of-explain/

It is a really great tool for getting tables in the join order *you* want and not what the optimizer *thinks* is the best! One badly timed runstats and it all goes horribly wrong!!!

Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-



Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
Web http://www.seg.de
Link zur Datenschutzerklärung

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich


Original Message:
Sent: 6/21/2022 10:25:00 AM
From: Russell Peters
Subject: RE: Trying to change order to join tables by using Runstat

You might try running runstats with the USE PROFILE option. We've had good results in badly performing sql after runstats with USE PROFILE.

------------------------------
RussellPetersCentral Technology Services
------------------------------

Original Message:
Sent: Jun 17, 2022 10:00 PM
From: Leila Hosseini
Subject: Trying to change order to join tables by using Runstat

Hi

I have two table those contributing in one select statement.

One table has 15000 rows , other one has 88 million rows and has 15 partitions,

Both tables have proper indices those can be used in this query.

The issue is Db2 optimizer using wrong order to join the tables. It starts with bigger table and scanning small table for 88 million times! It uses unappreciated indices.

I have tried Runstat to make optimizer aware of indices, and also tables already have statistics.

In one of the Db2 subsystem RUNSTA INdex worked, to change order of joining tables.but for the rest of the Db2 subsystems not worked.All subsystems look the same.

I am confuse why Db2 behavior is different?

Thanks for any help 

Regards 

Leila 

Sent from Yahoo Mail for iPhone

Leila,

As I understand from your private message, the chosen join is a nested loop join, with the 88 million row table as outer table.
And his outer table is accessed through a matching index access (on one column, the condition EMPLID = :host-var).
So only a fraction of the 88 million rows are accessed, through the EMPLID index.
Next, the nested loop will access the inner table's 4-column index, apparently in an index-only way.
Over and over again, of course, but that might be an index look-aside access, which essentially means that the index is traversed just once.
And since it's only 15000 rows, that might be an efficient access (even if it's not using index look-aside).

Anyhow, since the optimizer chose this nested loop join with the large, partitioned table as outer table, this access path is "cheaper" than a join with the smaller table as outer table.  If not, the optimizer would have chosen a different access path.

Still, of course, that subset (EMPLID = :hostvar) of the 88 million rows could be a substantial set of rows, and you still might prefer to force the optimizer to start from the smaller table for its join.

This can be done by "discouraging" the use of the EMPLID index on the large table as primary access.
Namely by changing the SELECT statement as follows:

Replace
        ...  EMPLID = :host-var
by
        ... EMPLID = :host-var + 0

(I'm assuming EMPLID is numeric; if not do the following:
        ... EMPLID = :host-var || ''
)

------------------------------
Peter Vanroose
ABIS Training & Consulting
https://www.abis.be/html/enDB2Calendar.html
------------------------------

Original Message:
Sent: Jun 17, 2022 10:00 PM
From: Leila Hosseini
Subject: Trying to change order to join tables by using Runstat

Hi

I have two table those contributing in one select statement.

One table has 15000 rows , other one has 88 million rows and has 15 partitions,

Both tables have proper indices those can be used in this query.

The issue is Db2 optimizer using wrong order to join the tables. It starts with bigger table and scanning small table for 88 million times! It uses unappreciated indices.

I have tried Runstat to make optimizer aware of indices, and also tables already have statistics.

In one of the Db2 subsystem RUNSTA INdex worked, to change order of joining tables.but for the rest of the Db2 subsystems not worked.All subsystems look the same.

I am confuse why Db2 behavior is different?

Thanks for any help 

Regards 

Leila 

Sent from Yahoo Mail for iPhone

Hi!

Also consider using CTE Opthints to lock the table access down... See my other post or my Blog about hidden explain features. It would solve your problem instantly!

Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-



Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: R.Boxwell@seg.de
Web http://www.seg.de
Link zur Datenschutzerklärung

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich


Original Message:
Sent: 6/23/2022 11:15:00 AM
From: Peter Vanroose
Subject: RE: Trying to change order to join tables by using Runstat

Leila,

As I understand from your private message, the chosen join is a nested loop join, with the 88 million row table as outer table.
And his outer table is accessed through a matching index access (on one column, the condition EMPLID = :host-var).
So only a fraction of the 88 million rows are accessed, through the EMPLID index.
Next, the nested loop will access the inner table's 4-column index, apparently in an index-only way.
Over and over again, of course, but that might be an index look-aside access, which essentially means that the index is traversed just once.
And since it's only 15000 rows, that might be an efficient access (even if it's not using index look-aside).

Anyhow, since the optimizer chose this nested loop join with the large, partitioned table as outer table, this access path is "cheaper" than a join with the smaller table as outer table.  If not, the optimizer would have chosen a different access path.

Still, of course, that subset (EMPLID = :hostvar) of the 88 million rows could be a substantial set of rows, and you still might prefer to force the optimizer to start from the smaller table for its join.

This can be done by "discouraging" the use of the EMPLID index on the large table as primary access.
Namely by changing the SELECT statement as follows:

Replace
        ...  EMPLID = :host-var
by
        ... EMPLID = :host-var + 0

(I'm assuming EMPLID is numeric; if not do the following:
        ... EMPLID = :host-var || ''
)

------------------------------
Peter Vanroose
ABIS Training & Consulting
https://www.abis.be/html/enDB2Calendar.html
------------------------------

Original Message:
Sent: Jun 17, 2022 10:00 PM
From: Leila Hosseini
Subject: Trying to change order to join tables by using Runstat

Hi

I have two table those contributing in one select statement.

One table has 15000 rows , other one has 88 million rows and has 15 partitions,

Both tables have proper indices those can be used in this query.

The issue is Db2 optimizer using wrong order to join the tables. It starts with bigger table and scanning small table for 88 million times! It uses unappreciated indices.

I have tried Runstat to make optimizer aware of indices, and also tables already have statistics.

In one of the Db2 subsystem RUNSTA INdex worked, to change order of joining tables.but for the rest of the Db2 subsystems not worked.All subsystems look the same.

I am confuse why Db2 behavior is different?

Thanks for any help 

Regards 

Leila 

Sent from Yahoo Mail for iPhone