I’ve been having some fun with explains lately. I’ve been preparing a presentation for some developers on how to analyze queries – I’ll be presenting next week when I’m on-site. So I started digging through some explain plans I had sitting around from various times when I’ve need to do analyses, and I started going through them. One reccomendation on my list of “DB2 SQL Best Practices” is to avoid in-lists。 whenever possible. Just one of those tips that I’ve always known and never really thought about. So I start digging into this excerpt from
Having not done much in-depth analysis recently and most of my recent analysis being of the popcorn and bubble-gum variety using the index advisor, I wonder why it is scanning the same index 6 times (and that is taking up just less than 1/2 of the total timerons). Looking into the query itself, I find an IN list with exactly 6 items – whaddya know. So in this case, DB2 had to scan the SAME index 6 times -once for each item in the list, and then it did a RID scan of that list and fetched the rest of the data from the base table. I now have proof of how IN lists can work and a nifty example for the developers.
So digging into more queries, I find this access plan:
Access Plan:
-----------
Total Cost: 102.842
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
4
NLJOIN
( 2)
102.842
8
/----+---
4 1
TBSCAN IXSCAN
( 3) ( 6)
0.00286127 25.7188
0 2
| |
4 55804
SORT INDEX: DB2INST1
( 4) IBM_007
0.00214227
0
|
4
TBSCAN
( 5)
5.95154e-05
0
|
4
TABFNC: SYSIBM
GENROW
Nothing too expensive or complicated here – single table access. But why the heck is it doing a JOIN when the query only accesses a single table with no subqueries? And furthermore, what’s this GENROW thing that it’s doing a table scan on and getting 4 rows. The documentation is real helpful on GENROW (from the Info Center):
GENROW
Generates a table of rows.
Digging a bit further, I find:
This operator is used by the optimizer to generate rows of data.
Operator name: GENROW
Represents: A built-in function that generates a table of rows, using no input from tables, indexes, or operators.
GENROW can be used by the optimizer to generate rows of data (for example, for an INSERT statement or for some IN-lists that are transformed into joins).
Ok, so burried in there is a reference to IN-lists and commerce tends to be a bit IN-list happy. Looking at the query, I find an IN-list with 4 values. So DB2 is essentially treating that 4 rows as a (non-indexed) table, and then doing an NLJOIN between it and the table itself. So on a single-table query, I get a sort, a table scan, and a join, even though I’m getting index-only access to the table directly.
Much of this I’ve learned before when digging into the details of explains, but I haven’t focused so much on how IN-lists are handled, and thought it was interesting.
阅读(4818) | 评论(0) | 转发(0) |
