We're migrating from Oracle 9i running on a single box (in MTS mode) to an
Oracle 10g RAC cluster. For those that don't know, RAC is Real Application
Server and it's (supposed to be) one of the ways that you create a highly
scaleable Oracle system. Before we began I naively thought the migration
would... a) cost a fortune, b) be a pain in the ass for the DBAs and IT folks
but c) be relatively painless for us software engineers. To make a long story
short, I was right about the first two points.
The problems for us stem from the sequence generation facilities. In Oracle
you can configure the sequence generator to work in different ways but the way
we've had it working for over two years is to generate numbers one right after
the other. The numbers that come out of our sequences are in order and there
are essentially no gaps between successive items.
One of the side-effects of this approach is that you can treat any such
sequence-backed column as a kind of time-stamp; you can order records by their
effective creation date just by sorting on the sequence-backed field.
When you move your database to a system that runs on multiple machines (i.e.
RAC) you basically lose the ability to have your sequences work this way. More
precisely, you can configure them to work that way but doing so means that
the nodes in your cluster will need to constantly coordinate with one another
and the ensuing chatter will take a big bite out of your performance.
So in switching to RAC we lost the comfort of being able to assume that
successive sequence values would go in increasing order. Calling
sequence_name.NEXTVAL three times in a row might get you: 47, 38, 52.
We had to hunt down every query in our application that ordered by a sequence-
backed field. That in itself is no easy task when you have thousands of
queries scattered across hundreds of files, some written in PHP, some in Java
and some in PL/SQL. The most sophisticated tool available was grep and we had
to manually inspect dozens of candidate queries to figure out which ones to go
after.
Then began the hard work of actually fixing the queries. We had to change all the "ORDER BY" clauses to use a different field (or set of fields), specifically a DATE field. Unfortunately, a lot of the underlying tables didn't have any appropriate field so in those cases we needed to modify the schema. The problem then arises of what do with the millions of existing rows that were created before we needed to record the date. We ended up giving them an arbitrary date in the past but that just meant that the "ORDER BY" clause now needed to act on two columns instead of one.
All in all, it took three developers about two weeks.
* I started writing this post months ago but it got waylaid and forgotten.
In finishing up I've tried to write in a manner consistent with my
thoughts and experiences at the time of inception. I'd like the post to approximate my thoughts at the time