Translate

Showing posts sorted by date for query optimizer. Sort by relevance Show all posts
Showing posts sorted by date for query optimizer. Sort by relevance Show all posts

Deterministic Functions

What are Deterministic Functions? How they help in improving Query Performance?

A function is called deterministic if it returns the same result value whenever it is called with the same values for its arguments.

When you define a function, you can simply add the DETERMINISTIC option to the declaration section, making sure that the function (or any functions or procedures it calls) does not depend on the state of session variables or schema objects as the results may vary across invocations.

This option instructs the optimizer that it may use a cached result whenever it encounters a previously calculated result.
Function-based indexes can only use functions marked DETERMINISTIC. The same goes for materialized views with REFRESH FAST or ENABLE QUERY REWRITE. One restriction is that you cannot define a nested function as deterministic.
EXAMPLE: SUBSTR is a Deterministic Function because each time you call SUBSTR with the same parameter values the same result will be returned

NOTE: A function should never be created as deterministic unless it will ALWAYS return the same value given the same parameters.

Let’s take an Example of Deterministic Function with the following code:

CREATE OR REPLACE FUNCTION RETURN_STRING (INPUT_STR IN VARCHAR2, P_START IN INTEGER, P_END IN INTEGER) RETURN VARCHAR2 IS
BEGIN
RETURN (SUBSTR (INPUT_STR, P_START, P_END- P_START+1));
END;

As long as you pass in, for example, “ABCDEF” for the string,3 for the start, and 5 for the end, RETURN_STRING will always return “CDE”.

Now, if that is the case, why not have Oracle save the results associated with a set of arguments? Then when the next function is called with those arguments, it can return the result without executing the function!

You can achieve this effect by adding the DETERMINISTIC clause to the function’s header, as in the following:

CREATE OR REPLACE FUNCTION RETURN_STRING (INPUT_STR IN VARCHAR2, P_START IN INTEGER, P_END IN INTEGER) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
RETURN (SUBSTR (INPUT_STR, P_START, P_END- P_START+1));
END;

By taking this step, I can now use this function in a function-based index. Even better, Oracle will, under very specific circumstances, cache the IN-Argument values and the RETURN values, and then avoid executing the function body if the same inputs are provided.
You must declare a function DETERMINISTIC in order for it to be called in the expression of a function-based index, or from the query of a Materialized View if that view is marked REFRESH FAST or ENABLE QUERY REWRITE.


Oracle has no way of reliably checking to make sure that the function you declare to be Deterministic actually is free of any side effects. It is up to you to use this feature responsibly. Your deterministic function should not rely on package variables, nor should it access the database in a way that might affect the result set.


NOT NULL and INDEXED Column

How INDEXED Columns behaves when not defined as NOT NULL?

Sometimes, while Declaring Table columns we do not declare them as NOT NULL even if we know that column will never be NULL. In such cases Optimizer has to assume that column can accept NULL values while generating Execution Plan which results in Full Table Scan.

Let’s check this with the help of below Example:
I have a Table below without any Constraint and Index.
SQL> DESC EMPLOYEE_DATA;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 EMP_NO                                                                           NUMBER
 ENAME                                                                            VARCHAR2(10)
 DEPTNO                                                                           NUMBER
 HIREDATE                                                                         DATE
 DNAME                                                                            VARCHAR2(10)
 DLOC                                                                             VARCHAR2(10)
 SALARY                                                                           NUMBER(8,2)
 JOB_ID                                                                           VARCHAR2(20)

SQL> SET AUTOTRACE TRACENLY EXP
SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 4276744253
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    81 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEE_DATA |     1 |    81 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("HIREDATE" IS NULL)

Note
-----
   - dynamic sampling used for this statement (level=2)
In above statement, Full Access path was followed which was obvious and expected that Optimizer will use Full Table Scan as there were no Index no Constraint been used.


Let’s create an Index on Above Table assuming that now Optimizer will use Index Scan Path.

SQL> CREATE INDEX INDX_HD_NAME ON EMPLOYEE_DATA(HIREDATE,ENAME);
Index created.

SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 4276744253
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    81 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEE_DATA |     1 |    81 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("HIREDATE" IS NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)


So, even after creating Index on Column it has followed Full Scan access path. Why?
The above index supports the query only if the column ENAME has a NOT NULL constraint. 

Removing NOT NULL Constraint renders the Index Unusable state for the above Query.

Let’s try declaring ENAME column as NOT NULL and see how it helps in generating better execution plan.
SQL> Alter table EMPLOYEE_DATA modify ENAME NOT NULL;
Table altered.

SQL> DESC EMPLOYEE_DATA;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 EMP_NO                                                                           NUMBER
 ENAME                                                                   NOT NULL VARCHAR2(10)
 DEPTNO                                                                           NUMBER
 HIREDATE                                                                         DATE
 DNAME                                                                            VARCHAR2(10)
 DLOC                                                                             VARCHAR2(10)
 SALARY                                                                           NUMBER(8,2)
 JOB_ID                                                                           VARCHAR2(20)

SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 1371098230
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    81 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE_DATA |     1 |    81 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX_HD_NAME  |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("HIREDATE" IS NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)

As you can see Optimizer has followed Index Scan path as Optimizer doesn’t need to Assume about NULLs.


Let’s do the same Exercise with Function returning your Table column instead of directly using them in your Query.
SQL> Create Index INDX_EMPNO ON EMPLOYEE_DATA(EMP_NO);
Index created.

SQL> CREATE OR REPLACE FUNCTION RETURN_EMPNO(EID IN NUMBER) RETURN NUMBER
DETERMINISTIC
IS BEGIN
RETURN EID;
END;
  /
Function created.

You will need to create DETERMINISITC Function when you know that function will always return Unique value. If we skip DETERMINISTIC in above function, then you will receive below error while referring it creating Index.

ORA-30553: The function is not deterministic


If the PL/SQL function is truly deterministic  (it always returns a unique value for all input values) then you can just add the "deterministic" keyword to the function definition. If the function is deterministic, mark it DETERMINISTIC. If it is not deterministic (it depends on package state, database state, current time, or anything other than the function inputs) then do not create the index. The values returned by a deterministic function should not change even when the function is rewritten or recompiled.
SQL> Drop Index INDX_HD_NAME;
Index dropped.
SQL> CREATE INDEX INDX_HD_NAME ON EMPLOYEE_DATA(HIREDATE,RETURN_EMPNO(EMP_NO));
Index created.

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 4276744253
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    81 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEE_DATA |     1 |    81 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("HIREDATE" IS NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)

We know that Function name RETURN_EMPNO will always return Unique and NOT NULL Values but Optimizer doesn’t have any idea what this function does. For Database, this is just a function which returns a Number. Although, we have Index created on EMP_NO but still Database can not use the Index for the Query.

So, if you know that Function never returns NULL, then you can change the Query to make use of Created Index by Optimizer.
SQL> SELECT * From EMPLOYEE_DATA Where HIREDATE IS NULL AND RETURN_EMPNO(EMP_NO) IS NOT NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 1371098230
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    94 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE_DATA |     1 |    94 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX_HD_NAME  |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("HIREDATE" IS NULL)
       filter("MYUSER"."RETURN_EMPNO"("EMP_NO") IS NOT NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)



The extra condition in the where clause is always true and therefore does not change the result. Nevertheless, the Oracle database recognizes that you only query rows that must be in the index per definition.




Dynamic sampling and its impact on Optimizer

What is Dynamic sampling and its impact on the Optimizer and how it helps generating better Execution Plan?

The Oracle optimizer makes decisions based on the statistics stored in the data dictionary for the objects referenced by a given query. Without valid statistics, the optimizer often makes poor choices when picking an execution plan. 
The main objective of dynamic sampling is to create more accurate selectivity and cardinality estimates, which, in turn, helps the CBO generate faster execution plans. 
Oracle optimizer use the default statistic values when table does not have any statistics and dynamic sampling query feature is disabled. Optimizer Dynamic Sampling has 11 levels between 0 to 10. The default setting is 2. This setting ensures that the optimizer will have a good estimate whenever it optimizes a query that accesses a table with no statistics. Database will perform dynamic sampling when existing statistics are missing, unusable or insufficient (unless dynamic sampling has been turned off entirely).


NOTE: Optimizer will not perform dynamic sampling on Remote tables or External Tables.

How Dynamic Sampling Works
There are two ways to use dynamic sampling:
  • The OPTIMIZER_DYNAMIC_SAMPLING parameter can be set at the database instance level and can also be overridden at the session level with the ALTER SESSION command.
  • The DYNAMIC_SAMPLING query hint can be added to specific queries. DYNAMIC_SAMPLING hint can also be used to invoke dynamic sampling for a single statement. 
Here are the criteria levels for OPTIMIZER_DYNAMIC_SAMPLING:
Level
When Dynamic Sampling will be used
Sample size (blocks)
0
Do not use dynamic sampling.
N/A
1
Use dynamic sampling with a 32-block sample size for all tables that have not been analysed, but only if the unanalysed table has more than 32 blocks, no indexes and is not partitioned.
32
2 (default)
Use dynamic sampling with a 64-block sample size if at least one table in the statement has no statistics. This is the default.
64
3
Use dynamic sampling with a 64-block sample size if the statement meets level 2 criteria or has one or more expressions used in the WHERE clause predicates. Example: Where SUBSTR(CUSTLASTNAME,1,3) or Where a + b =5
64
4
Use dynamic sampling with a 64-block sample size if the statement meets level 3 criteria or uses complex predicates (an OR or AND operator between multiple predicates on the same table).
64
5
Any statement that meets level 4 criteria
128
6
Any statement that meets level 4 criteria
256
7
Any statement that meets level 4 criteria
512
8
Any statement that meets level 4 criteria
1024
9
Any statement that meets level 4 criteria
4086
10
Use dynamic sampling of all blocks for all statements.
All Blocks


Consider below Table:
SQL> CREATE Table MyTemp AS
SELECT OWNER, OBJECT_TYPE
FROM ALL_OBJECTS;
Table created.

SQL> SELECT Count(*) From MyTemp;
  COUNT(*)
----------
     71879



Let’s disable the Dynamic Sampling by DYNAMIC_SAMPLING hint, with a level of zero and see the Execution Plan.

Disabling dynamic sampling to see default cardinalities
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT /*+ Dynamic_Sampling(MyTemp,0) */ *
  2  From MyTemp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2234051319
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 16908 |   462K|    58   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MYTEMP | 16908 |   462K|    58   (0)| 00:00:01 |


As you can see, the estimated cardinality is 16,906, which is very far off from the real cardinality, about 71,879. If I permit dynamic sampling, I get a much more realistic cardinality estimate.

More-realistic cardinalities with dynamic sampling enabled
SQL> SELECT * From MyTemp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2234051319

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 82504 |  2255K|    59   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MYTEMP | 82504 |  2255K|    59   (2)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
 - dynamic sampling used for this statement (level=2)


Now, 82504 is much closer to 71,879. An inaccurate cardinality estimate can swing either way. We have seen the optimizer radically underestimating the cardinality, but it can overestimate as well. 


Overestimating cardinalities
SQL> DELETE From MyTemp;
71879 rows deleted.
SQL> COMMIT;
Commit complete.

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT /*+ Dynamic_Sampling(MyTemp,0) */ *
  2  From MyTemp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2234051319
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 16908 |   462K|    58   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MYTEMP | 16908 |   462K|    58   (0)| 00:00:01 |
----------------------------------------------------------------------------
SQL> SELECT * From MyTemp;

Execution Plan
----------------------------------------------------------
Plan hash value: 2234051319
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    28 |    58   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MYTEMP |     1 |    28 |    58   (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)




Think about what might happen if the optimizer guessed 16,908 rows instead of 1 row in this case. For queries accessing table MyTemp, the optimizer would grossly overestimate the rows that will be returned from MyTemp and generate incorrect plans as a result.


Poor Cardinality Estimates 


Creating the “FLAG” table and gathering statistics

SQL> CREATE Table MyTable
  2  AS SELECT
  3  DECODE(MOD(ROWNUM,2),0,'N','Y') Flag1,
  4  DECODE(MOD(ROWNUM,2),0,'Y','N') Flag2, AO.*
  5  FROM ALL_OBJECTS AO;
Table created.

SQL> SELECT Count(*) From MyTable;
  COUNT(*)
----------
     71880

SQL> CREATE INDEX T_IND ON MyTable(Flag1,Flag2);
Index created.

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS(USER,'MYTABLE',
  3  METHOD_OPT=>'For All Indexed Columns Size 254');
  4  END;
  5  /
PL/SQL procedure successfully completed.

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * From MyTable Where Flag1='N' And Flag2='N';

Execution Plan
----------------------------------------------------------
Plan hash value: 1015944200
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         | 17969 |  1772K|   299   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| MYTABLE | 17969 |  1772K|   299   (1)| 00:00:04 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("FLAG1"='N' AND "FLAG2"='N')



Let’s use Dynamic Sampling
SQL> SELECT /*+ Dynamic_Sampling(MyTable 3) */  * From MyTable Where Flag1='N' And Flag2='N';
Execution Plan
----------------------------------------------------------
Plan hash value: 270124127
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     7 |   707 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYTABLE |     7 |   707 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND   |     7 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("FLAG1"='N' AND "FLAG2"='N')
Note
-----
- dynamic sampling used for this statement (level=2)




Note the much better row estimate (7 instead of more than 17,959) , also note the very different (and now optimal) plan. Instead of a full table scan, the optimizer has decided to use the index, and this execution plan will definitely be optimal compared to the original full table scan, because in reality, no data will be returned. The index will discover that instantly.
So, not all queries can benefit from Oracle dynamic sampling. Queries with very fast execution times or ad-hoc queries that will not be repeated are not good candidates. Here are the situations that can benefit most from dynamic sampling:
·       A query with complex predicates that is generating a poor execution plan.
·       Queries with execution times that are significantly longer than the time required to perform dynamic sampling.
·       Queries that will be repeatedly executed on the database.
 ·       If the available statistics are not enough to generate a good execution plan, dynamic sampling will be used.





Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.
Source- AskTom 

Understanding the EXECUTION PLAN and it's Components

Understanding the EXECUTION PLAN

To know how Execution Plan helps in Optimizing SQLs- Click here.

In order to determine if you are looking at a good execution plan or not, you need to understand how the Optimizer determined the plan in the first place. You should also be able to look at the execution plan and assess if the Optimizer has made any mistake in its estimations or calculations, leading to a suboptimal plan. The components to assess are:

• Cardinality– Estimate of the number of rows coming out of each of the operations.
• Access method – The way in which the data is being accessed, via either a table scan or index access.
• Join method – The method (e.g., hash, sort-merge, etc.) used to join tables with each other.
• Join type – The type of join (e.g., outer, anti, semi, etc.).
• 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?


Cardinality
 The cardinality is the estimated number of rows that will be returned by each operation. The Optimizer determines the cardinality for each operation based on a complex set of formulas that use both table and column level statistics as input (or the statistics derived by dynamic sampling).
The CARDINALITY estimate is found in the Rows column of the execution plan

Consider Employee Table below having 107 rows.
SQL> SELECT EMP_ID, ENAME, JOB_ID
FROM Employees
WHERE JOB_ID = ‘AD_VP’;














The JOB_ID column has 19 distinct values so the optimizer predicted the cardinality for this statement
to be 107/19 or 5.6 rows, which gets rounded up to 6 rows.


Determine the correct cardinality
To manually determine if the Optimizer has estimated the correct cardinality (or is in close proximity)
you can use a simple SELECT COUNT(*) query for each tables used in the query and applying any
WHERE clause predicates belonging to that table in the query. For the simple example used before
SQL> SELECT COUNT(*)
FROM Employees WHERE JOB_ID=’AD_VP’;
COUNT(*)
-------------
2


Alternatively, you can use the GATHER_PLAN_STATISTICS hint in the SQL statement to automatically collect more comprehensive runtime statistics. This hint records the actual cardinality.
Runtime cardinality statistics are displayed in the A-Rows column

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ EMP_ID, ENAME, JOB_ID

FROM Employees WHERE JOB_ID = ‘AD_VP’;














Access Method
The access method - or access path - shows how the data will be accessed from each table (or index).
The access method is shown in the operation field of the explain plan.











Oracle supports following common access methods:

Full table scan - Reads all rows from a table and filters out those that do not meet the where clause predicates. A full table scan is selected if a large portion of the rows in the table must be accessed, no indexes exist or the ones present can’t be used or if the cost is the lowest.

Table access by ROWID – The ROWID of a row specifies the data file, the data block within that file, and the location of the row within that block. Oracle first obtains the ROWIDs either from a WHERE clause predicate or through an index scan of one or more of the table's indexes. Oracle then locates each selected row in the table based on its ROWID and does a row-by-row access.

Index unique scan – Only one row will be returned from the scan of a unique index. It will be used when there is, an equality predicate on a unique (B-tree) index or an index created as a result of a primary key constraint.














Index range scan – Oracle accesses adjacent index entries and then uses the ROWID values in the index to retrieve the corresponding rows from the table. An index range scan can be bounded or unbounded. It will be used when a statement has an equality predicate on a non-unique index key, or a non-equality or range predicate on a unique index key. (=, <, >,LIKE if not on leading edge). Data is returned in the ascending order of index columns.















Index range scan descending – Conceptually the same access as an index range scan, but it is used when an ORDER BY .. DESCENDING clause can be satisfied by an index.

Index skip scan - Normally, in order for an index to be used, the prefix of the index key (leading edge of the index) would be referenced in the query. However, if all the other columns in the index are referenced in the statement except the first column, Oracle can do an index skip scan, to skip the first column of the index and use the rest of it. This can be advantageous if there are few distinct values in the leading column of a concatenated index and many distinct values in the non-leading key of the index.

Full Index scan - A full index scan does not read every block in the index structure, contrary to what its name suggests. An index full scan processes all of the leaf blocks of an index, but only enough of the branch blocks to find the first leaf block. It is used when all of the columns necessary to satisfy the statement is in the index and it is cheaper than scanning the table. It uses single block IOs. It may be used in any of the following situations:

• An ORDER BY clause has all of the index columns in it and the order is the same as in the index (can also contain a subset of the columns in the index).
• The query requires a sort merge join and all of the columns referenced in the query are in the index.
• Order of the columns referenced in the query matches the order of the leading index columns.
• A GROUP BY clause is present in the query, and the columns in the GROUP BY clause are present in the index.

Fast full index scan - This is an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. It cannot be used to eliminate a sort operation, because the data access does not follow the index key. It will also read all of the blocks in the index using multiblock reads, unlike a full index scan.

Index join – This is a join of several indexes on the same table that collectively contain all of the columns that are referenced in the query from that table. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the joined indexes. An index join cannot be used to eliminate a sort operation.

Bitmap Index – A bitmap index uses a set of bits for each key value and a mapping function that converts each bit position to a ROWID. Oracle can efficiently merge bitmap indexes that correspond to several predicates in a WHERE clause, using Boolean operations to resolve AND and OR conditions.

If the access method you see in an execution plan is not what you expect, check the cardinality estimates for that object are correct and the join order allows the access method you desire.


Join method
The join method describes how data from two data producing operators will be joined together. You can identify the join methods used in a SQL statement by looking in the operations column in the explain plan.

Join Method is shown in the Operations column











Oracles offers several join methods and join types.

Hash Joins - Hash joins are used for joining large data sets. The optimizer uses the smaller of the two tables or data sources to build a hash table, based on the join key, in memory. It then scans the larger table, and performs the same hashing algorithm on the join column(s). It then probes the previously built hash table for each value and if they match, it returns a row.

Nested Loops joins - Nested loops joins are useful when small subsets of data are being joined and if there is an efficient way of accessing the second table (for example an index look up). For every row in the first table (the outer table), Oracle accesses all the rows in the second table (the inner table).
Consider it like two embedded FOR loops. In Oracle Database 11g the internal implementation for nested loop joins changed to reduce overall latency for physical I/O so it is possible you will see two NESTED LOOPS join in the operations column of the plan, where you previously only saw one on earlier versions of Oracle.












Sort Merge joins – Sort merge joins are useful when the join condition between two tables is an inequality condition such as, <, <=, >, or >=. Sort merge joins can perform better than nested loop joins for large data sets. The join consists of two steps:
Sort join operation: Both the inputs are sorted on the join key.

Merge join operation: The sorted lists are merged together.












Cartesian join - The optimizer joins every row from one data source with every row from the other data source, creating a Cartesian product of the two sets. Typically, this is only chosen if the tables involved are small or if one or more of the tables does not have a join conditions to any other table in the statement. Cartesian joins are not common, so it can be a sign of problem with the cardinality estimates, if it is selected for any other reason. Strictly speaking, a Cartesian product is not a join.













Join Types
Oracle offers several join types: inner join, (left) outer join, full outer join, anti-join, semi join, grouped outer join, etc. Note that inner join is the most common type of join; hence the execution plan does not specify the key word “INNER’.

Outer Join - An outer join returns all rows that satisfy the join condition and also all of the rows from the table without the (+) for which no rows from the other table satisfy the join condition. For example, T1.x = T2.x (+), here T1 is the left table whose non-joining rows will be retained. In the ANSI outer join syntax, it is the leading table whose non-join rows will be retained. The same example can be written in ANSI SQL as T1 LEFT OUTER JOIN T2 ON (T1.x = T2.x);
Example plan output using OUTER JOIN. Note a join type is always matched with one of the join methods; in this case a hash join











Join Order
The join order is the order in which the tables are joined together in a multi-table SQL statement. To determine the join order in an execution plan look at the indentation of the tables in the operation column. In Figure 22 below the SALES and PRODUCTS table are equally indented and both of them are more indented than the CUSTOMERS table. Therefore, the SALES and PRODUCTS table will be joined first using a hash join and the result of that join will then be joined to the CUSTOMERS table.

Example plan output highlighting the JOIN ORDER














The join order is determined based on cost, which is strongly influenced by the cardinality estimates and the access paths available. 
The Optimizer will also always adhere to some basic rules:

• Joins that result in at most one row always go first. The Optimizer can determine this based on UNIQUE and PRIMARY KEY constraints on the tables.
• When outer joins are used, the row preserving table (table without the outer join operator) must come after the other table in the predicate (table with the outer join operator) to ensure all of the additional rows that don’t satisfy the join condition can be added to the result set correctly.
• When a subquery has been converted into an antijoin or semi join, the tables from the subquery must come after those tables in the outer query block to which they were connected or correlated. However, hash antijoins and semi joins are able to override this ordering condition under certain circumstances.
• If view merging is not possible all tables in the view will be joined before joining to the tables outside the view.

If the join order is not what you expect check the cardinality estimates for each of the objects and the
access methods are correct.

Partitioning
Partitioning allows a table, index or index-organized table to be subdivided into smaller pieces. Each piece of the database object is called a Partition. Partition pruning or Partition elimination is the simplest means to improve performance using Partitioning. For example, if an application has an ORDERS table that contains a record of all orders for the last 2 years, and this table has been partitioned by day, a query requesting orders for a single week would only access seven partitions of the ORDERS table instead of 730 partitions (the entire table).

Partition pruning is visible in an execution plan in the PSTART and PSTOP columns. The PSTART column contains the number of the first partition that will be accessed and PSTOP column contains the number of the last partition that will be accessed1. In Figure 24 four partitions from SALES are accessed, namely partitions 9,10,11, and 12.


Example plan output highlighting Partition pruning for a single-level partitioned table












A simple select statement that was run against a table that is partitioned by day and sub-partitioned by hash on the CUST_ID column is shown. In this case a lot more numbers appear in the PSTART, PSTOP columns. What do these additional numbers mean?


Example plan output highlighting Partition pruning for a composite partitioned table
















When using composite partitioning, Oracle numbers each of the partitions from 1 to n (absolute partition numbers). For a table that is partitioned on just one level, these absolute numbers represent the actual physical segments on disk of the single-level partitioned table.


In the case of a composite partitioned table, however, a partition is a logical entity and not represented on disk. Each partition is subdivided into so-called sub-partitions. Each sub-partition within a partition is numbered from 1 to m (relative sub-partition number within a single partition). Finally, all of the sub partitions in a composite-partitioned table are given a global number 1 to (n X m) (absolute sub partition numbers); these absolute numbers represent the actual physical segments on disk of the composite partitioned table.


Numbering scheme for a partitioned table





















So, in the previous plan in Figure the number 10 in PSTART and PSTOP column, on line 4 of the plan represents the global partitioning number representing the physical segments on disk. The number 5 in PSTART and PSTOP column, on line 2 of the plan represents the partition number; the number 2 in PSTART and PSTOP column, on line 3 of the plan, represents the relative sub-partition number within a partition.

There are cases when a word or letters appear in the PSTART and PSTOP columns instead of a number. For example, you may see the word KEY appears in these columns. This indicates that it was not possible at parse time to identify, which partitions would be accessed by the query but the Optimizer believes that partition pruning will occur at execution time (dynamic pruning). This happens when there is an equality predicate on the partitioning key column that contains a function. For example TIME_ID = SYSDATE. Another situation where dynamic pruning can occur is when there is a join condition on the partitioning key column in the query and the table that is joined with the partitioned table is expected not to join with all partitions, for example because of a FILTER predicate.

Partition pruning will occur at execution time. In the example in Figure27 below the where clause predicate is on the TIME table, which joins to the SALES table on the partition key time_id. Partition pruning will happen at execution time after the WHERE clause predicate has been applied to the TIME table and the appropriate TIME_IDs have been select.


Example plan output highlighting dynamic Partition pruning










If partition pruning does not occur as expected, check the predicates on the partition key column. Ensure that the predicates are using the same datatype as the partition key column. You can check this in the predicate information section under the plan. If the table is hash partitioned, partition pruning will only occur if the predicate on the partition key column is an equality or an in-list predicate. 

Also, if the table has multi-column hash partitioning then partition pruning will only occur if there is a predicate on all columns used in the hash partitioning.


Parallel Execution
Parallel execution in the Oracle Database is based on the principles of a coordinator (often called the Query Coordinator or QC for short) and parallel server processes. The QC is the session that initiates the parallel SQL statement and the parallel server processes are the individual sessions that perform work in parallel. The QC distributes the work to the parallel server processes and may have to perform a minimal, mostly logistical, portion of the work that cannot be executed in parallel. 
For example, a parallel query with a SUM() operation requires adding the individual sub-totals calculated by each parallel server processes.


Concept of parallel execution in the Oracle database






















The QC is easily identified in the parallel execution plan as it writes its name in the plan. You can see this on the line with ID#1 of the plan shown in Figure where you see the operation 'PX COORDINATOR'. All of the operations that appear above this line in the execution plan are done by the QC. Since this is a single process all of these operations are done serially. Typically, you want to minimize the number of operations done by the QC. All of the operations done under the line ‘PX COORDINATOR’ are typically done by the parallel server processes.


Example plan output highlighting the concepts of parallel execution