Month: December 2009

Ten Years on Oracle

Almost exactly ten years ago, as all the Y2K bug projects were quietly winding down, I’d been getting ready for a new decade, which for me was full of uncertainty and promise. It was in 2000 that I got married, and started my first job in the I.T. industry (but not in that order). Many people thought the world was going to end, but for me it felt like the dawn of a new personal era.

After eight years of tertiary study, I’d finally gotten my degree and was fortunate enough to have landed a job as an Oracle analyst programmer at the Valuer General’s Office in Perth. Working there turned out to be a perfect introduction to the industry – I learned a lot about Oracle, about running I.T. projects, and about working productively.

There were some great people there, who mentored me and gently guided me in how to think about database programming. Specifically, it was there I started throwing off the shackles of procedural thinking, to be replaced with a relational approach, thinking about sets of data instead of individual rows.

These mentors never said, “just do it this way because I said so.” They’d allow me to make the mistakes first, then show me the better way, and explain why. For the most part, however, they just let me learn by doing, which (for me at least) is the best way to learn.

Over the last ten years I’ve gained valuable experience in a number of quite different projects, in different types of environments, different team structures, and different kinds of people, all working with Oracle. I’m open to trying new technologies, and always seek opportunities to expand my experience with different languages and software; but I think if I get to the end of my career, having never worked on any system without the Oracle database, I won’t be at all disappointed. It’s certainly not a perfect system, but there’s so much to like, so much to learn and explore.

Only God knows what my future will hold, but if the next ten years are anything like the last, they’ll involve a lot of learning, learning, and more learning. As I get older it might take a greater effort, but I always want to be the kind of person who never takes anything for granted, never assumes something just because someone said it, and who mentors others with patience and grace.

Happy new decade, everyone.

How to Change the ORDER BY When You Can’t Modify the SQL

Is it possible to change the ORDER BY on a query, if the SQL is defined in a wrapped PL/SQL package? A colleague asked me this interesting question today – and the answer was quite simply, Yes and No.

If the SQL already has an ORDER BY, then the answer is No. Fortunately, in our case, the SQL didn’t have any ORDER BY clause:

SELECT a.addressid
FROM address a, addresstype t
WHERE a.locationid = :locationid
AND a.typeid = t.typeid
AND t.typecode = 'STREET';

The plan currently being picked by the CBO (Oracle 9.2.0.8) is:

NESTED LOOPS
   TABLE ACCESS BY ROWID addresstype
      INDEX RANGE SCAN pkx_addresstype
   INDEX RANGE SCAN ix_address

For background, here are the relevant schema details:

TABLE address
(addressid NUMBER PRIMARY KEY
,locationid NUMBER NOT NULL
,typeid NUMBER NOT NULL --FOREIGN KEY to addresstype
);

INDEX idx_address (locationid, typeid, addressid);

TABLE addresstype
(typeid NUMBER PRIMARY KEY
,typecode VARCHAR2(30) NOT NULL
);

typecode is actually unique, but no constraint has been declared to the database.

Because the first step in the plan (the TABLE ACCESS on addresstype) only ever gets one row (in practice), the index range scan on ix_address causes this query to return the rows in ascending order of addressid.

The process that runs this query gets rows one at a time, does some work to “check” it against some other data, and once it finds a “match”, it stops getting more rows.

This design probably seemed like a good idea at the time, but now the business would like this process changed so that in the (rare) occurrence when more than one row would “match”, the process should favour a more recent address rather than an old one. For our purposes, “recent” may be approximated by assuming that bigger addressids are more recent than smaller addressids.

We can’t change the code without a lengthy and costly call to the application vendor; it would be better if we can change it ourselves. We cannot change the PL/SQL package in which this query is defined; so what can we do? Can we change the order in which rows are returned from this query?

The answer is yes – firstly, the query has no ORDER BY, so the order of rows is affected only by the query plan. If we change the query plan, which we can do with a stored outline, the order of rows can be changed.

So in this case, all we have to do is create a stored outline to add an INDEX_DESC hint:

SELECT /*+INDEX_DESC(a)*/ a.addressid
FROM address a, addresstype t
WHERE a.locationid = :locationid
AND a.typeid = t.typeid
AND t.typecode = 'STREET';

This causes the following plan to be used:

NESTED LOOPS
   TABLE ACCESS BY ROWID addresstype
      INDEX RANGE SCAN pkx_addresstype
   INDEX RANGE SCAN DESCENDING ix_address

With the stored outline, the query now returns the rows in descending order of addressid.

NOTE:
If you want to use this approach, it is highly dependant on being able to find a query plan that produces the ordering that you want. This may mean creating a suitable index designed strategically for this purpose, if one doesn’t exist already. Even creating indexes won’t guarantee that you’ll be able to find a plan that gets the order that you want; generally I’d say this approach will only be feasible for relatively simple queries.