ORACLE Explain Plan

Explain Plan

Explain Plan : It is a statement that allows you to have oracle generate execution plan for any sql statement with out actually executing it.You will be able to examine the execution plan by querying the plan table.

Plan Table : A Plan table holds execution plans generated by the explain plan statements.Create Plan table by running utlxplan.sql located in

$oracle_home/rdbms/admin.

Explain Plan Syntax :

Explain plan [set statement_id=<string in single quotes>]

[into <plan table name>]

for

<sql statements>;

Example to Explain Plan:

Sql> Explain plan set statement_id=’demo’ for

select a.customer_name,a.customer_number,b.invoice_number,

b.invoice_type,b.invoice_data,b.total_amount,

c.line_number,c.part_number,c.quantity,c.unit_cost

from customers a, invoices b, invoice_items c

where c.invoice_id=:b1

and c.line_number=:b2

and b.invoice_id=c.invoice_id

and a.customer_id=b.customer_id

Sql> @ explain.sql

Enter statement_id : Demo