CUBRID Manager Query Editor

7.1. Query Editor Structure

The CUBRID Manager's Query Editor is a query tool that supports execution of all DML, DDL, DCL statements, allowing users to edit and execute queries more easily. To run the Query Editor, select Tools > New Query or select New Query from the toolbar. You can also right-click a specific database and then select New Query.

If you select New Query by right clicking on a database, the default query editor is run with the basic information provided upon login to the corresponding database.

However, if you select it on the menu or toolbar, you can specify your login information by yourself to connect the database. The character set displayed on the screen is the same one that is specified for the database connection. You can log in setting the information of the database login, character set, and JDBC driver version. Note that separate configuration for broker connection is enabled in case a Broker is running in another server.

The Query Editor window is divided into a query edit pane at the top and a query results pane at the bottom. In the query edit pane, you can type and edit queries to execute with a toolbar that contains icons for frequently used functions in the Query Editor. In the query results pane, you can see the query results in a tab format and check the query execution time.

When you use multiple editors at the same time, it could more convenient to check the connection information in the title bar (at top) and in the status bar (at bottom) of the CUBRID Manager. Information that will be displayed is as follows: database, user name, broker port, and charset.

7.2. Query Results Pane

The query results pane displays the results of the query executed. If there are multiple queries executed, the results of each query are displayed in a separate tab. You can check the query results by selecting the corresponding tab.

The query results pane is divided into areas where you can navigate the results, view information of the executed query, and check execution time and the number of results returned by the query.

• Results search : You can navigate the entire search results while moving by the value set in Page unit of result instances of Query options.
• Query execution information : Shows from which query the current result comes.
• Execution time and Number of hits : Provides information about the execution time on the server to get the current query results and total search count.

7.3. Query Results Pane functions

• Copy : Copies an entire row. To copy a specific column of the row only, select View detail.
• Modify : The information can be modified directly from the query results pane. Change data to a modifiable state by double-clicking it, and then modify it. This option is available when Get OID info is set in the given host's Properties > Query dialog box, or OID info is specified.
• Delete : You can delete data directly in the query results pane. Right-click the row to delete and then select Delete. This option is available only when Get OID info is set as the modify function does.
• OID navigator : This option can be activated by right-clicking an OID data. It provides a function that allows you to navigate the selected OID directly.
• View detail : If there are too long data to be displayed in a single row, or if its size is too big, it is very difficult to see column values on the query results pane. In this case, you can see column values in detail by right-clicking the row and then selecting View detail. You can also modify data directly in the Detail dialog box if Get OID info option is set. For BLOB or CLOB type data, you can import and export it with a separate file.
• Export all : Exports all data in the query results pane to an Excel or a CSV file.
• Export selected : Exports only the data in the row selected in the query results pane to an Excel or a CSV file. The charset for a file where exported data is saved can be specified.
• Export BLOC/CLOB data : Exporting/importing data for BLOB, CLOB, BIT VARYING (>100) cannot be executed, but importing detailed information in the result pane of the Query Editor is possible. Note that only type tags such as (BLOB), (CLOB), and (BIT) are displayed in the result pane, instead of displying real data.
• Import BLOB/CLOB data : Entering or editing data in the detailed information of the result pane is supported by opening the file where type data of STRING, CHAR, VARCHAR, NCHAR, NCHAR VARYING, BIT VARYING, BIT, BLOB, and CLOB is saved.

7.4. Query Editor other functions

Executing Multiple Queries

Enter a semicolon at the end of the query statement to specify the end of one query and the start of the next. If there are multiple queries, they are executed sequentially. Each query creates a corresponding tab in the query results pane. If you execute multiple queries without separating them with semi-colons, only the first query is executed, with the rest ignored.

Cancel Query Function

A function that stops the currently running query. This can be divided into two functions.

• When executing multiple queries : If you click the Stop button when multiple queries are being executed with the auto commit button enabled, queries processed before the stop operation are reflected normally, and the currently canceled query and following ones are not reflected. If the auto commit button is not enabled, no executed queries are reflected.
• When executing a long transaction : If you click the Stop button when executing a long transaction, a query stop command is delivered to CUBRID Manager > JDBC > Broker > Server, and the query finally stops in the database server the query is actually canceled. For Windows, this function is supported in CUBRID 2008 R2.2 or later.

7.5. Query Execution Plan

If Display query plan is selected in the Query Editor's options, Display query plan 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 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 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, 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.

Query Explain Toolbar

The Query Explain toolbar has the following functions:

• New : 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 : 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, Save a query explain file as : Saves the collected query plan to an external file. The file extension is xml.
• Disable to collecting histories : 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. The name of the tab below the query plan display pane is displayed as "plan."
• Query plan type : By toggling the icon, the type can be switched between text and tree. You can view the unprocessed query plan source created by the database in a text form.
• Show or Hide a query history pane : 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.

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).

• 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.

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.

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.

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.

Query result filter

After run the query on the query editor, you can find some data using some filter on the query result pane such as the following image.

It will make a highlight on the query result If you input a keyword which you want to find something. Also, you can use several filter options by clicking the filter menu if you want to find in detail as followings:

• All: Filter data in all columns.
• Assign the column: Filter data in assigned column.
• More: Set the match columns.  
• Case sensitive: Case sensitive for matching data.
• Case insensitive: Case insensitive for matching data.
• Use wild cards: Use the char ‘*’ for matching data.
• Use regular expression: Use regular expression for matching data.
• Match from start: Match the data from start.
• Match exactly:  Match the data exactly.
• Match anywhere:  Match the data anywhere.