An age old problem with SQL is pagination through a row set. You want to display rows one page at a time, in some sort of order. The issue is architectural: SQL is a set oriented language. It deals with rows as a group, not individually, making it impossible to paginate through a set because that would require navigation from row to row. There are work arounds, there are potential programming bugs, and there is a Database 12c New Feature: the row limit clause. All demonstrations below are done with 12c final beta, release 12.1.0.1.0. Below is the old and the new way, with a bit of reverse engineering.

12 MArch 2013 update: Content removed - it exceeded what is permitted for the pre-release status of the product.
Sorry about that, will repost when I can.
JW.

A 12c new feature that may have significant performance and scalability implications is the multithreaded database. All releases of Oracle below 12.x (and 12.x by default) run on Unix in a multi process model. The various background processes (typically at least fifty for 12c, thirty or forty for 11g) run as separate operating system processes. Furthermore, the dedicated server processes that support sessions also run as separate operating system processes. An Oracle instance will usually be running as hundreds (perhaps thousands) of processes. The multithreaded database changes this.

12 MArch 2013 update: Content removed - it exceeded what is permitted for the pre-release status of the product.
Sorry about that, will repost when I can.
JW.

The COST column cannot be used to compare execution plans. Punkt.

Matthew Morris (who makes many constructive and knowledgeable contributions to the forum) has written a series of study guides for the OCA/OCP exams. I asked him if I could have a copy of one to review. The result: it's very good. This is a copy of the review I put up on Amazon.

The other day, I was asked how to move a table from one schema to another. The answer, as we all know, is "you can't do that: you have to create a new table as a copy of the old one, or use export/import. It will take a long long time." Not true.

I've been asked by the publishers to review "Oracle Database 11g Release 2: Performance Tuning Tips and Techniques" by Richard Niemiec of TUSC. Conclusion: it is good but not perfect. This is an extended version of the review I put up on Amazon.

The old Oracle join syntax really should be consigned to history.

Why do you sometimes not get partitionwise joins? Because the optimizer isn't clever enough. Reference partitioning has many benefits, one of which is that the optimizer understands it. You will always get a partitionwise join if your tables are reference partitioned.

Several times I have had to deal with people who do not want to define constraints. I have never understood why they don't, because my experience is that the more constraints you can define, the better Oracle will perform. If anyone knows where the idea that not defining constraints is a Good Thing comes from, I would be interested to know.

Following are two very simple examples of constraints allowing the optimizer to develop better plans.

First, foreign key constraints. These give the optimizer a lot of information about the data that may mean it can cut out whole tables from a query.