Parser and Optimizer in SQL |Execution Plan in SQL | SQL Performance Tuning |Difference Between Rule Based and Cost Based Optimizer

SQL Optimizer

Parser and Optimizer in SQL:

In Previous articles I just explained about the Basics of Performance Tuning.In this article I will explain Parser and Optimizer in SQL and how it works from inside.What exactly the optimizer is?Optimizer is nothing but the execution of query in optimum manner.Parser and Optimizer in SQL is most efficient way of processing the query.SQL parser ,SQL Optimizer and source code generator compiles the SQL statement.This article gives you basic idea of Parser and Optimizer in SQL.

IF you want to see the Stepwise execution of SQL Select Statement Click Here

Parser and Optimizer in SQL

1.What is SQL Parser?

When SQL Statement has been written and generated then first step is parsing of that SQL Statement.Parsing is nothing but checking of SQL Query.Parser Performs following functions:

1.Syntax Analysis:

The parser checks for SQL statement syntaxs.If the syntax is incorrect then parser gives the incorrect syntax error.

2.Semantic Analysis:

This checks for references of object and object attributes referenced are correct.

Parser and Optimizer in SQL
SQL Optimizer


There are two types of optimizers:

1.Rule Based Optimizer

2.Cost Based Optimizer



1.Rule Base Optimizer:

When we execute any SQL statement ,the optimizer uses the predefined rules which defines what indexes are present in the database and which indexes needs to be executed during the execution.Rule Based optimizer is used to specify which table is been full scanned and which tables are taking the indexes during the execution.In Earlier the only optimizer which is used by Oracle is Rule Based optimizer

“Rule Based Optimizer  specifies the rules for how to execute the query.”

Real Life Scenario:

If Table contains the Employees records and 100 records in which 80 Employees  from department named ‘Oracle’.

When we run the query.and there is index on Department_name

Select * from Employee where Department_name=’Oracle’; 

If We execute the above query then the table is full scanned so that because of index and rule based optimizer the time taken to execute the query high if the query is executed with the indexes. The Biggest disadvantage of Rule Based Optimizer is it does not take data distribution in to account. For data distribution Cost Based Optimizer is used.



2.Cost Based Optimizer:

Cost Based Optimizer (CBO) uses the artificial intelligence to execute the Query. The Optimizer itself  decides the execution plan based on the cost of query. The cost based method means the database must decide which query execution plan to choose. It decides the query execution plan by deciding the statistical information based on  the database objects.(tables, indexes and clusters).The Oracle cost based optimizer is designed to determine the most effective way to carry out the SQL statement.

“Cost based optimizer considers the statistical information of the table for query execution”

Real Life Scenario:

If Table contains the Employees records and 100 records in which 80 Employees  from department named ‘Oracle’.

When we run the query.and there is index on Department_name

Select * from Employee where Department_name=’Oracle’; 

When We use the rule based  Optimizer then it will take the unnecessary index and the performance will slow down if the index is been taken.Using cost based optimizer it will take the statistical information of the table named Employee.

Following is the Stepwise Execution using  the above SQL Statement  using optimizer:

Execute Statement: Select * from Employee where Department_name=’Oracle’; 

Index Used:On Department name: IDX_DEPT_NAME


1.Step 1:

The Parser checks both syntax and Semantics of the Query.

Select * from Employee where Department_name=’Oracle’; 

Syntax correct.Semantics correct.

2.Step 2:

Then Optimizer Decides which method to be used.If the oracle version is older then only Rule based Optimizer is used and index has been taken.If New Oracle version is there then Cost based optimizer decides on the table data that index is to be used or not.It determines the most efficient way of producing the result of the query.

3.Step 3:

The Row source generator receives the optimal plan  and outputs the execution plan.

4.Step 4:

Full Table Scan if Cost Based Optimizer is used:

What is Full table Scan?

The full table scan reads all the records from the table and filter out those which records does not meet the criteria of selection.Each row from the column is examined and determined which is used in where clause.

Full tables scans are faster when there is large volume of data because of its larger I/O calls than the index scan..

The Optimizer uses Full table Scan in Following cases:

1.When Specified Column is not Indexed

2.To Fetch very large ammount of data

3.High Parallelism

So if in our example the data of department name is ‘Oracle’ is 80% of the total records(assumption).Then the table is full scanned.If the data is less than and divided properly in the table then cost based optimizer fetches the index and scans the table using index.

Rule Based Optimization uses Index Scan:

What is Index scan?

When optimizer uses the indexes to fetch the data then the scanning technique is known as Index scan.It retrieves the data specified by index.First oracle optimizer searches for the indexes on the column and then it executes the indexes and based on that indexes the execution of the SQL statement is been performed.

So in our example if optimizer is Rule based optimizer then it fetches the index from the table and based on that index the execution is done.

Hope everyone get the basic idea of the Parser and Optimizer in SQL and how parsing and optimizing is done in SQL.In next article i will explain how to check and analyse the query execution plan and tune the query with Real Life Example in detail.If You like this article dont forget to comment in comment section.If you want PDF notes of this article Comment in comment section you will get it.













About admin 111 Articles
Oracle Consultant with Vast Experience in Oracle BI and SQL PLSQL Development


  1. Thanks for Suggestions..I will check each and every post and correct the spellings if any correction required..Thanks again 🙂

  2. Great site you’ve got here.. It’s hard to find high quality writing like yours nowadays. I truly appreciate individuals like you! Take care!!

  3. Awesome Article ….Could you please post some interview question & Answer as well related to performance tuning
    Thanks again for this article.

    • Sure Dinesh..

      Step by Step i will share everything related to production support,database development related stuff on this site 🙂

  4. Awesome Articles…..Sir, you are really doing great work……Keep it up….These all articles are really helpful for me….Thanks a lot …

  5. Hi
    please tell me about these
    1) What are oracle sessions?

    2) Does one connection object always relate to one oracle session.?

    3) can one oracle session be shared by another connection started by the same user.?

    • Hi Nandini,
      Following are answers of your question:
      1.When you log in to your oracle server your session will start.Multiple users have multiple check the sessions oracle in built view is used named-V$SESSION
      2.Connection object not always related to oracle sessions.There are 2 connection objects server connection object and client connection objects.Client connection object related to sesson..
      3.Yes you can share the connection….

  6. Valuable information…. I like u r blog so… much yar… daily atleast once I read for topics revision purpose…. thanks for keeping ur knowledge on sites …. but one small request kindly post performance tuning topic… thanks in advance…( bro’s and sis’s)

9 Trackbacks / Pingbacks

  1. SQL Interview Questions For Tech Mahindra | Important SQL Interview Questions
  2. Interview Questions on SQL Performance | SQL Performance Tuning Questions | 20 Interview Questions of SQL Performance
  3. Basics of SQL Performance Tuning | Performance Tuning for SQL | How to improve Query Performance?
  4. SQL Indexes |Performance Tuning |Normal Index | Unique Index| Bitmap Index | Global Index | Local Index
  5. Interview Questions for HCL | SQL Interview Questions for HCL | Interview Questions Asked in HCL | HCL Interview Questions | 20 Most Important SQL Interview Questions for HCL
  6. Operators in SQL | SQL Operators | Arithmetic Operators | Logical Operators | Comparison Operators | Negation Operators | Operators in SQL with Examples
  7. Interview Questions for TCS | SQL Interview Questions for TCS | Interview Questions Asked in TCS | TCS Interview Questions
  8. Sed Command in Unix with examples | Stream Editor in Unix | Pipe Command with Examples | Head and Tail command with examples
  9. Unix Directory Commands | Create Directory in Unix | Mkdir Command with Example | PWD command with example | Rmdir command to remove directory | Cd command to change directory

Comments are closed.