Infolinks

Sunday 15 July 2012

Explain Plan,Plan Table

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

No comments:

Post a Comment