A short report of the second day of the OakTable Seminar AskTom Live with Tom Kyte!
The day started with the last part about analytic functions that we didn’t finish yesterday. Analytic functions are so ‘brutally great’ that it is all very much worthwhile. It appears that once you have learned to use them you will use them everywhere.
Next, one of Tom Kytes favorite subjects, read consistency and write consistency, was on the program. Even if you think you know something about Oracle databases there where some surprising consequences presented. Two rules of thumb: data integrity should be handled by the database (e.g. with function based unique indexes) and do not put any non-transactional things in a database trigger (the triggers may fire more often then you think!)
The top 5 things done wrong over and over again, according to Tom Kyte, are: not using bind variables (by far the number 1), not having a test environment, not having any configuration management, database independence, DBA versus developer and not building to scale, not building secure. Some of these things can easily be avoided, so do it.
A short part was about hints: ‘To hint or not to hint?’ Some of the times hints are good but most of the times not. When you think you need a hint probably something else is not right and it is better to fix that.
With the Workspace Manager (package DBMS_WM) it is possible to version the data and to create branches. This out of the box functionality was compared with a do it yourself method. The conclusion was that for a moderate transactional system the performance would not notably suffer and DBMS_WM would be a good option.
This second day ended with a long list of advantages of PL/SQL above Java for doing things in the database.