The program for data retrieval for a given SELECT statement will be compiled at runtime[1]  in difference to other data processing languages for example like COBOL where the program has to be compiled before runtime. Which physical objects like Datafiles, indexes are accessed is fix defined in a COBOL program. Also the kind of OPERATION like sort a merge sort or an index access is defined. In SQL processing not only a program has to be compiled to get the data also many decisions at runtime has to be done which physical structures should be used to get the fastest access path.

This is the task of the Optimizer. It has to choose the most effective access path to the data. An Access path better called one step in the EXECUTION PLAN for example could contain a FULL TABLE scan or an INDEX RANGE Scan. The Optimizer needs a model to decide which PLAN is the better or so called cheaper one. The are two approaches of optimizer strategies the oracle database has built in.

• The rule base optimizer, called RBO, choose the best Access Path depending on a static ranking model.
• The cost based optimizer, called CBO, estimates costs depending on underlying statistics of the Tables, Indexes and the global system.

Searching the best EXCUTION PLAN for a statement is the same work like a chess program searches for the best move. This Search dependent on how many tables are joined could need a lot of time and system resources like CPU and I/O.

To be continued

Karl

[1] After first compile the compiled program is cached (in Shared Pool) for reuse. If the same SQL is executed again the no further compilation (Hard Parses) needs to be done.