[DB Tuning] wrong query usage for INDEX

  1. Data type casting of column
    • WHERE TO_CHAR(created_at, ’YYYYMMDD) = ‘20030618’
  2. Data type casting of record
    • WHERE created_at = ‘20030618’
    • if created_at type is DATE type, this query does not refer to index
  3. NULL or NOT NULL condition in query
    • WHERE amount IS NULL
    • tuning =>
      • set amount default as 0,
      • use [ WHERE amount > 0 ] for query
  4. != usage
    • where debt_code != ‘partial’
    • NEGATIVE query can not refer to index
    • tuning => use the one of next.
      • WHERE debt_code > ‘ partial’ OR debt_code < ‘partial’
      • WHERE NOT EXISTS (SELECT ‘x’ FROM TABLE WHERE debt_code = ‘partial’)
      • WHERE NOT IN
  5. LIKE search
    • WHERE addr like ‘%melbourne’
    • tuning =>
      • Split the addr column as city, suburb, street etc
Advertisements