Skip to main content

SQL Plan Management

This topic explains what SQL Plan Management is and describes how to use this feature.

Overview​

SQL Plan Management (SPM) is a feature provided by Relyt, designed to ensure the stability of SQL query plans. It allows you to bind one SQL command (referred to as source SQL command) to another SQL command (referred to as destination SQL command). The association between the source and destination SQL commands is defined as SQL binding. After SPM is enabled, each time the source SQL command is issued to run, Relyt runs the destination SQL command.

info

Currently, only SELECT can serve as both the source SQL command and the destination SQL command.

Create an SQL binding​

To use the SPM feature, you must have at least one SQL binding.

Syntax​

CREATE BINDING FOR '<source_sql>' USING STMT '<destination_sql>';

In this syntax,

  • <source_sql>: a string that specifies the source SQL command.

  • <destination_sql>: a string that specifies the destination SQL command.

note

Ensure that the values of both <source_sql> and <destination_sql> adhere to the SELECT syntax.

Examples​

Create an SQL binding to direct Relyt to run SELECT orders_delivered FROM sales when someone tries to run SELECT orders_delivered FROM sales:

CREATE BINDING FOR 'SELECT orders_closed FROM sales' USING STMT 'SELECT orders_delivered FROM sales';

Check SQL bindings​

Relyt stores information about SQL bindings in table pg_catalog.relyt_spm_info. To check the SQL bindings, you can run SELECT * FROM pg_catalog.relyt_spm_info.

info

To access data in table pg_catalog.relyt_spm_info, you must connect to Relyt using utility mode.

The following figure shows the table structure.

In this table,

  • original_query_id: an integer that indicates the ID of the query to which the SQL binding applies.

  • original_sql: a string that indicates the source SQL command.

  • bind_sql: a string that indicates the destination SQL command.

  • constant_size: an integer that indicates the number of constants in the source SQL command.

  • constant_list: the list of constants in the source SQL command.

  • enabled: a boolean value that indicates the status of the SQL binding.

Other parameters are reserved for future extensions and can be ignored.

Modify an SQL binding​

After an SQL binding is created, you can update its settings in table pg_catalog.relyt_spm_info.

Syntax​

UPDATE <table_name> SET <config_param_to_update> = <new_value>[, ... ] WHERE <config_param> = <value>[, ... ];

In this syntax,

  • <table_name>: the table that stores the SQL binding settings. It is fixed to pg_catalog.relyt_spm_info.

  • <config_param_to_update>: the configuration parameter of which the value you want to change.

  • <new_value>: the new value for the configuration parameter.

  • <config_param>: the configuration parameter that you specify to identify the SQL binding.

    At least one of original_query_id and original_sql must be specified. It is preferrable to use original_query_id. This is because the value of original_sql is case-sensitive and must adhere to strict format requirements.

    SPM allows one SQL command to be bound to multiple SQL commands and also allows repeated SQL bindings. Because of this, if you specify only one original_query_id or original_sql in your DELETE command, all SQL bindings with the same ``original_query_idororiginal_sql` will be deleted. We recommend that you specify as many configuration parameters as you can to help uniquely identify the binding that you want to delete. To check the configuration settings of an SQL binding, see Check bindings.

  • <value>: the current value of the configuration parameter.

caution

We recommend that you use this syntax to change only the enabled parameter. Changing the values of other parameters may result in unexpected errors.

Examples​

Disable the SQL binding of which the source SQL command is SELECT orders_closed FROM sales:

UPDATE pg_catalog.relyt_spm_info SET enabled = false WHERE original_sql = 'SELECT orders_closed FROM sales';

Use an SQL binding​

To use an SQL binding, you must ensure that SPM is enabled for the binding or for all bindings.

Enable SPM for a specific SQL binding​

To enable a specific SQL binding, change its value of enabled to true in pg_catalog.relyt_spm_info.

For example, enable the SQL binding of which the source SQL command is SELECT orders_closed FROM sales:

UPDATE pg_catalog.zdb_spm_info SET enabled = true WHERE original_sql = 'SELECT orders_closed FROM sales';

For more information about the syntax, see Modify an SQL binding.

Enable SPM for all SQL bindings​

Relyt uses the relyt.spm_enable variable to control the SQL feature. To enable SPM for all SQL bindings, set relyt.spm_enable to true, for example:

SET relyt.spm_enable = on;
info

The value on can be any value that represents true in Relyt. For more details, see Boolean Type.

Delete an SQL binding​

If an SQL binding is no longer needed, you can use DELETE to remove it.

Syntax​

DELETE FROM <table_name> WHERE <config_param> = <value> [, ...];

In this syntax:

  • <table_name>: the table that stores the SQL binding settings. It is fixed to pg_catalog.relyt_spm_info.

  • <config_param>: the configuration parameter that you specify to identify the SQL binding.

  • <value>: the value of the configuration parameter.

Usage notes​

To delete an SQL binding, at least one of original_query_id and original_sql must be specified. It is preferrable to use original_query_id. This is because the value of original_sql is case-sensitive and must adhere to strict format requirements.

SPM allows one SQL command to be bound to multiple SQL commands and also allows repeated SQL bindings. Because of this, if you specify only one original_query_id or original_sql in your DELETE command, all SQL bindings with the same ``original_query_idororiginal_sql` will be deleted. We recommend that you specify as many configuration parameters as you can to help uniquely identify the binding that you want to delete. To check the configuration settings of an SQL binding, see Check bindings.

Examples​

Delete the SQL binding of which the source SQL command is SELECT orders_closed FROM sales and the destination SQL command is SELECT orders_delivered FROM sales:

DELETE FROM pg_catalog.relyt_spm_info 
WHERE original_sql = 'SELECT orders_closed FROM sales', bind_sql = 'SELECT orders_delivered FROM sales';