DB2執行計劃（1）

檢視執行計劃的兩種方式：
1.
db2 -tvf $DB2HOME/sqllib/misc/EXPLAIN.DDL
db2 set current explain mode explain
db2 "statements"
db2 set current explain mode no
db2exfmt -d bidw -u username password -w -l -s % -n % -o explain.out
cat explain.out

2.
db2expln -d bidw -u username password -g -t -q "statement";
eg:
db2expln -d bidw -u username password -g -t -q " statemens"

===============================
db2exfmt o Table Operators
db2exfmt TBSCAN - Table Scan
db2exfmt IXSCAN - Index Scan
db2exfmt FETCH - Fetch from Table
db2exfmt
db2exfmt o Joins
db2exfmt MSJOIN - Merge Scan Join
db2exfmt NLJOIN - Nested Loop Join
db2exfmt HSJOIN - Hash Join
db2exfmt
db2exfmt o Aggregation
db2exfmt GRPBY - Group By
db2exfmt SUM - Sum
db2exfmt AVG - Average
db2exfmt MIN - Minimum
db2exfmt MAX - Maximun
db2exfmt etc.
db2exfmt
db2exfmt o Temp / Sort
db2exfmt TEMP - Insert into temp table
db2exfmt SORT - Sort
db2exfmt
db2exfmt o Special Operations
db2exfmt IXAND - Index ANDing
db2exfmt RIDSCA - Index ORing or List Prefetch
db2exfmt IXA - Star Schema Bitmap Indexing
db2exfmt BTQ - Broadcast Table Queue
db2exfmt DTQ - Directed Table Queue
db2exfmt MBTQ - Merge Broadcast Table Queue
db2exfmt MDTQ - Merge Directed Table Queue
db2exfmt LTQ - Local Table Queue, for Intra-partition parallelism

+-------------------------------+-----------------------+-----------------------+
| Join Strategy | Inner Table | Outer Table |
+-------------------------------+-----------------------+-----------------------+
| Collocated Join /*配置地*/ | Temporary Table | Temporary Table |
+-------------------------------+-----------------------+-----------------------+
| Directed Inner Join | Temporary Table | Hashed Table Queue |
| Directed Outer Join | Hashed Table Queue | Temporary Table |
| Directed Inner and Outer Join | Hashed Table Queue | Hashed Table Queue |
+-------------------------------+-----------------------+-----------------------+
| Broadcast Inner Join | Temporary Table | Broadcast Table Queue |
| Boradcast Outer Join | Broadcast Table Queue | Temporary Table |
+-------------------------------+-----------------------+-----------------------+

附:

BTQ Broadcast Table Queue broadcasts data to several partitions.
DELETE Deletes rows from a table.
DTQ Directed Table Queue transfers data to a specific partition.
EISCAN Scans a user-defined index to produce a reduced stream of rows.
FETCH Fetches columns from a table using specific record identifier.
FILTER Represents the application of residual predicates.
GRPBY Groups rows by common values of desig-nated columns or functions.
HSJOIN Represents a hash join, where two or more tables are hashed on join columns.
INSERT Inserts rows into a table.
IXAND ANDs together the row identifiers (RIDs) from two or more index scans.
IXSCAN Scans an index of a table with optional start/stop conditions, producing an ordered stream of rows.
LTQ Local Table Queue. Transfers data between local agents.
LMTQ Local Merge Table Queue. Merges data transferred between local agents.
MBTQ Merging Broadcast Table Queue.
MDTQ Merging Directed Table queue.
MSJOIN Represents a merge join, where both outer and inner tables must be in join-predicate order.
NLJOIN Represents a nested loop join that accesses an inner table once for each row of the outer table.
RETURN Represents the return of data from the query to the user.
RIDSCAN Scans a list of row identifiers (RIDs) obtained from one or more indexes.
RPD For nonrelational wrappers, it shows the simulated SQL operation that the nonrelational wrapper will be asked to perform.
SHIP Retrieves data from a remote database source. Used in federated systems.
SORT Sorts rows in the order of specified columns, and optionally eliminates duplicate entries.
TBSCAN Retrieves rows by reading all required data directly from the data pages.
TEMP Stores data in a temporary table to be read back out (possibly multiple times).
TQUEUE Transfers table data between agents.
UNION Concatenates streams of rows from multiple tables.
UNIQUE Eliminates rows with duplicate values, for specified columns.
UPDATE Updates rows in a table.

[@more@]