Wednesday, December 20, 2006

11g PL/SQL enhancements presentation

While a number of bloggers have reported on what they heard at conferences regarding 11g, in many cases the presentations by Oracle employees have not been made available. I believe this is due to Oracle not wanting to be implied as committing to ANYTHING.
While doing an information hunt, I came across the NYOUG presentations here which includes the pdf of a Tom Kyte presentation on 11g PL/SQL enhancements.

If there is a policy of not releasing these presentations, there's a chance Oracle will ask for it to be removed from the site so best "get it while its hot".

May ratings are :
  • DML in triggers is faster - 2. We are putting that code into packages anyway, aren't we ?
  • Fine Grained Dependency Tracking - 8. Very useful for impact analysis.
  • Native compilation - ?. Depends on whether there is a significant performance benefit.
  • Intra-unit inlining -5. Anything that allows use to write more readable code with no performance hit is bound to be good.
  • Function result cache - 7. Looks good, but we'll have to watch that RELIES clause
  • SQL result cache - ?. If it is a straight query with no variables, isn't it a materialized view ? Wonder how it works with variables and/or SYS_CONTEXT
  • Compound trigger -?. Sounds good, but I can see people trying to put validation in there that doesn't cater for concurrent table updates.
  • Dynamic SQL -3. I don't see much use of it, but anything that makes it more practical to keep the SQL in the database and away from those Java developers must be good :)
  • Fine-grained access control for UTL_FILE/UTL_HTTP/... - 8. More security GOOD.
  • Regular expression enhancements-?. Not something I've used.
  • Super-?. Also not something I'm familiar with.
  • Read only table-2. Check out the Dizwell article on this. Is it really Read Only (and could we apply it to individual partitions ?)
  • Disabled trigger-1. Don't we test these things ?
  • Trigger firing order-1. Do we need to make triggers MORE complicated ?
  • "When others without raise" warning-7.
  • Direct use of sequence.nextval in PL/SQL-7.
  • "Continue" command for loops-2. Spaghetti code.
  • Named parameter referencing for functions called from SQL-7. Readability improvement.
So fine-grained dependency tracking is the big ticket item as far as I'm concerned, with the advantage that as soon as 11g is out, you can copy your database structure to a development copy of 11g and get that impact analysis benefit long before you upgrade your production environment to 11g.

Tuesday, December 19, 2006

A funny place, Australia

At the weekend, I had to cast my vote in a local election arising from the death of one of the concillors. I say "had to" because voting is compulsory here.

"VOTING AT THIS ELECTION IS COMPULSORY FOR ALL ELECTORS WHOSE PLACE OF LIVING IS WITHIN ..... Every elector who fails to vote without a sufficient reason will be liable to a penalty not exceeding $110.00."

Coming from the UK (where you get a little polling card to present at a polling booth), when I attended my first election as an Australia I was quite surprised that all I had to do was tell them my name and address. No id check, no secret check of birth dates or mother's maiden name or anything else. Trust but don't verify.

The man just crosses your name off his list. That's so you don't try voting again. Except there were 11 polling places I could attend, each with their own lists. So it is quite possible to "vote early, vote often".

There is a cross check after the election between the polling place lists so they can fine people who DON'T vote. If you appear to have voted several times, you'll get a "PLEASE EXPLAIN" letter but since they don't check id, they can't really do anything about multiple votes.

Strange, but it all seems to work.

Monday, December 11, 2006

NULL is my middle name

Eddie Awad has linked to one of the perennial database discussions, the role of NULLs in the database.

My take on this is coloured by the fact the NULL is my middle name. Or to be more precise, my parents chose not to give me a middle name. So let me make it quite clear that NULL is NOT "Unknown". I know for a fact, backed up by my birth certificate, that I do not have a middle name. Any application that deals with my name needs to get used to that fact, because it is not going to change. I do not want to be addressed as Mr G. M. Yers, or Mr G. . Myers or Mr G. ?. Myers. The vast majority of computer applications don't have a problem with my lack of middle name. The world has not fallen in because of my parents decision.

So if NULL isn't "Unknown", then what is it ? Simply speaking NULL is as NULL does. It is ignored by aggregate functions (count, min, max etc), it is ignored by most predicates (=, IN, LIKE etc) and in fact ignored anywhere that doesn't explicitly cater for them. And that is they key to its usage. You allow a column to be null when you want null values to be processed in the way Oracle processes them.

Null-haters tend to list a bunch of cases where the NULL makes no sense and extend that to say that NULL should be abolished. That assumes NULL is a data problem. It isn't. It is a business issue, and one of a number of several presented by 'extreme' data. A company decides to pay a bonus based on the percentage increase in sales value by individual salesmen between quarters. But Fred has only been there a month, so didn't have sales for the last quarter. Does he get a bonus, and if so, how much ? That is a business decision, not a data one. It doesn't matter whether we have recorded a null or a zero against Fred's sales for a quarter, or whether the row doesn't exist or if we have an exotic datatype that holds either a number or a 'weird data flag'.

There are undoubtably cases where a NULL makes no sense. We are allowed to defined columns as NOT NULL (and even put in other check constraints to ensure they only have acceptable values) and this data integrity constraints should be used wherever possible. The question is not just whether we should allow SALES_VALUE to be null, but should be allow it to hold negative values, sub-cent (or penny) fractions, values over a billion....

And my last word is, if you abolish NULLs, then I'm going to get a middle name that is at least 2001 characters long (and include some heiroglyphics not seen since the pyramids were finished), and that will really sod up your database !