Use UNION instead of OR
Consider using UNION when you have to use OR condition on 2 index columns
… 07 Performance Improvement Consideration
Reasons for Index Suppression (Accidental)
There are many reasons why an index might not be used even though you have an index on the column you are searching for.
Use of <> operator on an indexed column
Use o a SUBSTR function on an indexed column
use of arithmetic operators (+,_,*,/) on an indexed column
Use of TRUNC function on an indexed column
Use of || operator on an indexed column
Data type conversion on an indexed column
Use of IS NULL/ IS NOT NULL on an indexed column
… 06 Index Suppression
Oracle execution plan sample
An execution plan is a hierarchical listings of steps.
Each step is one of a few basic data access operations known to the database server.
The most complex statement can be broken down into a series of basic operations
To execute any SQL statement oracle has to derive an ‘Execution Plan’
It is nothing but a tree which contains the order of steps and a relationship between them.
What should you look for in the execution plan?
You should be able to look at the execution plan and assess if the optimizer has made any mistakes in its estimations or calculations, leading to a sub-optimal plan.
Cardinality : Estimate the number of rows coming out of each operations.
Access Method: The way in which data is being accessed , via either a table scan or index access (full table scan, index unique scan, range scan etc)
Join Method: The method (e.g. hash, sort-merge join , nested loops, sort join, etc) used to join tables with each other.
Join Type: The type of join (inner, outer, semi, anti…)
Join Order : The order in which the tables are joined to each other.
Partition Pruning: Are only the necessary partitions being accessed to answer the query?
Parallel Execution : In case of parallel execution, is each operation in the plan being conducted in parallel? Is the right data redistribution method being used
… 04 Explain Plan
What is my Address?
In Oracle RowID is the physical address of a row. Oracle knows uses ROWID to identify the location of record in the database. RowID’s are binary addresses of the location where the row is stored.
Example: Select rowid, s.salesorder from sales_history s
… 03 Accessing Tables
Cost based optimization
What is cost based optimization?
Optimization is the selection of the best plan to un a SQL.
Oracle cost based optimizer is designed to determine the most efficient way to carry out an
SQL statement. but it can’t do this without good, up to date statistical information on
the data being accessed.
With the cost based approach, the optimizer factors in statistical information about the contents of the particular schema objets (tables, clusters, or indexes) being accessed. statistical information includes data on some of the items listed below
Row count, Row length, Bytes, Percentage free
Statistics, Last Analyzed date
Pecent of Dsitinct Values on coulmn
All indexes on the table (including statistics)
All constraints on the table.
… 02 Oracle Optimizer and its execution plan