Oracle Musings

Ok, I suppose you can’t apply standards to people and their personalities…  But you can try, can’t you?  I thought this article about personality traits of the best software developers was spot on.  In a nutshell, the best software developers are:

1. Pessimistic in the short run, optimistic in the long run
2. Angered at sloppy code
3. Like to plan ahead
4. Pay attention to detail

I like it.  Simple, short, to the point.

First up in our work on development standards refers to SQL.  A reminder that these are just my opinions, based on what I’ve seen work — I’m perfectly willing to hear counter-arguments, proposals and even full-blown critiques of what’s wrong, what’s better or otherwise

I like to keep the overriding goal in place here — these standards should not place undue burdens on developers.  They should be simple enough that even novice developers can abide by them without requiring a ton of knowledge, but also be appreciated by savvy developers.

(It does help if the developer has some knowledge of how Oracle works in order to appreciate the standard — hence why I continue to believe that you want people with some DBA experience to be your SQL developers instead of trying to teach the Java / PHP and Perl people this stuff).

Anyway, I’ve got very few standards for SQL statements:

1. Whenever there are 2 or more tables in the statement, always alias each table and every referenced column.  Be consistent — don’t use schema.tablename.columnname in one place and then alias.columnname in another.  You don’t need to “standardize” on table aliases across all systems, just within your statement.
2. I prefer the old-style syntax to the new, fancy ANSI-style join syntax, but that’s just me.  But whatever you do, pick one and stick with it or at least state that all new code will adhere to it and all rewrites will bake it in.
3. Use bind variables unless you have a absolute reason not to — one you can actually articulate.
4. Learn to use sqlplus AUTOTRACE and run the statement against a representative data set (we’ll talk about that later) using representative bind variables.  Your goal is 5-10 consistent gets per table join per row retrieved or processed.  (I don’t know how to represent this kind of information in other databases — another reason I like Oracle is that it gives you this kind of statistical feedback “for free”).
5. Use as few hints as possible — let the optimizer earn its keep (and justify part of the price you’re paying for a robust database).
6. Try to look at things from the database’s perspective — what information does it have about the data it’s trying to work with?  Understand that it usually does the best it can with the information it has on hand.
7. Minor nit — don’t rely on GROUP BY for data ordering.  If you want something sorted, use the ORDER BY clause — that’s what it’s there for.
8. Read the Functions section in the SQL Reference Guide.  You’d be surprised how often you can do something without having to write 3GL code.

A lot of people like to quibble about the AUTOTRACE item.  “Why 5-10, why not 1-2…” — the truth is that I don’t care all that much about the particular number.  What I do care about is that the system uses as few resources as possible related to the size of the result set it’s asked to produce.  The ultimate goal is to produce the largest possible result set using very few “work units” (ultimately, time).  A simple standard and process to measure it is all that is needed.

This simple set of rules usually produces pretty good SQL, even if your developers are new to the game.

Back when I was a tweener I used to watch this show on PBS — I seem to recall that I enjoyed it, but I liked science as a kid, so no surprise.

Actually, this post is about my search for a good on-line contact and calendar manager with an emphasis on collaboration capabilities.  I’ve looked at the “hot” properties like CalendarHub, Google Calendar and 30boxes — but all of them seem to be deficient in some area.

I found Airset last night and it appears to do everything I want — and includes mobile access.  But I can’t find much in the way of reviews or feedback on it.  PC Magazine liked it, but I found that PC Mag’s reviews have gotten awfully “light” over the years.

Anybody have any comments on Airset (or any other worthy competitor?)

I’ve been thinking lately about what topics to post on — and while I’m attracted to the religious arguments about developer DBAs and DBA developers and who develops the SQL and who designs the tables and who writes the PL/SQL and whether or not you should use stored procedures… (deep breath)… I think we’ve all heard variations of that song ad nauseum.

So, given that, I think I’d like to talk about the development process itself.

I’m going to try an describe a possible process for developing an internal applications with reasonably high uptime requirements and fairly frequent feature introduction and/or bug fixing.

I’m NOT going to focus on coding and naming standards.  Honestly, I don’t care too much about those — only that they are consistent.  And heck, anybody who specifies spacing and alignment needs to chill out and look into pretty-printers and code formatters.

This will be a multi-part series and I’d love to hear feedback — even specific questions and scenarios.

BTW, the reason I find this topic interesting has to do with some of my notions around the construction and formation of development teams.  In general, I believe that as a manager my job is to remove as many non-coding obstacles as possible from my staff in order to allow them to be as productive as possible.  (I read a fair amount of Mills and Yourdon a long time ago — I know, today I’m supposed to speak about “agile” development and “extreme programming”, but sometimes I just like to avoid the tags and speak about the underlying methods and results).

I’ll close this post with a guiding principle: A development process should promote efficiency — not add overhead or administrivia.

Looks like our MySQL migration work is on hold for now — the server is undergoing recovery.  But in any event I think I’m going to abandon work with using ODBC through Oracle HS to Oracle XE conversion.  In all, I think that approach would work for a light-weight system (i.e, systems between 1-3GB in database size, and without TEXT / LONG columns).  But the MySQL database I’m looking at is about 80-100GB in size — small, but big enough (and including TEXT columns), that I’m probably going to move up to Oracle Migration Workbench andfull-blown Oracle 10g.

Now, if I can only scrounge up a stable test / development system…