Information on a QEP
The information that can appear on a QEP is as follows:
Table or Index Name
Indicates the table on which the query is being run or the secondary index, if any is selected by the query optimizer for execution of the query. This information is provided for orig nodes only (described under Type of Nodes in a QEP below).
Label
Indicates the type of node. For example, Proj-rest identifies a projection-restriction node (described under Type of Nodes in a QEP below).
Storage Structure
Indicates the storage structure in use, as follows, where key is the primary key, and NU indicates that the key structure cannot be used:
B-tree(key|NU)
Hashed(key|NU)
Heap
Isam(key|NU)
Total Number of Pages and Tuples
Indicates the total number of pages involved at the node, and the total number of tuples (rows).
Query Cost Information
Indicates the cumulative amounts of cost that are anticipated at each stage in the execution of the query. This cost is a blend of the CPU consumption and the number of disk I/Os involved in plan execution. The information is shown in the following form:
• Dx estimates the disk I/O cost. x approximates the number of disk reads to be issued.
• Cy estimates the CPU usage, which has been subjected to a formula which turns it into an equivalent number of disk I/Os. y units can be used to compare amounts of CPU resources required.
• Nz is shown for Star databases only. z represents the network cost of processing the query.
Because these values are cumulative up the tree, the top node carries the total resources required to execute the query. The cost involved in executing a specific node is, therefore, the values for that node, minus those of the child node (or both child nodes in the case of a join node).
The QEP graph you see in VDBA indicates both the cumulative cost and the cost for the individual node. For more information, see Viewing QEP Node Information in online help.