This article provides details on how to obtain the TIBCO Data Virtualization query execution plan(s) in client application. 

All supported Operating Systems

This can be achieved by adding the keyword EXPLAIN before the SELECT query. 

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 VIEWORDER;
4.Run the query.

Screenshot for reference: 


You can review the explain plan to determine how to optimize the query.

Alternatively, to explain a query execution plan within Studio:
1.In the Studio 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.

Screenshot for reference:



Additional Information: 
- 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. Screenshot of the configuration for reference:
 

- Useful Query hints that can be included in the EXPLAIN query: 
 

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>

Screenshot for reference:


 

How to obtain the Query Execution Plan in Client application for TIBCO Data Virtualization?