Obtaining a SQL Execution Plan

Function

This API is used to obtain a SQL execution plan.

Authorization Information

Each account has permissions to call all APIs, but IAM users must have the required permissions specifically assigned.

If you are using role/policy-based authorization, see the required permissions in Permissions Policies and Supported Actions.
If you are using identity policy-based authorization, the following identity policy-based permissions are required.

Action

Access Level

Resource Type (*: required)

Condition Key

Alias

Dependencies

das:clouddba:getSqlExecutionPlan

Read

Instance

-

-

-

Action	Access Level	Resource Type (*: required)	Condition Key	Alias	Dependencies
das:clouddba:getSqlExecutionPlan	Read	Instance	-	-	-

URI

POST /v3/{project_id}/connections/{connection_id}/get-plan

**Table 1** URI parameters
Parameter	Mandatory	Type	Description
project_id	Yes	String	Definition Project ID of a tenant in a region. To obtain this value, see Obtaining a Project ID. Constraints N/A Range The value can contain 32 characters. Only letters and digits are supported. Default Value N/A
connection_id	Yes	String	Definition Connection ID established between a database account and the database. The database user ID is created using the API in Creating an Instance Connection. Constraints N/A Range The value is a UUID containing 36 characters. Only letters, digits, and hyphens (-) are allowed. Default Value N/A

Request Parameters

**Table 2** Request body parameters
Parameter	Mandatory	Type	Description
database_name	Yes	String	Definition Database name Constraints N/A Range N/A Default Value N/A
schema_name	No	String	Definition Schema name Constraints N/A Range N/A Default Value N/A
sql_script	Yes	String	Definition SQL script Constraints N/A Range N/A Default Value N/A
node_id	No	String	Definition Unique ID of an instance node. You can obtain the node ID from the node list in the instance details. Constraints N/A Range The value can contain 36 characters. Only letters and digits are allowed. Default Value N/A
node_type	No	String	Definition Node type Constraints N/A Range master: primary node slave: secondary node readreplica: read replica Default Value N/A
use_default_search_path	No	Boolean	Definition Whether PostgreSQL uses the default search path Constraints This parameter is available only for PostgreSQL instances. Range N/A Default Value N/A
ignore_limit	No	Boolean	Definition Database name Constraints N/A Range N/A Default Value N/A
perpage	Yes	Integer	Definition Number of records on each page Constraints N/A Range [0, 100]. The actual value depends on the query result. Default Value N/A
curpage	Yes	Integer	Definition Page number Constraints N/A Range [0, 2^31 – 1]. The actual value depends on the query result. Default Value N/A

Response Parameters

Status code: 200

**Table 3** Response body parameters
Parameter	Type	Description
column_values	Array of Maps	Definition Field value
metadata	Array of Table 4 Objects	Definition Field attributes, such as a field name
result_type	String	Definition Result set type Range N/A
rows	Integer	Definition Number of rows Range N/A
time_delay	Long	Definition Execution duration Range N/A
can_edit	Boolean	Definition Whether the result set can be edited Range N/A
can_export	Boolean	Definition Whether the result set can be exported Range N/A
edit_db_name	String	Definition Edited database name Range N/A
edit_table	String	Definition Edited table name Range N/A
edit_primary_keys	Array of strings	Definition Primary key information
cannot_edit_reason	Integer	Definition Reason why the information cannot be edited Range 1001: The virtual table cannot be edited. 1002: Multiple tables cannot be edited. 1003: Editing is not allowed if there is no primary key. 1004: Metadata cannot be edited. 1005: Editing is not allowed if the result set does not contain all primary keys. 1006: Editing is not allowed if there are multiple result sets. 1007: The system catalog cannot be edited. 1009: The view cannot be edited. 1010: Editing is not supported.
extend_datas	Array of Table 3 Objects	Definition Extra information
data_sum	Integer	Definition Total data volume Range N/A
big_table	Boolean	Definition Whether there is a big table Range N/A
warning	Array of strings	Definition Alarm generated after a command is executed
sql	String	Definition SQL statement to be executed Range N/A
explain_sql	Boolean	Definition Whether the statement is an execution plan statement Range N/A
page_state	String	Definition Page status Range N/A
exceed_data	Boolean	Definition Whether the query result exceeds the specified size Range N/A
execute_status	String	Definition Execution status Range finished: The execution is complete. pending: The statement is being executed.

**Table 4** Column
Parameter	Type	Description
auto_increment	Boolean	Definition Whether auto-increment is enabled Range N/A
data_type	String	Definition Data type Range N/A
db_name	String	Definition Database name Range N/A
label	String	Definition Label Range N/A
name	String	Definition Name Range N/A
real_name	String	Definition Real name Range N/A
size	Integer	Definition Size Range N/A
table_name	String	Definition Table name Range N/A
schema_name	String	Definition Schema name Range N/A
readonly	Boolean	Definition Read-only or not Range N/A
is_binary	Boolean	Definition Binary or not Range N/A
int_data_type	Integer	Definition Data type Range N/A

Example Requests

POST https://das.cn-north-1.myhuaweicloud.com/v3/054e292c9880d4992f02c0196d3ea468/connections/179e525e-0e9c-4dcc-9dd7-ee2f36121e23/get-plan 
{
  "node_id": "7c1efa30d2a340b68bc0a80652e14179no01",
  "node_type": "master",
  "database_name": "czh_test",
  "sql_script": "SELECT *\nFROM lbw",
  "curpage": 1,
  "perpage": 50,
  "ignore_limit": false
}

Example Responses

Status code: 200

Success.

{
    "column_values": [
        {
            "COLUMN_5": "ALL",
            "COLUMN_2": "SIMPLE",
            "COLUMN_11": "100.00",
            "COLUMN_1": "1",
            "COLUMN_10": "1002",
            "COLUMN_3": "lbw"
        }
    ],
    "metadata": [
        {
            "auto_increment": false,
            "data_type": "BIGINT UNSIGNED",
            "db_name": "",
            "label": "id",
            "name": "COLUMN_1",
            "real_name": "id",
            "size": 3,
            "table_name": "",
            "schema_name": "",
            "readonly": false
        },
        {
            "auto_increment": false,
            "data_type": "VARCHAR",
            "db_name": "",
            "label": "select_type",
            "name": "COLUMN_2",
            "real_name": "select_type",
            "size": 19,
            "table_name": "",
            "schema_name": "",
            "readonly": false
        },
        {
            "auto_increment": false,
            "data_type": "VARCHAR",
            "db_name": "",
            "label": "table",
            "name": "COLUMN_3",
            "real_name": "table",
            "size": 64,
            "table_name": "",
            "schema_name": "",
            "readonly": false
        },
        {
            "auto_increment": false,
            "data_type": "MEDIUMTEXT",
            "db_name": "",
            "label": "partitions",
            "name": "COLUMN_4",
            "real_name": "partitions",
            "size": 6316032,
            "table_name": "",
            "schema_name": "",
            "readonly": false
        },
        {
            "auto_increment": false,
            "data_type": "VARCHAR",
            "db_name": "",
            "label": "type",
            "name": "COLUMN_5",
            "real_name": "type",
            "size": 10,
            "table_name": "",
            "schema_name": "",
            "readonly": false
        },
        {
            "auto_increment": false,
            "data_type": "VARCHAR",
            "db_name": "",
            "label": "possible_keys",
            "name": "COLUMN_6",
            "real_name": "possible_keys",
            "size": 4096,
            "table_name": "",
            "schema_name": "",
            "readonly": false
        },
        {
            "auto_increment": false,
            "data_type": "VARCHAR",
            "db_name": "",
            "label": "key",
            "name": "COLUMN_7",
            "real_name": "key",
            "size": 64,
            "table_name": "",
            "schema_name": "",
            "readonly": false
        },
        {
            "auto_increment": false,
            "data_type": "VARCHAR",
            "db_name": "",
            "label": "key_len",
            "name": "COLUMN_8",
            "real_name": "key_len",
            "size": 4096,
            "table_name": "",
            "schema_name": "",
            "readonly": false
        },
        {
            "auto_increment": false,
            "data_type": "VARCHAR",
            "db_name": "",
            "label": "ref",
            "name": "COLUMN_9",
            "real_name": "ref",
            "size": 1024,
            "table_name": "",
            "schema_name": "",
            "readonly": false
        },
        {
            "auto_increment": false,
            "data_type": "BIGINT UNSIGNED",
            "db_name": "",
            "label": "rows",
            "name": "COLUMN_10",
            "real_name": "rows",
            "size": 10,
            "table_name": "",
            "schema_name": "",
            "readonly": false
        },
        {
            "auto_increment": false,
            "data_type": "DOUBLE",
            "db_name": "",
            "label": "filtered",
            "name": "COLUMN_11",
            "real_name": "filtered",
            "size": 4,
            "table_name": "",
            "schema_name": "",
            "readonly": false
        },
        {
            "auto_increment": false,
            "data_type": "VARCHAR",
            "db_name": "",
            "label": "Extra",
            "name": "COLUMN_12",
            "real_name": "Extra",
            "size": 255,
            "table_name": "",
            "schema_name": "",
            "readonly": false
        }
    ],
    "result_type": "metadata",
    "rows": 1,
    "time_delay": 3,
    "can_edit": false,
    "can_export": false,
    "cannot_edit_reason": 1004,
    "data_sum": 44,
    "big_table": false,
    "sql": "/*sql from das*/explain SELECT *\nFROM lbw",
    "explain_sql": true,
    "exceed_data": false,
    "execute_status": "finished"
}

Status Codes

Status Code	Description
200	Success.
400	Client error.
500	Server error.

Error Codes

See Error Codes.

Parent topic: Intelligent O&M

Previous topic: Querying the SQL Execution Result (POST Request)

Next topic: Querying the Connection Sharing Information List