Query Optimization

• Join Methods
  • Nested Loop Join
  • Sort-Merge Join
  • Hash Join
• Updating Statistics
• Checking Statistics Information
• Viewing Query Plan
• Optimizer Principle
  • Selectivity
  • Expected number of rows
  • Cost of sequential scan
  • Cost of index scan
  • Cost of join
• Query Profiling
• Using SQL Hint
• Index Hint
  • USING INDEX
  • USE, FORCE, IGNORE INDEX
  • Examples of index hint
• Special Indexes
  • Filtered Index
  • Function-based Index
• Optimization Using Indexes
  • Covering Index
  • Optimizing ORDER BY Clause
  • Index Scan in Descending Order
  • Optimizing GROUP BY Clause
  • Multiple Key Ranges Optimization
  • Index Skip Scan
  • Loose Index Scan
  • In Memory Sort
  • SORT-LIMIT optimization
• Optimization Using Rewrite
  • Join transforming Optimization
  • View Merging Optimization
  • Predicate Push
  • Subquery unnest
  • Transitive predicate
• QUERY CACHE
  • SUBQUERY CACHE (correlated)