jump to navigation

Oracle Nostalgia December 15, 2011

Posted by mwidlake in database design, development.
Tags: ,

When preparing the material for my “Oracle Lego – an introduction to Database Design” presentation for the UKOUG last week, I was looking back at my notes from a course on the topic from “a few years back”. There were a few bits which made me smile.

Oracle’s [SQL] implementation conforms to ANSI standard, although referential integrity will not be enforced until version 7

Any other old geezers having flashbacks? I am so glad my first major Oracle development project swapped to using a Beta of V7 very early, so we had the integrity turned on during most of development. I had to help a few projects go from V6 to V7 and turn on the RI – it was usually very, very painful. Or impossible. I always think back to those nightmare experiences when some bright spark suggests turning off referential integrity for “ease of development” or “performance” reasons. There are good performance reasons for altering how you implement RI but, as I said during my presentation on database design, I have never, ever, ever seen a system with RI turned off that did not have damaged data.

Oracle’s optimiser is rule-based. Designing efficient queries involves taking advantage of the optimiser behaviour


You can tell this course was run in the UK due to the lack of ‘z’ in ‘optimiser’ :-). How many of use can now make a stab at the seven or eight significant rules from the 15 (16, 17 as versions advanced) in the list? Several rules were to do with Clusters so you did not care. Let’s think, what were the main things to keep in mind…

  • most significant table last in the FROM clause and order upwards in the order you wanted to visit {most significant being the one you felt you could most efficiently do the first filter against}
  •  WHERE clauses ordered downwards in the order you wanted them to be applied.
  • Order of preference to identify a row was something like ROWID, primary key, unique key, full non-unique key, partial unique key, partial non-unique key, full index scan, full table scan.
  • Disable index access by adding 0 to numeric columns and concatinating null to varchars.

I’ve not checked back in the manuals (I have a set of the V7 on my laptop) so I’m probably wrong.

Storage….selecting suitable values for storage parameters … will improve the final performance of the database

Considering the “suitable values for storage parameters” was perhaps my first real conscious step into being a performance/design guy {I was lucky to be on a project where designing for the RBO and matching those rules was just part of being any developer}, but the calculating of rows-per-block, initial and next extent, pctincrease (not always zero you know), initrans/maxtrans, segment to tablespace size… I learnt all about that and had spreadsheets for it all.

Now of course, all of the above about storage (and RBO) has pretty much disappeared. Oracle has made some of the contents of my brain redundant.

But some things have not changed at all in 18 years:

Users can be relied upon to know what they do NOT want, not what they want, which {unfortunately} is the premise from which analysis starts)

I think the above is the fundamental issue from which all iterative design methodologies spring. ie do not believe what the user says they want, show them something and fix it. It is probably human nature that we are not well able to express what we want but have no problem pointing out something is not at all what we want :-). Add in all the issues in respect of forgetting about the exceptions, assumed knowledge, incompatible vocabularies (the words your users say to you are as confusing as the techno-babble you fire back at them) and all analysis is fundamentally flawed.

Do some analysis – but then prototype like crazy. With real users.


1. Neil Chandler - December 15, 2011

You’re showing your age there, Martin. Although my first relational database project was SQL./DS (which is what came before DB/2 at IBM) on an IBM Mainframe under VM/HPO around 1990/1.

My first Oracle release was 6.0.29 – we didn’t fully port to Oracle 7 until mid-1993.

How am I younger than you?


mwidlake - December 15, 2011

>How am I younger than you?
Don’t forget you were a child prodigy…
We are about the same age, I just happened to work for the NHS for the first 3 years or so, starting from ’89. I now work with people occasionally who were not born in ’89. I guess this just gets worse and worse as we get older and older…

2. Mike Cox - December 15, 2011

Some of us began life on Oracle 5 (or maybe even 4) sometime around ’86, things were so much simpler back then. I had the entire oracle toolset and database all running on a Dec rainbow, which despite its name was only green screen.

Optimisation/tuning was a lot easier back then, it was just a case of is the sql using an index, if so is it the right one or should it not be using an index at all, but then again we considered 30GB to be VLDB.

I still have a very early copy of oracle on 5 1/4″ disc, though of course no drive to read it on, which is probably for the best.

mwidlake - December 15, 2011

5 1/4 ” beats me, I can only claim to 3.5″ – though I have a set for OS/2

I’ve got a document somewhere under my desk, an internal Oracle paper on VLDBs, dated 1992 I think. It says a VLDB is 30GB on proper hardware (DEC VAX or IBM Mainframe) and on this fancy new UNIX thing it is 20GB 🙂 I keep meaning to scan it in and do a blog about it.

You cheated anyway Mike, you did Oracle as part of your college course!

3. Dom Brooks - December 15, 2011

>Disable index access by adding 0 to numeric columns and concatinating null to varchars.
Got plenty of code doing that right here 😦

Dom Brooks - December 15, 2011

But I suppose the earliest comment in this codebase is dated 1992 so it’s not unexpected

4. William Robertson - December 15, 2011

Started on Oracle 5 and Forms 2.3 in 1989, didn’t even know there was a newer version until my next job in ’91. I remember taking that job because I was told I would be working with an experienced guy and I thought it would be a chance to learn something. A week in, he suggested inserting data one column at a time and asked me whether binary handled odd numbers.

mwidlake - December 15, 2011

Oh that’s brilliant William! All of it, but ‘asked me whether binary handled odd numbers’, that is just gold.

5. jgarry - December 16, 2011

I rarely admit it, but oracle was part of an evaluation I did back in V3. We stuck with the mainstream PDP/VAX stuff popular at the time, including Datatrieve. So I really consider that I started on V5 (I managed to save the floppies and docs from dumpster heaven – and I do have the drive, but have decommissioned the computer it is in), converting it from pc to 6.0 on SunOS. I also have some 6 and 7 docs, and a Larry Ellison authored SQL language pamphlet I won in an IOUG trivia contest.

I do recall being on usenet cdo and answering some question about 7.0 RI with a snide comment about “welcome to referential integrity!” having been on Unify and dealing with the late ’80s issues there. But I can’t seem to find it on groogle. I did find a sig I used once: We used to spend all night getting drunk and rebuilding pointer files. Now we spend all night reconstructing referential integrity. Can’t get drunk. That’s progress?

6. simpleoracledba - December 19, 2011

hmmm. (wonder what they put in the coffee here…)

The year 1987, some vague IT course at uni. On a VAX.
Got told about SQL (also called UFI then) and about some iap-generator that needed careful answers to a lot of prompts (later called forms). Then had to do a lab with those – I created my first table, some queries and a whole “form” screen.
mustabeen v4, but I dont think they even told us the version. Unfortunately I didnt keep the printouts.

It wasnt nice: I found it cumbersome, not the promised, hyped, new-efficiency at all.

Real hands on only started in 1989, culminating in some pro-FORTRAN programming in 1991/2 and even a bit of Pro*C, twice. User Exits anyone? (just dont!)

I told my managers in early nineties, repeatedly:
“That Oracle thing has no future: too big, too clumsy, too complicated, too many promises and too greedy sales.”
I still stand by that opinion.
After all, I was only proven wrong on the words “no future”.

Mental note:
Keep looking forward.
Never forget.
But stop looking back(ward) like grumpy old men…

The Future is 12c (C as in Clear? Complex? Cumbersome? Clout – as in bully-power ? Cloud – as in fluff?)

Mike Cox - December 19, 2011

I was thinking about ufi the other day when Martin first posted this, wasn’t it a separate tool to sqlplus but with better functionality, variables etc ? allowed you to do some of the things that had otherwise to be written in rpt/rpf, it was useful but didn’t hang around for long.

The screen builder was iag to generate/compile and the iap to run, if I remember correctly you edited the source for the screen, which was essentially a series of question/answers and put %sw when you wanted to return control to the terminal, nightmare if you got it wrong and wiped out your source code, very limited but surprising what you could do with a bit of lateral thinking. This later became iad/iac which may have been the pre-cursor to forms 2.3 if it wasnt indeed 2.3. Sadly I destroyed most of my listings/logs years ago though I do have some pro-cobol somewhere, which I kept for the sake of posterity.

User exits were fun, can’t remember exactly but didn’t these require a recompilation/remake of the oracle kernel ?

7. Nigel Thomas - December 20, 2011

UFI was indeed the early name for what became SQL*Plus (I used it to test Oracle in 1983). See http://www.orafaq.com/wiki/SQL*Plus_FAQ for more background.

IAF was the Interactive Application Facility – forerunner of SQL Forms, Oracle Forms, Oracle Developer. It consisted of IAG (G for Generator – the bit that asked the questions), IAP (P for Processor – the run time) and eventually IAD (D for Designer – the develop-time tool that allowed you to see the screen layout and edit the triggers).

Of course in those early days IAD was practically unusable so real men (of both genders – there seem to have been more girl geeks around back then than now) edited the text files that contained the answers to the questions. Heaven help you if you upset the Q+A sequence.

User exits only required compilation of the exits (in PRO*C) and re-linking of IAP – because the user exits were part of Forms (ie they ran on the client, not the server). User exits could get very elaborate – in the late 80s Oracle Financials depended on a library of user exits for Forms 2.3 called Application Foundation which ran to over 1 million lines of code. AF – which became AOL (Application Object Library) is still recognisably knocking around in the eBusiness Suite version 12, more than 20 years later, though hopefully it will fade away as Fusion Apps take over.

8. Greg - January 3, 2012

I had a boss that could not pronounce r. Referential integrity became wefewential integwity. He sounded like Elmer Fudd hunting wabbits. The system I supported was written in Oracle 6 and used the app to enforce referential integrity, but the customers wanted it turned on in release 7.

He finally got frustrated during a meeting where referential integrity was being discussed and declared that no one was to use the R word anymore… They did end up implementing it, putting him out of his misery.

9. flashdba - September 18, 2012

Just found this article after reminiscing about SQLDBA in Oracle 6 with it’s fantastic “monitor” command… then Googling to try and remember when Oracle implemented referential integrity. I love the quote at the top of this article, “Oracle’s implementation conforms to ANSI standard, although referential integrity will not be enforced until version 7″… even then Oracle Marketing had a way with words!

10. pardydba - October 24, 2012

I love it. This is great.

I started back on Oracle 7 around 1996. Our backup was the output of a ton of exp commands cpio’d to tape. 1GB database on a RAID array with about 40MB free, all running on a Compaq Pentium-90 with the original Pentium FDIV bug. I showed up just after they had finished converting from Oracle on PyramidOS to SCO Unix. Hacked Forms 6.5 and Pro*C, felt like a real master when I started using perl to write reports that needed to be prettier than sqlplus would format them and stopped having to do it all in Pro*C. Then started writing character-based GUIs in Pro*C to get away from all the onCommit, onExit hooks in Forms. Dialing in at 1:00AM to export, truncate, and reimport sales history tables just to free up a couple extents so people could enter bills of lading. PCTINCREASE 50 was once a good idea.

The database powered a glass factory, our locking issues were perpetually due to some guy out on the factory floor not hitting commit on his Wyse VT99. A stack of 6 DigiBoards all hooked up to the P90 ran miles of serial lines at 1200 baud all through the shop floor. I’d have to break out of coding to go re-solder wires each time someone drove a forklift into the giant Printronix line printers.

11. William Robertson - October 24, 2012

The only sensible values for pctincrease seemed to be 0 and 100, and nobody ever believed me about 100. This was a long time ago though.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: