EXPLAIN

Purpose

You can use this statement to interpret the execution plan for an SQL statement, which can be a SELECT, DELETE, INSERT, REPLACE or UPDATE statement.

EXPLAIN, DESCRIBE, and DESC are synonyms.

Syntax

/*Obtain table or column information.*/
{EXPLAIN | DESCRIBE | DESC} table_name [column_name];

/*Obtain the SQL plan.*/
{EXPLAIN | DESCRIBE | DESC} [explain_type] dml_statement;

explain_type:
    BASIC
  | OUTLINE
  | EXTENDED
  | EXTENDED_NOADDR
  | PARTITIONS
  | FORMAT = {TRADITIONAL| JSON}

dml_statement:
    SELECT statement
  | DELETE statement
  | INSERT statement
  | UPDATE statement
  | MERGE statement

Parameters

Examples

Create two tables respectively named tbl1 and tbl2.

obclient>CREATE TABLE tbl1(col1 INT,col2 INT);
Query OK, 0 rows affected

obclient>CREATE TABLE tbl2(col1 INT,col2 INT);
Query OK, 0 rows affected

• Obtain the information about the tbl1 table.

  obclient> EXPLAIN tbl1;
  +-------+------------+------+-----+---------+-------+
  | FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
  +-------+------------+------+-----+---------+-------+
  | COL1  | NUMBER(38) | YES  | NULL | NULL    | NULL  |
  | COL2  | NUMBER(38) | YES  | NULL | NULL    | NULL  |
  +-------+------------+------+-----+---------+-------+
  2 rows in set
  

• Obtain the information about the col2 column of the tbl2 table.

  obclient> EXPLAIN tbl2 col2;
  +-------+------------+------+-----+---------+-------+
  | FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
  +-------+------------+------+-----+---------+-------+
  | COL2  | NUMBER(38) | YES  | NULL | NULL    | NULL  |
  +-------+------------+------+-----+---------+-------+
  1 row in set
  

• Omit explain_type and return the execution plan of the SELECT statement.

  obclient> EXPLAIN SELECT * FROM tbl1,tbl2 WHERE tbl1.col2=tbl2.col2 AND tbl2.col1 > 4\G
  *************************** 1. row ***************************
  Query Plan: =======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST   |
  ---------------------------------------
  |0 |HASH JOIN  |    |9801000  |2442404|
  |1 | TABLE SCAN|TBL2|10000    |40790  |
  |2 | TABLE SCAN|TBL1|100000   |38681  |
  =======================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([TBL1.COL1], [TBL1.COL2], [TBL2.COL1], [TBL2.COL2]), filter(nil),
        equal_conds([TBL1.COL2 = TBL2.COL2]), other_conds(nil)
    1 - output([TBL2.COL2], [TBL2.COL1]), filter([TBL2.COL1 > 4]),
        access([TBL2.COL2], [TBL2.COL1]), partitions(p0)
    2 - output([TBL1.COL2], [TBL1.COL1]), filter(nil),
        access([TBL1.COL2], [TBL1.COL1]), partitions(p0)
  
  1 row in set
  

• Use the EXTENDED_NOADDR keyword to output additional information.

  obclient> EXPLAIN EXTENDED_NOADDR SELECT * FROM tbl1,tbl2 WHERE tbl1.col2=tbl2.col2 AND tbl2.col1 > 4\G
  *************************** 1. row ***************************
  Query Plan: =======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST   |
  ---------------------------------------
  |0 |HASH JOIN  |    |9801000  |2442404|
  |1 | TABLE SCAN|TBL2|10000    |40790  |
  |2 | TABLE SCAN|TBL1|100000   |38681  |
  =======================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([TBL1.COL1], [TBL1.COL2], [TBL2.COL1], [TBL2.COL2]), filter(nil),
        equal_conds([TBL1.COL2 = TBL2.COL2]), other_conds(nil)
    1 - output([TBL2.COL2], [TBL2.COL1]), filter([TBL2.COL1 > 4]),
        access([TBL2.COL2], [TBL2.COL1]), partitions(p0),
        is_index_back=false, filter_before_indexback[false],
        range_key([TBL2.__pk_increment]), range(MIN ; MAX)always true
    2 - output([TBL1.COL2], [TBL1.COL1]), filter(nil),
        access([TBL1.COL2], [TBL1.COL1]), partitions(p0),
        is_index_back=false,
        range_key([TBL1.__pk_increment]), range(MIN ; MAX)always true
  
  1 row in set
  

• Show the execution plan of the INSERT statement in TRADITIONAL format.

  obclient> EXPLAIN FORMAT=TRADITIONAL INSERT INTO TBL1 VALUES(1,1)\G
  *************************** 1. row ***************************
  Query Plan: ====================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST|
  ------------------------------------
  |0 |INSERT     |    |1        |1   |
  |1 | EXPRESSION|    |1        |1   |
  ====================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([column_conv(BIGINT UNSIGNED,PS:(-1,-1),NOT NULL,nextval(1))], [column_conv(NUMBER,PS:(38,0),NULL,__values.COL1)], [column_conv(NUMBER,PS:(38,0),NULL,__values.COL2)]), filter(nil),
        columns([{TBL1: ({TBL1: (TBL1.__pk_increment, TBL1.COL1, TBL1.COL2)})}]), partitions(p0)
    1 - output([__values.COL1], [__values.COL2]), filter(nil)
        values({1, 1})
  
  1 row in set
  

• Show the execution plan of the SELECT statement in JSON format.

  obclient> EXPLAIN FORMAT=JSON SELECT * FROM tbl1,tbl2 WHERE tbl1.col2=tbl2.col2 AND tbl2.col1 > 4\G
  *************************** 1. row ***************************
  Query Plan: {
    "ID":2,
    "OPERATOR":"JOIN",
    "NAME":"JOIN",
    "EST.ROWS":9800999,
    "COST":2442403,
    "output": [
      "TBL1.COL1",
      "TBL1.COL2",
      "TBL2.COL1",
      "TBL2.COL2"
    ],
    "CHILD_1": {
      "ID":0
      "OPERATOR":"TABLE SCAN",
      "NAME":"TABLE SCAN",
      "EST.ROWS":10000,
      "COST":40789,
      "output": [
        "TBL2.COL2",
        "TBL2.COL1"
      ]
    },
    "CHILD_2": {
      "ID":1,
      "OPERATOR":"TABLE SCAN",
      "NAME":"TABLE SCAN",
      "EST.ROWS":100000,
      "COST":38680,
      "output": [
        "TBL1.COL2",
        "TBL1.COL1"
      ]
    }
  }
  1 row in set