:: Query Editor
7. 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
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
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
- 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
Query Explain Toolbar
The Query Explain toolbar has the following functions:
: Initializes all query plan histories retrieved so far and begins
collecting them again. This function is used to initialize the existing
- 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
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.