Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 28, 2007

Somethings never change, be carefull

Filed under: Oracle Concepts and Architecture — H.Tonguç Yılmaz @ 9:24 pm

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

2 Comments »

  1. another case about DDL auto commit from Jonathan Lewis is below

    Session 1-

    10:53:55 SQL> lock table tt in exclusive mode;

    Table(s) Locked.

    Session 2-
    10:54:06 SQL> delete from tt;
    –this session is waiting for releasing the lock

    Back to Session 1-

    10:54:23 SQL> truncate table tt;
    truncate table tt
    *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified

    Back to session 2- Lock released because of the truncate command. And the delete operation is finished. (truncate is a DDL too)

    2 rows deleted.

    10:54:24 SQL>

    Reference

    http://www.jlcomp.demon.co.uk/faq/truncate_busy.html

    Comment by coskan — January 29, 2007 @ 8:57 am | Reply

  2. another prove for what if parse not successful ??

    session 1-

    11:05:11 SQL> insert into tt select user,sysdate from dual;

    1 row created.

    11:05:21 SQL> select * from tt ;

    USR SD
    —————————— ———-
    PR 29/01/2007

    11:05:41 SQL> commit;

    Commit complete.

    11:06:15 SQL> insert into tt values (‘COSKAN’,sysdate) ;

    1 row created.

    –not committed

    session 2-

    11:06:09 SQL> select * from tt ;

    USR SD
    —————————— ———-
    PR 29/01/2007

    11:06:17 SQL> select * from tt ;

    USR SD
    —————————— ———-
    PR 29/01/2007

    session 1- syntax check failed during parse

    11:06:49 SQL> create table tt select usr,sysdate from dual;
    create table tt select usr,sysdate from dual
    *
    ERROR at line 1:
    ORA-00922: missing or invalid option

    session 2- –no commit no change

    11:06:54 SQL> select * from tt ;

    USR SD
    —————————— ———-
    PR 29/01/2007

    Comment by coskan — January 29, 2007 @ 9:12 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: