Shows the expected data movements and supporting queries for a user-specified SELECT query, in the expected execution order.

Syntax


EXPLAIN query ;

Returns

  • LINE_NUMBER: The line item count for each row in the response. Responses with one row will have LINE_NUMBER 1 only.

  • OP_DESCRIPTION: A short summary of the operation.

  • QUERY: The internal query for the operation.

Examples

EXPLAIN for a simple SELECT query on ORACLE1


EXPLAIN SELECT * FROM ORACLE1.oracle.INVENTORY.TWITTER_SALES2 ORDER BY twitter_id ;

Returns

LINE_NUMBER OP_DESCRIPTION QUERY
1 Returning Results from Oracle1 select “INVENTORY”,”TWITTER_SALES2”,”twitter_id” AS “twitter_id”,”INVENTORY”,”TWITTER_SALES2”.”saledate” AS “saledate”,”INVENTORY”,”TWITTER_SALES2”.”amnd” AS “amnt”,”INVENTORY”,”TWITTER_SALES2”,”retailer” AS “retailer” from “INVENTORY”,”TWITTER_SALES2” where 1 = 0 order by “INVENTORY”,”TWITTER_SALES2”,”twitter_id” ASC

EXPLAIN for a query joining data on MYSQL1 and ORACLE1


EXPLAIN SELECT s1.twitter_id, s1.amnt, s1.yr, S2.twitter_id, S2.saledate, S2.amnt FROM MYSQL1.inventory2.inventory2.sales as s1 LEFT OUTER JOIN ORACLE1.oracle.INVENTORY.TWITTER_SALES2 AS S2 ON (s1.twitter_id=S2.twitter_id);

Returns

LINE_NUMBER OP_DESCRIPTION QUERY
1 Running from cache  
2 Returning results from ORACLE1 select “s1”,”TWITTER_ID” AS “twitter_id”,”s1”,”AMNT” AS “amnt”,”s1”,”YR” AS “yr”,”S2”,”twitter_id” AS “twitter_id”,”S2”,”saledate” AS “saledate”,”S2”,”amnt” AS “amnt” from “CIRRO”,”AMETHST28133142ZA6813CA3BDF14” “s1” left outer join “INVENTORY”,”TWITTER_SALES2” “S2” on (1=1 and ((“s1”,”TWITTER_ID”=”S2”,”twitter_id”) AND (1=0)) AND (1=0) where 1=0

EXPLAIN for a query joining data on MYSQL1 and ORACLE1


EXPLAIN SELECT s1.twitter_id, s1.amnt, s1.yr, S2.twitter_id, S2.saledate, S2.amnt FROM MYSQL1.inventory2.inventory2.sales as s1 LEFT OUTER JOIN ORACLE1.oracle.INVENTORY.TWITTER_SALES2 AS S2 ON (s1.twitter_id=S2.twitter_id);

Returns

LINE_NUMBER OP_DESCRIPTION QUERY
1 Running from cache  
2 Returning results from ORACLE1 select “s1”,”TWITTER_ID” AS “twitter_id”,”s1”,”AMNT” AS “amnt”,”s1”,”YR” AS “yr”,”S2”,”twitter_id” AS “twitter_id”,”S2”,”saledate” AS “saledate”,”S2”,”amnt” as “amnt” from “CIRRO”.”AMETHYST28133142ZA6813CA3BDF14” “s1” left outer join “INVENTORY”,”TWITTER_SALES2” “S2” on (1=1 and ((“s1”,”TWITTER_ID”=”S2”,”twitter_id”) and 1=1) AND (1=0)) AND (1=0) where 1=0