Somethings never change, be carefull

In one of my previous posts I wanted to attract attention to the “change”; Technology changes very fast, keep UNlearning. But some concepts do not change :)

In Oracle if you use Data Definition Language(DDL) in your code your transaction logic will be broken because there is an implicit COMMIT immediately before parsing and executing phases of the DDL;

Begin
   Commit; -- this breaks the transaction, whatever your DDL wiil result doesnt matter
   Parse DDL; -- syntax and privileges need to be controlled
   Begin
      Execute DDL;
      Commit;
   Exception
      When Others Then
        Rollback;
   End;
End;

Code listing 65a : Using DDL in a transaction Example

Another important concept in Oracle is full table scans always reads all the blocks under the high-water mark of the related segment.

Code listing 65b : Full Table Scan and High Water Mark Relation Example

Like DDL you must also be carefull with DELETEs in your applications, especially after huge DELETEs you need to perform CTAS or MOVE operations on the table if you need FTS performance. Or most probably it should better you use partitioning instead of this DELETE operations :)

The use of SQL in triggers may result excessive parsing problem, move SQL in the triggers into PL/SQL and call them from triggers;

Code listing 65c : Move SQL out of the trigger Example

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

Refences Used :
Effective Oracle by Design by Thomas Kyte Chapter 5- Statement Processing, Page 264
Effective Oracle by Design by Thomas Kyte Chapter 8- Effective SQL, Page 453
Effective Oracle by Design by Thomas Kyte Chapter 5- Statement Processing, Page 299