The tabular representation is a top-down, left-to-right traversal of the execution tree.
When you read a plan tree
you should start from the bottom left and work across and then up. In the above example, begin by looking at the leaves of the tree. In this case the leaves of the tree are implemented using a full table scans of the PRODUCTS and the SALES tables. The rows produced by these table scans will be consumed by the join operator. Here the join operator is a hash-join (other alternatives include nested-loop or sort-merge join). Finally the group-by operator implemented here using hash (alternative would be sort) consumes rows produced by the join-operator, and return the final result set to the end user.
This is a cost-based query, since the “cost” hint is used.
We should be suspicious of the TABLE ACCESS FULL statement, since this is very slow. The student should recommend:
- Check for proper index on this table.
- Try re-explaining this query with a “rule” hint.