How does sql optimization work internally?

Question

My previous question:

http://stackoverflow.com/questions/2112567/date-of-max-id-sql-oracle-optimization

In my previous question, I was finding different ways of finding the date of the record with the highest id number. Below are several of the offered solutions, and their 'cost' as calculated by explain plan.

select date from table where id in (
select max(id) from table)

has a cost of 8

select date from table where rownum < 2 order by id desc;

has a cost of 5

select date from (select date from table order by id desc) where rownum < 2;

also has a cost of 5

with ranked_table as (select rownum as rn, date from table order by id desc)
  select date from ranked_table where rn = 1;

has a cost of 906665

SELECT t1.date
FROM table t1
LEFT OUTER JOIN table t2
  ON t1.id < t2.id
WHERE t2.id IS NULL;

has a cost of 1438619

Obviously the index on id is doing its job. But I was wondering, in what cases would the last two perform at least as well, if not better? I want to understand the benefits of doing it that way.

This was done in Oracle. All varieties can be discussed, but kindly say what your answer applies to.

Answer 1

Use solution #1 if you want the most portable SQL that will work on a wide variety of other brands of RDBMS (i.e. not all brands support rownum):

select date from table where id in (select max(id) from table);

Use solution #3 if you want the most efficient solution for Oracle:

select date from (select date from table order by id desc) where rownum < 2;

Note that solution #2 doesn't always give the right answer, because it returns the "first" two rows before it has sorted them by id. If this happens to return the rows with the highest id values, it's only by coincidence.

select date from table where rownum < 2 order by id desc;

Regarding the more complex queries #4 and #5 that give such a high cost, I agree I wouldn't recommend using them for such a simple task as fetching the row with the highest id. But understanding how to use subquery factoring and self-joins can be useful for solving other more complex types of queries, where the simple solutions simply don't do the job.

Example: given a hierarchy of threaded forum comments, show the "hottest" comments with the most direct replies.

Answer 2

Almost all decent databases have introduced instructions called optimizer hints which are not portable, there are default costs on joining tables, you can advice the query optimizer to use nested loop joins or dynamic table hash joins. A good explaination for oracle you find in oracle performance tuning guide