Parameters that enable and control Query Optimizer Features

You know that Query optimizer is responsible to determine the best execution/explain plan. In many cases you may be astonished that the data is same in both database and you have gather statistics but both database give different execution plan. The possible reason for it it the variation of initialization parameter between two database. Now [...]

Read Full Post »

Choosing an Optimizer Goal

By default, the goal of the query optimizer is the best throughput. This means that it chooses the least amount of resources necessary to process all rows accessed by the statement. Oracle can also optimize a statement with the goal of best response time. This means that it uses the least amount of resources necessary [...]

Read Full Post »

Optimizer Operations while executing SQL statement

Whenever you write an sql query, the query can be executed in many different ways, such as full table scans, index scans, nested loops, and hash joins. The query optimizer checks the various ways and determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and [...]

Read Full Post »

ORA-01450: maximum key length (3215) exceeded

Error Description Whenever I try to rebuild index online then it fails with message ORA-00604: error occurred at recursive SQL level 1 along with ORA-01450: maximum key length (3215) exceeded. Below is the scenario. SQL> create table tab1(a varchar2(3000),b varchar2(2000)); Table created. SQL> create index tab1_I on tab1(a,b); Index created. SQL> alter index tab1_I rebuild [...]

Read Full Post »

Optimizer_mode – ALL_ROWS or FIRST_ROWS?

Out of all Oracle RDBMS modules, optimizer code is actually the most complicated code and different optimizer modes seem like jack while lifting your car in case of a puncture. This paper focuses on how optimizer behaves differently when you have optimizer mode set to ALL_ROWS or FIRST_ROWS. Possible values for optimizer_mode = choose/ all_rows/ [...]

Read Full Post »