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.
Of course some things cannot be done by the database and standard PL/SQL, such as reaching out to the filesystem (yes we have dbms_lob and utl_file, but they cannot for example read the contents of a directory), reaching out to other non-Oracle databases (except when you have Oracle gateways), reaching out to the internet in more advanced ways than utl_http and utl_tcp support (although grantedly I do not have a meaningful example rightaway).
Then there are things that PL/SQL can do – but not any better than Java, such as generating HTML. When you are thinking about scalable solutions, you could and probably should wonder whether using PL/SQL and therefore database resources, for producing HTML – especially the none-data driven HTML, provides the optimial, most scalable solution. Kyte is a big fan of HTML DB and MOD_PLSQL in general and I have used a lot of MOD_PLSQL in developing the Repository Object Browser (pka ODWA), but I really do not believe it a very scalable architecture. Perhaps if one database acts as data server and another as PL/SQL application server you have a more scalable layout. Personally, I’d go for the most efficient way of gathering the data to use for a webpage (for example using a PL/SQL based API that collects all data, in Collections or ref-cursors) and returning it to a Java based middle tier that sticks it into fancy HTML.