Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 | 

Query Execution Plan

If [Display query plan] is selected in the Query Editor's options, [Display query plan icon_queryplan.png] becomes activated in the Query Editor toolbar.

You can check how the selected query will be executed, even without executing it, by clicking [Display query plan icon_queryplan.png] from the toolbar or by clicking < Ctrl+L> . You can also check query execution plan that have already been executed.

Since query plans are displayed all the time at the bottom of the Query Editor window, you can check the existing query plans by opening query plan history files without connecting to the database.

The query plan function retrieves the SQL execution plans it is used not for a one-time purpose but for a collection purpose to continuously manage and retrieve them. Every time you retrieve a query plan, the query plan history is accumulated. You can save this accumulated data to an .xml file. When you open the saved .xml file, you can check the original query plan and executed SQL statements. If it is a one-time retrieval, select [Disable to collecting histories icon_nohistory.png] to view the current query plan without recording history.

The [Query Explain] tab consists of a toolbar, query plan display pane, original statement display pane and query plan history pane.

Query Explain tab

If you select a query and then click [Display query plan icon_queryplan.png], the query plan is displayed in the [Query Explain] tab. The [Query Explain] tab shows the query plan summarized in the tree structure.

The [Query Explain] tab is located to the right of the [Result] tab. You can switch to the [Query Explain] tab while viewing the query result.

manager_queryeditor_queryplan_plan.png

Query Explain Toolbar

The Query Explain toolbar has the following functions:

  • New icon_new.png : Initializes all query plan histories retrieved so far and begins collecting them again. This function is used to initialize the existing job.
  • Open a query explain file icon_open.png : Imports a previously saved query plan history file. If you open the .xml file, you can view the original query plan.
  • Save a query explain file icon_save.png, Save a query explain file as icon_saveas.png : Saves the collected query plan to an external file. The file extension is xml.
  • Disable to collecting histories icon_nohistory.png : Histories are added to the query plan history pane whenever you retrieve the execution plan by using [Display query plan] in the Query Editor. To retrieve a query plan temporarily without recording history, click [Disable to collecting histories icon_nohistory.png]. The name of the tab below the query plan display pane is displayed as "plan."
  • Query plan type icon_tree.png icon_text.png : By toggling the icon, the type can be switched between text icon_text.png and tree icon_tree.png. You can view the unprocessed query plan source created by the database in a text form.
  • manager_queryeditor_queryplan_text.png
  • Show or Hide a query history pane icon_show.png icon_hide.png : Shows or hides the collection history pane on the right side.
  • Query plan history file : If a query plan history is saved as a file, or an existing file is opened, the name of the currently used query plan history file is displayed.
  • manager_queryeditor_queryplan_file.png
Query Plan Display Pane

In the query plan display pane, the query plan executed in each step is displayed in the tree structure.

Each item in the vertical axis is called a node. Each node contains different data. You can view the tree moving from the top to the bottom.

The horizontal axis is called an item and contains Type, Table, Index, Terms, CPU I/O cost, Disk I/O cost, and Total (ROW/PAGE).

manager_queryeditor_queryplan_output.png

  • Type : Indicates the scan type or join method (such as sscan, iscan and idx-join).
  • Table : The table (class), view (virtual class) and alias, which are referred to when the node is executed, are displayed altogether.
  • Index : The name of the index used is displayed if the type is iscan.
  • Terms : Join and filter conditions are displayed. The contents are hidden for readability. If you click +, sub-nodes are extended to show details. In addition, different colors are used for different search conditions.
  • Cost : Displays CPU and Disk I/O cost of the query plan. Fixed and variable costs are displayed separately.
  • Total (r/p) : Displays the total number of rows and the number of pages to be used to fetch data.

The original statements of the query plan selected in the query plan pane are displayed below the query plan display pane.

manager_queryeditor_queryplan_output2.png

Query Plan History Pane

Histories are displayed accumulatively in the query plan history pane every time a query plan is executed. # is the accumulation order and corresponds to the tab number below the query plan display pane. Date indicates the date when a query plan is executed, and Cost is the sum of CPU and Disk I/O costs. If you double-click an item in the query plan history pane, you can view the query plan again in the query plan display pane.

manager_queryeditor_queryplan_history.png

Using Query Plans

By using the query plan function, you can analyze data while viewing the query plan and the schema info of the corresponding table.

If you right-click a row where a table is located in the query plan display pane and then select [Show Schema Info], you can open and view the schema information of the table as well.

manager_queryeditor_queryplan_context.png

You can also view the information in a separate window by dragging the Schema Info pane out of the CUBRID Manager. This can be useful in an environment using multiple monitors.

manager_queryeditor_queryplan_schema.png