Generating an Explain Plan and Displaying it in a Client Application
An alternate to show a query execution plan is to precede the query with the keyword EXPLAIN. This feature can be used in Studio, although it is intended for use in JDBC/ODBC client applications. Being able to analyze the execution plans of a query in tools other than TDV can help you optimize your queries faster. The text results are retrieved in a result set that can be consumed by Studio or by your client application. The result set is one column of VARCHAR text.
The default column width of the execution plan is 100 characters. You can change it from Studio using the TDV Explain text width configuration parameter.
Option | Description | Example Syntax |
show_source_plan="true" | Retrieves the query plan. This can also be used in the SQL Scratchpad. | explain select {option show_source_plan="true"} * from <view> |
show_runtime="true" | Retrieves the execution statistics (plan and runtime statistics). This can also be used in the SQL Scratchpad. | explain select {option show_runtime="true"} * from <view> |
To explain a query execution plan within your client application
1. Use your client application to connect through JDBC or ODBC to TDV.
2. Navigate to the TDV table or view with the query you want to analyze.
3. In the SQL editor for that query, type EXPLAIN before the query. For example:
explain select * from emp;
4. Run the query.
5. Review the explain plan to determine how to optimize the query.
To explain a query execution plan within Studio
1. Open a view.
2. In the SQL Scratchpad, add the keyword EXPLAIN before the query.
3. Click the Execute button.
The Result panel displays the view’s query execution plan (rather than the view’s execution results). The actual view is not executed.
4. Use the Load More Results button to view the next 50 rows of the plan.