SQL Tips1

1. Optimizer Goals :

  (1). First_rows: 
   (a). Used for applications
   (b). The first row are delivered fast to the application screen
   (c). It takes longer until all rows are fetched

  (2). All_rows:
   (a). Used for batch jobs
   (b). Faster to fetch all rows
   (c). Longer to deliver the first row

2. A database sequence is very fast (values are pre-generated in a memory cache)

3. Don’t update, if you don’t change anything!
   OLD: UPDATE T1 R SET STATUS=’1’ 
             WHERE NOT EXISTS (SELECT ‘FOUND’ FROM T2 OL WHERE OL.ID=R.ID);
   NEW: UPDATE T1 R SET STATUS=’1’
              WHERE COALESCE ( R.STATUS, ‘#’)<>’1’ 
              AND 
              NOT EXISTS(SELECT ‘FOUND’ FROM T2 OL WHERE OL.ID=R.ID);
  Note:  COALESCE returns the first non-null expr in the expression list. You must specify at 
              least two expressions. If all occurrences of expr evaluate to null, then the function 
              returns null.
             
4. Bitmap indexes should not be used if two transactions update/insert rows with identical values 

   on the index column. (Contention issue: no row level locking here). If that is no issue, a 
   bitmap can be used if bitmap index rule-of-thumb: (number of distinct values)<10.000 or 
   (number of distinct values)/(number of all values)<0.01

5. Bitmap indexes can be used in combination:
   SELECT * FROM T WHERE T.COL1=:X AND T.COL2=:Y; --bitmap index on col1 and col2 
   can be merged (bit-and)
   If we had normal indexes on col1 and col2, only one of them could be used or a (costly) 
   bitmap conversion is performed.

6. SELECT col_1, ..., col_n FROM T1
   WHERE name LIKE ‘Ham%’  --Index on name is used
   AND ROWNUM<2001
   ORDER BY name   --Index on name is used

7. SELECT col_1, ..., col_n FROM T1
    WHERE name LIKE ‘Ham%’  --Index on name is used
    AND ROWNUM<2001
    ORDER BY place   --Can’t use index on place (because the condition in the where clause is name)

8. For large result set, the order-by should only be used on columns that are also selective in the where-clause

9. How can we search efficiently for ‘%Nagel%’?
    Solution1: 
    RAM: Pin the index on table.name into the RAM. Database still needs to scan the complete index, but it is in RAM
    Solution2:
    Do-it-yourself-tokenize: Create a new table with all possible prefixes: ‘K’,’u’, ..., ‘N’,’a’,’g’,’e’
    Solution3:
    Oracle Intermedia Indexes: Can also be used for fuzzy searches. Different index types available