The explain plan plays an important role in identifying sub-optimal joins. Any discrepancies in a join can be checked from the explain plan even before executing the actual query. If you find these keywords in your query, make sure it is tuned before execution:
- Translate
- Product join
- Estimated row 1
- All-rows scan
- Low confidence
Sub-optimal joins not only impact the CPU and runtime, but also SPOOL. Teradata often has to use spool space to hold copies of rows redistributed to do a join. The optimizer minimizes the amount of spool required by:
- Copying only those rows that the query requires
- Doing single-table set selections first (qualifying rows)
- Putting only the smaller tables into spool whenever possible
High spool queries ...
