There's more...

  • You should run an EXPLAIN for a query with multiple Joins especially if joining columns are non-index columns
  • Look for high estimated runtime's (more than one to three hours) or a large row count (several hundred million to billions in the case of large tables)
  • If estimated rows don't match your expected row count, especially if they are exponentially high, don't execute a query before attempting to optimize it
  • The explain plan of an executing query can be checked from the Viewpoint interface at the time of running
  • The DBQLExplainTbl table in the DBC database holds the explain text of already executed queries. The DBQL ruleset to collect the execution plan needs to be enabled to store data in this table.
  • Check for partition elimination on large tables (most fact tables) if the PPI column is used; mismatched data types, character sets (Unicode versus Latin), applying functions, or inequality on the PPI column will prevent partition elimination. 
  • If you see steps with estimated row count = number of AMPs on your system in a distributed or duplication step, it could mean that stats are missing or stale on that table.
  • Product joins can be a problem, but can be the best option when one of the tables has very few rows (for example, a table with a number of rows less than the number of AMPs on the system).
上一章目录下一章