SQL Rewriter: SQL Statement Rewriting
Overview
SQL Rewriter is an SQL rewriting tool. It converts query statements into more efficient or standard forms based on preset rules to improve query efficiency.
NOTE
- This function does not apply to statements that contain subqueries.
- This function supports only the SELECT and DELETE statements for deleting the entire table.
- This function contains 11 rewriting rules. Statements that do not comply with the rewriting rules are not processed.
- This function displays original query statements and rewritten statements on the screen. You are not advised to rewrite SQL statements that contain sensitive information.
- The rule for converting UNION to UNION ALL avoids deduplication and improves the query performance. The obtained result may be redundant.
- If a statement contains ORDER BY + specified column name or GROUP BY + specified column name, the SelfJoin rule is not applicable.
- The tool does not ensure equivalent conversion of query statements. The purpose is to improve the efficiency of query statements.
Usage Guide
Prerequisites
The database and connection are normal.
Example
Use the tpcc database as an example:
gs_dbmind component sql_rewriter 5030 tpcc queries.sql --db-host 127.0.0.1 --db-user myname --schema publicqueries.sql is the SQL statement to be modified. The content is as follows:
select cfg_name from bmsql_config group by cfg_name having cfg_name='1';
delete from bmsql_config;
delete from bmsql_config where cfg_name='1';The result is multiple rewritten query statements, which are displayed on the screen (the statements that cannot be rewritten are displayed as null), as shown in the following.
+--------------------------------------------------------------------------+------------------------------+
| Raw SQL | Rewritten SQL |
+--------------------------------------------------------------------------+------------------------------+
| select cfg_name from bmsql_config group by cfg_name having cfg_name='1'; | SELECT cfg_name |
| | FROM bmsql_config |
| | WHERE cfg_name = '1'; |
| delete from bmsql_config; | TRUNCATE TABLE bmsql_config; |
| delete from bmsql_config where cfg_name='1'; | |
+--------------------------------------------------------------------------+------------------------------+Obtaining Help Information
Before using the SQL Rewriter tool, run the following command to obtain help information:
gs_dbmind component sql_rewriter --helpThe following information is displayed:
usage: [-h] [--db-host DB_HOST] [--db-user DB_USER] [--schema SCHEMA]
db_port database file
SQL Rewriter
positional arguments:
db_port Port for database
database Name for database
file File containing SQL statements which need to rewrite
optional arguments:
-h, --help show this help message and exit
--db-host DB_HOST Host for database
--db-user DB_USER Username for database log-in
--schema SCHEMA Schema name for the current business dataPasswords are entered through pipes or in interactive mode. For password-free users, any input can pass the verification.
Command Reference
Table 1 Command line parameters
Troubleshooting
- If the SQL statement cannot be rewritten, check whether the SQL statement complies with the rewriting rule or whether the SQL syntax is correct.