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: =======================================
  IDOPERATOR   NAMEEST. ROWSCOST   
  ---------------------------------------
  0 HASH JOIN      9801000  2442404
  1  TABLE SCANTBL210000    40790  
  2  TABLE SCANTBL1100000   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
  

• Output extended information by using the EXTENDED_NOADDR keyword.

  obclient> EXPLAIN EXTENDED_NOADDR SELECT * FROM tbl1,tbl2 WHERE tbl1.col2=tbl2.col2 AND tbl2.col1 > 4\G
  *************************** 1. row ***************************
  Query Plan: =======================================
  IDOPERATOR   NAMEEST. ROWSCOST   
  ---------------------------------------
  0 HASH JOIN      9801000  2442404
  1  TABLE SCANTBL210000    40790  
  2  TABLE SCANTBL1100000   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: ====================================
  IDOPERATOR   NAMEEST. ROWSCOST
  ------------------------------------
  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