What does the term explain plan in SQL query means. Has it got something to do with SQL optimization? If so can someone tell me the usage of this so that I can use it for optimizing my queries?
Printable View
What does the term explain plan in SQL query means. Has it got something to do with SQL optimization? If so can someone tell me the usage of this so that I can use it for optimizing my queries?
explain plan in sql is used to explore an execution plan. If explain plan is used then Oracle will analyze the statement and fill a special table with the Execution plan for that statement which has details like whether index is used or not, if there are more than one index then which index is used for achieving good performance and so on.
To add further to the explanation of timmy the syntax for explain plan usage in both the situations is given below:
explain plan for your-precious-sql-statement;
The above is a general syntax of explain plan statement.
If one wants to write to a table the syntax used for explain plan statement is explain plan into table name for your-precious-sql-statement;
The discussion is a very useful one. It gave a detailed insight about the term plan in SQL.
Explain plan is to find the excution cost of a particular query.
Independent of the sequencing of statments it gives the cost of a query in terms of time taken to execute the same.
the explain plan changes if some conditions are changed in a Query and it gives the least cost of the query executed.
it shows the execution plan or the path followed by the optimizer to retrive the record. Here we can check wheather an index or a hint is being used or not by the optimizer.