Example of using the explain query REST API to get the ARJDBC query execution plan

Analyzing query execution plan scenario

While generating the quarterly sales report, Seth experiences a delay. He escalated the issue to the IT team.

To understand the cause of the delay, the IT team decided to analyze the ARJDBC query execution plan. The IT team uses the explain query REST API to fetch the ARJDBC query execution plan from the .

The following infographic shows fetching query execution plan by using the explain query REST API:

Retrieving query execution plan workflow

• The database administrator, retrieves the query execution plan by using the Postman client and the explain query REST API call.
  The example of the explain query REST API call is as follows:

  POST /api/arsys/v1.0/sql/explain
  Request: RestSqlQuery object
  
  {
      "sql": "select ...",
      "bind_variables": {
          "1": {
              "value": "Value",
              "type": "Type"
          },
          "2": {
              "value": "Value",
              "type": "Type"
          },
          ...
      },
      "properties": {
          "name", "Value",
          "name2", "Value2"
      }
  }

• The request is processed and a response that contains the query execution plan is sent. 
  See the example below: 

  {
     "execution_plan": "Execution plan as returned by database",
     "table_map" {
         "form/record name": [
             "table_name alias",
             "table_name2 alias2"
          ],
         "form/record name2": [
             "table_name alias",
             "table_name2 alias2"
          ],
          ...
     }
  }
  table_map": {
          "User": {
              "table_info": [
                  {
                      "name": "T35",
                      "alias": "J0"
                  }
              ],
              "field_column_map": {
                  "Full Name": "C8"
             }

Analyzing query execution plan result

The IT team analyzes the query execution plan, and updates the ARJDBC query by adding an index on the Full Name field to speed up the query processing time.

After the Full Name field is indexed, the query fetches results faster.

explain query REST API

The following table lists details about using the explain query REST API to get the query execution plan.

Sample query and output formats

The  returns the query execution plan in a database-specific format.

See the sample query and output formats for MS SQL, Oracle, and PostgreSQL databases.