This topic describes some tips on the SQL console in OceanBase Developer Center (ODC) for you to carry out development work more efficiently on ODC.
Background information
ODC provides a web SQL-based SQL console as a work area where you can edit and diagnose SQL statements. In the SQL window, you can run and save perform operations such as running and saving SQL and PL scripts. The SQL window also provides diagnostic information such as execution details and execution results in the corresponding tabs. The editing area of the SQL window provides rich features, for example, highlighting of keywords in different colors, auto-complete, and formatting, to improve the SQL statement editing efficiency. Under the editing area, you can find the Execution Record tab and the Results tab. The Execution Record tab displays the execution details of each statement in the current connection. The Results tab displays the result of the current execution.
This topic describes some additional tips that can facilitate your development and management work in the SQL console.
Switch the transaction commit mode
In ODC V2.3.2 and later versions, auto-commit is turned off by default in Oracle mode, so you need to manually commit transactions. In MySQL mode, auto-commit is turned on by default. In manual-commit mode, make sure to set ob_trx_idle_timeout to a value greater than the timeout duration of SQL queries. Otherwise, if two SQL statements in one transaction are executed at an interval longer than the value of ob_trx_idle_timeout, the connection will be terminated.
You can switch the transaction commit mode in ODC.
Click the name of the target connection to go to the connection management page, click the Session button in the navigation bar, and select Session Properties from the drop-down list.
On the Session Properties page, select the
autocommitvariable in the variable list, and click the Edit button in the toolbar to go to the variable editing page.On the editing page, set Value to ON to turn on auto-commit or OFF to turn off auto-commit.
View the execution plan
After you finish editing the required statements in the SQL window and before you execute the script, you can select a statement and click the Plan button in the toolbar. A panel appears, displaying an execution plan for the selected statement or the statement on which the pointer is located. This execution plan is estimated by the system before execution and is the result of the
EXPLAIN PLANoperation. Therefore, the execution data displayed may differ from the actual execution data. You can use this feature to evaluate SQL statements.After a statement that you wrote in the editing area is executed, you can click the Plan button in the toolbar of the result set to view the actual resource consumption and execution plan of the statement. This allows you to evaluate the performance of the statement.
Save an SQL script
Edit your SQL script in the editing area of the SQL window.
Click the Save SQL button in the toolbar of the SQL window, specify a name for the script in the dialog box that appears, and then click OK to save the script.
Click the Workspace button in the navigation bar in the upper part of the page, and place the pointer on Stored Scripts on the drop-down list that appears, to view all the scripts that you saved on the cascading menu.
On the cascading menu, the Edit and Delete buttons are provided after each script name. Click Edit . A dialog box appears and displays the script name and content. You can modify the script and click OK to save the modification.
After you click a script name on the cascading menu, an SQL window appears and displays the script content. You can continue to edit the script and run the script in the SQL window.
Set the delimiter
ODC supports PL and allows you to edit PL statements in an SQL window starting from V2.2.0. You must set a delimiter before you edit PL statements.
Click the Settings button in the toolbar of the SQL window.
Select the sign that you want to use as the delimiter in the Delimiter box in the drop-down list. Five types of delimiters are supported: semi-colons ( ; ), slashes ( / ), double slashes ( // ), dollar signs ( $ ), and double dollar signs ( $$ ).
You can start to write PL statements after you set the delimiter. After you write a PL statement, end it with the selected delimiter.
In ODC V2.4.1 and later, you can use the DELIMITER statement to define the delimiter. In Settings , the Delimiter field displays the delimiter that you specified.
Limit the number of lines in the query result
Click the Settings button in the toolbar of the SQL window.
Specify Query Result Limit to set the maximum number of lines returned in the query result set. The default value is 1000 rows.