Mount Wellington

21 kilometers …. all up hill.  Just up…and up….and up Smile

All on a bicycle Smile

mt_wellington

Tasmania 2012

I did three talks for the tasmanian user group.  Its a small user group, but a credit to the committee that they keep it strong, and keep it going year after year.  It was a pleasure to go there.

And if you’re reading this, I hope

a) you’re a member of your local user group

b) you attend and support any events that your local user group has

c) you will (perhaps one day in future) speak at your local user group.

User groups MAKE the Oracle community.  We share, we learn, we strive to make our solutions better for our customers.  I’m lucky enough to really enjoy getting up in front of an audience and talking about the Oracle things I’m passionate about.  I know for others – getting up in front of a crowd sounds like being tortured slowly. 

But invest in your user group, and it will pay your back two fold.

Perth AUSOUG meeting

I’ll be doing a talk on Flashback on Feb 29.  Details below – hope to see you there.

 

Where: Oracle Australia: Level 2, 66 Kings Park Road, West Perth

When: Wednesday, February 29, 2012. Breakfast starts 7:30am, and the talk from 8-9am

RSVP: By RSVP Sunday 26th February 5pm by emailing vicepresident.wa@ausoug.org.au

Cost: Free to all AUSOUG members. Ticket parking on Walker St, just off Kings Park Road, or catch the Red CAT, with a short walk from corner of Outram/Ord.

Abstract – Flashback:
We have all heard the term "adrenalin rush".  It comes about 3 seconds after you press the Commit button and you realise that you probably needed to have a WHERE clause on that "delete all rows from the SALES table" SQL statement.  Or it might come just after you run "drop table" on the Production database, when you were just so sure that you were logged onto the Test system.  This session will discuss how you can use the FLASHBACK features in 10g and 11g to overcome this uniquely human condition

Calling all Oracle Tasmanians :-)

I’m in Hobart Tasmania for a half day on Monday (Feb 29).  If you’re in town, please drop in and say Hi – should be a fun morning

Here is the agenda

09:00 – 09:15 am General introduction

09:15 – 10:15 am Presentation – Diary of an 11.2 upgrade

10:15 – 10:30 am Morning tea

10:30 – 11:30 am Presentation – Optimizer stats strategies – a fresh approach

11:30 – 12:30 pm Presentation – Miscellaneous SQL features

12:30 – 01:30 pm Lunch, general questions and networking

Morning tea and a  light lunch will be provided so it would be very much appreciated if you could RSVP so we can correctly cater for the event by email to president.tas@ausoug.org.au

 

See you there !

MOS–the brave new world

Its been a long hard painful road….

But finally, we no longer have to ensure that Flash-based mistake that was unleased onto the world a few years back Smile

I freely admit that I’ve been a scathing critic of the MOS flash interface, both on newsgroups and internally via feedback to the support team themselves, and to be honest, I thought that this would all be falling on deaf ears.

So I’m happy to say that somewhere inside the organisation, people were obviously listening to myself (and the many others), because we finally have an interface that looks promising and at first usage, seems functional and practical.

UKOUG–feedback from attendees

Just a short note to say thank you to anyone that took the time to score, rate and comment on my presentations at the UKOUG this year.  Whether you loved my talks, or hated my talks, I’m always flattered by anyone that takes the time to give the feedback that all speakers desire in order to better cater toward the preferences of the attendees at conferences.  After all, its the attendees that are paying the money – they deserve the best that we can give.

Updating primary keys and triggers

I recently came across an example where someone wanted to update primary keys with an INSTEAD-OF trigger (on a view). It can be done, but I wanted to demonstrate that you need to take extreme care, because you can get some problems you might not have forseen.

If you choose to allow primary key updates, then you’ve pretty much got two options:

1) “simple update”

The incoming SQL is say:



        update ...

        set   col7 = :new.col7

        where ...

and the instead-of trigger takes a “global” approach, ie, all columns



        update ...

        set   pk = :new.pk,

              col1 = :new.col1,

              col2 = :new.col2

        where ...

where the column list includes the primary keys…

The problem here is that even an update of a primary to the same value, is still an update to the primary key – which means increased locking for any child tables. It means a pretty careful review of any foreign keys, and associated foreign key indexes.

2) “conditional update”

The instead-of trigger checks to see if the PK columns are being altered, and only includes them if appropriate



 if :old.pk != :new.pk then
          update ...
          set   pk = :new.pk,
                col1 = :new.col1,
                col2 = :new.col2
          where ...
        else
          update ...
          set   col1 = :new.col1,
                col2 = :new.col2
          where ...
        end if;

Still challenges here – because a set operation has now become a row-by-row operation….Consider the following example:



SQL> create table T ( x number primary key, y date );

Table created.

SQL> insert into T values (1,sysdate);

1 row created.

SQL> insert into T values (2,sysdate);

1 row created.

SQL> insert into T values (3,sysdate);

1 row created.

SQL> insert into T values (4,sysdate);

1 row created.

SQL> create or replace

  2  view V as select * from T;

View created.

SQL> create or replace
  2  trigger TRIG instead of update on V
  3  for each row
  4  begin
  5    update T
  6    set x = :new.x
  7    where x = :old.x;
  8  end;
  9  /
Trigger created.

That all seems straightforward enough – but now we’ll compare updates on the base table and view…


SQL> update T set x = x + 1;

4 rows updated.

SQL> roll;

Rollback complete.

No problems there – each primary key was incremented by 1, and then oracle ensured statement level integrity, ie, no duplicates

Now lets try the exact same operation on our view


SQL> update V set x = x + 1;

update V set x = x + 1
       *
ERROR at line 1:
ORA-00001: unique constraint (SYS_C00177683) violated
ORA-06512: at "TRIG", line 2
ORA-04088: error during execution of trigger TRIG'

Uh oh….ker splat !

That’s actually a best case scenario – because at least the thing crashed. Things can get really gnarly once you start doing primary key updates across tables. For example:



SQL> create table PARENT ( p number primary key );

Table created.

SQL> create table CHILD ( f_p number constraint FK references PARENT );

Table created.

SQL> insert into PARENT values (1);

1 row created.

SQL> insert into PARENT values (2);

1 row created.

SQL> insert into PARENT values (3);

1 row created.

SQL> insert into CHILD  values (1);

1 row created.

SQL> insert into CHILD  values (2);

1 row created.

SQL> insert into CHILD  values (3);

1 row created.

SQL> create or replace
  2  trigger TRIG after update on PARENT
  3  for each row
  4  begin
  5    update CHILD set f_p = :new.p where f_p = :old.p;
  6  end;
  7  /

Trigger created.

SQL> UPDATE PARENT SET p = p+1;

3 rows updated.

SQL> select * from parent;

         P
----------
         2
         3
         4

SQL> select * from child;

       F_P

----------
         4
         4
         4

Nothing crashed – but we totally corrupted the child table. This last example is actually also contained in the standard Oracle docs.

And you also have to really ask the question – who or what is updating a primary key ? That’s just a bad idea, triggers or not.