Some key concents regarding SQL optimization
- predicate
- selectivity (column unique ratio) / cardinality (estimated returned)
- driving (outer) table
- access path/method (e.g. Table Scan, Index Uique/Range Scan)
- join method (e.g. Nested Loops or Sort Merge)
- join order (which table first as outer table)
- join Types (Inner, semi - IN or Exists, anti - Not IN/Not Exists)