SQL Optimization: Optimizer Process

Query Optimizer SQL Processing
--------------------------------

1. Parsing: 
    Optimizer:
        -> Syntax Check
        -> Objects exists in sql [semantic check]
        -> Search shared pool, if statement is ever executed [cache]
            -> if this is the first time optimizer received the sql, it will create a hash of the sql statememt. and then saved it. [sha256]
           
2. Optimzation Stage:
        -> generate possible execution plans
        -> save the selected execution plan along side with parsed sql hash in shared pool.

3. Row Source Generation:
        -> convert execution plan in iterative binary format.
        -> similar to compiling execution plan.
        -> expensive
       
4. Execution: 
        -> Excuting the Row Source Tree produced by row source generator.
       
------------------------------------------------------------------------

Hard Parse: 1->2->3->4 [first time always hardparse]
when the optimizer perform all above steps to execute SQL statement, it is called Hard Parse.

Soft Parse: 1, 4 Only: [no 2,3]
------------------------------------------------------------------------

Lowest Cost Plan is chosen: CBO

------------------------------------------------------------------------

Execution Plan overall cost =
        -> selectivity [stat based]
        -> Cardinality [stat based]
        -> Cost: CPU, I/O and Network
       
*selectivity and cardinality changes based on data changes in objects

-> after any signifacant data changes: we should update these stats [gather stats program in Oracle apps]

-------------------------------

1. selectivity[relative no]: (retrieved rows/total rows)
            -> always <= 1
    [so how many rows you are selecting the query]
 
2.  cardinality [Rows]: no of rows returned by each operation in execution plan [ex. select empname from emp, dept where e.deptid= d.deptid and deptid = 10]           
    ex. if 10 employees are returned by sql, then cardinality is 10


   
Why selectivity and cardinality are important
----------------------------------------------
1. 10/10000, selectivity = 0.0001, get the rowid of the rows and search in index.

2. 5000/10000: low selectivity ex: search in index for half rows, so its better to skip index, access table dierctly is better.

3. cardinality: similar to selectivity, cardinality concept, Optimizer choose, if Index to be used or not.

Since cardinality is stat based, and optimizer look at these stats for
cardinality and then choose the execution plan, it most of times issue with stats which causes sub-optimal plan.
------------------------------------------------