Database Optimization

I’m loving digging into the weeds on some of this stuff. So many nuances.
Article summary from http://www.smart-soft.co.uk/
“…performance tuning is not an exact science – you can’t always predict that a certain tactic or technique will improve performance. Every change has to be tested, because whilst it may improve performance in one area, it may also degrade performance in another area”
  • Full table scan vs. Index – Consider how it will impact ram (SGA in oracle)
  • How much data will be retrieved from each of the tables
  • How selective the indexes are
  • How frequently the indexed columns are queried, compared to the frequency of update of the same columns, before deciding whether or not to use an index
    • For b-tree indexes, the fewer rows being selected by the query, the bigger the performance increase would be
  • More indexes = longer updates and inserts
  • There should be n-1 join conditions where n is the number of tables in the join
  • Make sure that the smallest result set is created first (either use hints or rearrange the order of the tables in the join clause, last being the first used)