Hi Gurus,

Recently, i got 'cornered' as my apps SQLs were deemed extremely resource intensive - reason: DB2 snapshots performed periodically listed my SQLs performing the following join

Table A (with 28 million ) inner join Table B (with 10 K ) on A1 = B1

to process 140 billion records

A1 and B1 are not a part of any index - besides, these are full table joins, no predicate - so not sure if indexes would have done any good. I know Tables A and B are new to the environment, and the stats were updated only table A, not on table B (the smaller table).

My question is - does DB2 perform a cartesian product when it sees stats are out dated?

Also, while the DB2 snapshot got everyone calling me - when I ran a DB2TOP, I saw that the SQL handle was processing the same number of rows (as listed in the snapshot), but the memory , CPU and IO figures were all low!!!!!! (< 1%)

What does that mean?

Any insights, like always, are very much appreciated!!!

You can use db2advis tool to get advisement on whether or not to build the index.

DB2 use three method to perform the table join: Nested Loop join(can use index) ,Merge join and Hash join. DB2 optimizer will choose one method according to the statistics(whether or not they are out of date) .You can use db2expln tool to see which join method db2 used.

To this the answer is "no". If the query semantics don't ask for a cartesian join, no sane database management system would perform a cartesian join.

As for your other claims, for which you don't provide any supporting evidence, I'm afraid all mind readers are out on vacation so you'll have to wait for them to come back.

aint talking sane database management systems here funny pants! ... talking db2!

and for supporting evidence, I'd say, based on the data, typically joins between a 28 million record table and a 10,000 record table would result in a little more than 28 million record (like 40 - 60 million) assuming a 1:1.5 cardinality - that is not in the same ballpark as 140 billion records - for detailed evidence, ask an 11-year old for his elementary math text - he should be able to guide you!

If you join two tables and end up with a Cartesian product, then you did not join them on the correct columns (or the relationship between the two tables is not what you think it is).

You probably should create indexes on the join columns. Specifying join columns is equivalent to putting the join condition in the predicate.

Thank you. This makes it to my "quote of the week" list. And it's not even Tuesday!

Thanks Marcus - I did verify the following -

1. the join clause does use the right columns (of both tables) i.e. based on relationships
2. based on the join, the join output should have been 27 million records

With that knowledge then,

Why are the snapshots and db2top reporting 141 billion records being processed for the join SQL? (This is where I started speculated a cartesian)

I assume that it is doing a table scan on one of the tables each time a join is done. The monitor you are looking at probably just shows the number of rows actually read based on having to do so many table scans.

So what the snapshots reporting as records read, are really records traversed in the table scan for one table - not true join output records.

What was also interesting was - db2top reported CPU usage for this appl. handle to be 0%. What does that mean??

really - how fun!!!! dude, you really need help!