Hi everyone,

i want to learn that, is there a way to choose access path manually in db2?
it is essential for performance.

optimizer' choose is not always correct. especially large tables joins.

No, there is no way to dictate exactly what access plan for DB2 to use. You can however influence it quite a bit based on how the query is written.

Andy

You can use optimization profiles to influence the optimizer.

thanks a lot firstly,

hkzz,
at my work, i'm not dba and not responsible of db system unfortunately.
but i want to learn how can i influence the optimizer by optimization profiles.


andy,
if all joins are equal join (inner), always optimizer chooses the access paths. sometimes i add 1=0 expression into the join sentence for not using of that index.
but sql sentences are very long, 20+ tables can be joined, row counts vary about 1000 and 2.5 billion (of course, i'm working in a bank).
in that situtation, join order, access path is very very essential.
optimizer chooses small tables first, then big tables.

for instance: (just example, it can be trivial)

Table A: 2.1 billion rows (partitioned)
Table B: 1 billion rows (partitioned)
Table C: 10 million rows
Table D: 100.000 rows
Table E: 10.000 rows
Table F: 1000 rows

relations
A -> B (joined)
A -> E
C -> E (alias of E)
B -> C
C -> D
B -> F

optimizer chooses acces path that
E -> C -> D -> B -> F -> A -> C (just imagination, maybe the example is not consistent)

but i always prefer that
first,
A -> B should be,
ex: A -> B -> C, .....

since A and B is partitioned, and i use partition index, i take very quickly response (not for optimizer !! )

real performance values:
my preference: 2 minutes (explained cost value is very high)
optimizer preference: 4.5 hours (explained cost value is very low)

My answer was based on DB2 for LUW. My guess is that you are using ZOS, since I never heard of optimization profiles. Please state OS and DB2 version in the future.

Andy

you're right, we are using ZOS and main frame
OS version is OS390 V1.7
DB2 version is V7.1

i'm upset of optimizer chooses. the same sql sentence may has different access paths because of different where condition.

assume we have monthly partitioned table and partition indexes are integer ids.
ex: id is period id of the table
id = 1 refers 01.01.2007
id = 31 refers 31.01.2007

in where condition, if id is between 1 and 31, that means query processed for one partition

think about 2 queries

SELECT .....
FROM ....... (joins)
WHERE ...
and id between 1 and 11


SELECT .......
FROM .......
WHERE...
and id between 1 and 12

both of the queries goes to the same partition,
but for this queries, access paths are different and result time is different (2 minutes, 4.5 hours !!)

if i change the second sql sentence, i add 1=0 into the correct place (generally for small table),
it resembles the first one.

but i can't place the correct 1=0 for every query !!!

Pls refer to http://www.ibm.com/developerworks/db...e/dm-0612chen/
, I am not sure it can support db2 v7.1. If you want, I can send the optimization profiles v8.2.2 to you.

i'm not sure optimazation profiles help me, since i'm not responsible of db system.
but it is useful for me personally, konowledge-based