Queries the details of optimization suggestions. The operation applies only to the tickets for the data changes and SQL review.

For more information about the SQL review feature, see SQL review.

Debugging

OpenAPI Explorer automatically calculates the signature value. For your convenience, we recommend that you call this operation in OpenAPI Explorer. OpenAPI Explorer dynamically generates the sample code of the operation for different SDKs.

Request parameters

Parameter Type Required Example Description
Action String Yes GetSQLReviewOptimizeDetail

The operation that you want to perform. Set the value to GetSQLReviewOptimizeDetail.

SQLReviewQueryKey String Yes a57e54ec5433475ea3082d882fdb****

The key that is used to query the details of optimization suggestions. You can call the ListSQLReviewOriginSQL operation to query the key.

Tid Long No 1

The ID of the tenant. You can call the GetUserActiveTenant or ListUserTenants operation to query the ID of the tenant.

RegionId String No cn-hangzhou

The ID of the region where DMS is activated. For more information about the valid values of the parameter, see RegionID parameter.

Note Set this parameter to the ID of the region that is close to your service.

Response parameters

Parameter Type Example Description
RequestId String 0C1CB646-1DE4-4AD0-B4A4-7D47DD52E931

The ID of the request.

ErrorCode String UnknownError

The error code returned.

ErrorMessage String UnknownError

The error message returned.

Success Boolean true

Indicates whether the request is successful.

OptimizeDetail Object

The details of optimization suggestions for SQL statements.

QueryKey String a57e54ec5433475ea3082d882fdb****

The key that is used to query the details of optimization suggestions.

InstanceId Integer 123321

The ID of the instance to which the database belongs.

DbId Integer 111222

The ID of the database.

SqlType String UPDATE

The type of the SQL statement. Valid values: DELETE, UPDATE, and ALTER_TABLE.

QualityResult Object

The quality of the SQL statement.

ErrorMessage String syntax error

The error message returned.

Results Array of Results

The review results based on rules.

RuleName String SELECT_SUGGEST_ASSIGN_WHERE

The name of the rule. For more information, see SQL review optimization.

Feedback String MUST_IMPROVE

The optimization suggestion for the SQL statement. Valid values:

  • MUST_IMPROVE: The SQL statement must be improved.
  • POTENTIAL_ISSUE: The SQL statement contains potential issues.
  • SUGGEST_IMPROVE: We recommend that you improve the SQL statement.
  • USEDMSTOOLKIT: We recommend that you change schemas without locking tables.
  • USEDMSDML_UNLOCK: We recommend that you change data without locking tables.
  • TABLEINDEXSUGGEST: We recommend that you use SQL statements that use indexes.
Comments String xxx specifies that SQL statements must contain the WHERE clause.

The comment that is specified when you create the SQL review rule. For more information, see SQL review optimization.

Messages Array of String xxx table does not exist.

The review results.

Scripts Array of Scripts

The SQL script for data changes.

OpType String AddIndex

The purpose of the SQL script. The value is set to AddIndex.

Content String alter table xxx add index idx_xx(yyy);

The content of the SQL script.

TableName String xxx

The name of the table.

RuleType String REVIEW

The type of the SQL review rule. Valid values:

  • REVIEW: a rule that is used to review SQL statements based on standards.
  • OPTIMIZE: a rule that is used to provide optimization suggestions.
OccurError Boolean false

Indicates whether an error occurs. Valid values:

  • true: An error occurs.
  • false: No error occurs.

Examples

Sample requests

http(s)://dms-enterprise.aliyuncs.com?Action=GetSQLReviewOptimizeDetail
&SQLReviewQueryKey=a57e54ec5433475ea3082d882fdb89c5
&Tid=1
&<Common request parameters>

Sample success responses

XML format

HTTP/1.1 200 OK
Content-Type:application/xml

<GetSQLReviewOptimizeDetailResponse>
    <RequestId>0C1CB646-1DE4-4AD0-B4A4-7D47DD52E931</RequestId>
    <ErrorCode>UnknownError</ErrorCode>
    <ErrorMessage>UnknownError</ErrorMessage>
    <Success>true</Success>
    <OptimizeDetail>
        <QueryKey>a57e54ec5433475ea3082d882fdb****</QueryKey>
        <InstanceId>123321</InstanceId>
        <DbId>111222</DbId>
        <SqlType>UPDATE</SqlType>
        <QualityResult>
            <ErrorMessage>syntax error</ErrorMessage>
            <Results>
                <RuleName>SELECT_SUGGEST_ASSIGN_WHERE</RuleName>
                <Feedback>MUST_IMPROVE</Feedback>
                <Comments>xxx specifies that SQL statements must contain the WHERE clause.</Comments>
                <Messages>xxx table does not exist.</Messages>
                <Scripts>
                    <OpType>AddIndex</OpType>
                    <Content>alter table xxx add index idx_xx(yyy);</Content>
                    <TableName>xxx</TableName>
                </Scripts>
                <RuleType>REVIEW</RuleType>
            </Results>
            <OccurError>false</OccurError>
        </QualityResult>
    </OptimizeDetail>
</GetSQLReviewOptimizeDetailResponse>

JSON format

HTTP/1.1 200 OK
Content-Type:application/json

{
  "RequestId" : "0C1CB646-1DE4-4AD0-B4A4-7D47DD52E931",
  "ErrorCode" : "UnknownError",
  "ErrorMessage" : "UnknownError",
  "Success" : true,
  "OptimizeDetail" : {
    "QueryKey" : "a57e54ec5433475ea3082d882fdb****",
    "InstanceId" : 123321,
    "DbId" : 111222,
    "SqlType" : "UPDATE",
    "QualityResult" : {
      "ErrorMessage" : "syntax error",
      "Results" : [ {
        "RuleName" : "SELECT_SUGGEST_ASSIGN_WHERE",
        "Feedback" : "MUST_IMPROVE",
        "Comments" : "xxx specifies that SQL statements must contain the WHERE clause.",
        "Messages" : [ "xxx table does not exist." ],
        "Scripts" : [ {
          "OpType" : "AddIndex",
          "Content" : "alter table xxx add index idx_xx(yyy);",
          "TableName" : "xxx"
        } ],
        "RuleType" : "REVIEW"
      } ],
      "OccurError" : false
    }
  }
}

Error codes

For a list of error codes, visit the API Error Center.