Execution Plan

See above: explain plan is the SQL Statement.The plan created can be fetch using the following select statement:

V$SQL PLAN:

Not using this.

What to look for in Execution Plan? What is a good execution plan?

Following components needs to be considers:

1. Cardinality - Estimate of number of rows getting accessed.

2. Assess Method -

a. Table Access Full

b. Table access by Index

c. Index Unique Scan

d. Index Range Scan

e. Index Full Scan

f. Index [Hash] Join

3. Join Method

a. Hash Join

b. Nested Loop

c. Sort-Merge

d. Cartesian Join

4. Join Type

a. Outer

b. Semi

c. Anti

Define terms:

Table Access Full/Full Table Scan: Read all rows from a table. It uses multi block IO.

Index Unique Scan: Only one row will be returned from the scan of a unique index. (where ID = 1)

Index Range Scan: Used when non-unique index as equality predicate or non-equality predicate (<,>=, LIKE) on unique index.

Fill Index Scan: Scan full index for the condition != for example.

Index Hash Join: Used to join several indexes on the same table that collectively contains all the columns that are referenced in the query.

Hash Join: Defined in separate Page.

Nested Loop Join: Is generally used one of the tables is small and there is an efficient way of accessing it. Think of it as two FOR loops.

Block-Nest Loop Join: is enhancement over Nested Loop. The loop on second table is made not for each record of first, but for each block of first table, hence improving the read blocks.

Sort-Merge Join: is used for join condition is inequality condition (<, >=). It performs better than nested loop for ???

References:

1. http://www.dwbiconcepts.com/database/22-database-oracle/26-oracle-query-plan-a-10-minutes-guide.html

2. http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf