For example, would there be anything on this graph that would tell me "Hey
dummy, stop using an IN and use an EXISTS."?
No, nothing that explicit. That's why I advocate at least a part time DBA
in shops driving a lot of SQL through the box. And attending the 3 day
query optimization course IBM offers as a good start.
Is the reason for the temporary index "nested loop join"?
Possibly. If you don't already have a key over join columns, for sure. But
more likely it is the case where you're joining on derived fields, i.e.
UPPER(myFieldFromTable1) = UPPER(myFieldFromTable2). Query engine will
probably build a temporary index to satisfy the join condition. Obviously,
knowing this factoid is very useful, as I can already hear the wheels
spinning as to the solution to this particular scenario :).
Or the three table scans and one table probe?
Highly doubt it's a table probe. Quite possible for table scans, unless
you're fetching more than 20% of the rows (don't quote me on this number;
things change too rapidly with the query optimizer to count on any numbers
being 'solid').
HTH, Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: RE: How to save Visual Explain
Thank you.
View, Arrow, Processing time, changes the labels to "Not Available". Just
to see if the data has aged I changed it back to number of rows and
they're still there.
Going to a presentation on this was good. However using it to analyze a
real problem and how to resolve it would make it better. Or the three
table scans and one table probe?
Rob Berendt
midrange.com
