If you've got several queries that are all using DECODE and/or CASE to map one value to another, consider creating a mapping table and joining that into your queries instead. It will make them easier to maintain, and depending on your indexes it might even speed up some queries.

Recently I've been of service to some of my colleagues who have been writing SQL Queries. Based on their feedback, I have helped them write faster-performing queries, in a shorter period of time, with fewer mistakes, and in such a way that maintenance was simpler. If you have found any use in what I'm about to present, you can thank them for encouraging me to dust off my blog and make another contribution to the community.

I listened intently to the new Oracle programmer as he described all the struggles he's been having on his first big project. As I've done many times already in his short career, I interrupt with some words of wisdom. "It's time to add Global Temporary Tables to your toolbelt." "What are those?" he asks, as he opens the directory with the Oracle documentation. I smile. He has already learned

You pick up the candidate's resume and it proudly proclaims "SQL Expert: 10 Years." Your boss trusts you, as the technical expert on the team, to participate briefly in the interview to gauge this individual's knowledge of SQL. Where to begin? I have asked literally hundreds of different questions during interviews over the past decade. Some were simple questions that were nevertheless

While attempting to insert several rows into a table in our Oracle database, a colleague dutifully copied the exact ANSI/ISO SQL standard syntax for his purposes. Guess what happened?

I've got very few beefs with Oracle. It is extremely complicated and tough to learn compared to other relational databases, but that's partially offset by the tremendous documentation, and the huge Oracle community. Don't get me wrong, I love Oracle. I'm regularly impressed by the sophisticated and often clever ways it handles the requirements of an RDBMS, and quick to recommend its use. But

Everybody learns their lessons, and so will you. The only variable is how expensive the lesson is. While there is no substitute for direct, first-hand experience, the cheapest way to learn a lesson is to benefit from the experience of others. My favourite source of cheap lessons is Ask Tom. I've compiled a sample collection of Tom's Wisdom from just the articles updated in the past week.

As devoted readers may have noticed, my new job doesn't involve nearly as much work with Oracle. I stay sharp by reading Ask Tom, the very site that has provided me with 90% of the answers that I can't find in Oracle documentation or figure out on my own. Those of you who may find it nerdly to spend lunch hours reading Oracle Q&A are actually really missing out. It's far more entertaining than

Here is another advanced concept that will come in useful when solving Oracle problems. Imagine you're trying to create a result set where the rows need to be columns, or vice versa. In essence, you need to "pivot" rows into columns, or vice versa. That is a very common requirement, and this is where you need to look at a pivot (or crosstab) query to get the job done. As always, when you

You have a table with all your company's financial transactions. There is another table which references a subset of these financial transactions (ie. transactions with certain properties). Your current solution to maintain the integrity is to set up a foreign key, referencing the transactions table, and then write a trigger to make sure that any records reference only transactions that have [...]