hive> SET hive.cbo.enable=true; hive> EXPLAIN > SELECT * > FROM i1122 AS t1 > WHERE EXISTS ( > SELECT 1 > FROM i1122 AS t2 > WHERE t2.id = substr(t1.id,4) > ); OK STAGE DEPENDENCIES: Stage-3 is a root stage Stage-8 depends on stages: Stage-3 Stage-6 depends on stages: Stage-8 Stage-5 depends on stages: Stage-6 , consists of Stage-7, Stage-1 Stage-7 has a backup stage: Stage-1 Stage-4 depends on stages: Stage-7 Stage-1 Stage-0 depends on stages: Stage-4, Stage-1 STAGE PLANS: Stage: Stage-3 Map Reduce Map Operator Tree: TableScan alias: t1 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: string), id (type: string) outputColumnNames: id, id Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Group By Operator keys: id (type: string) mode: hash outputColumnNames: _col0 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col0 (type: string) sort order: + Map-reduce partition columns: _col0 (type: string) Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Reduce Operator Tree: Group By Operator keys: KEY._col0 (type: string) mode: mergepartial outputColumnNames: _col0 Statistics: Num rows: 1 Data size: 2 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe Stage: Stage-8 Map Reduce Local Work Alias -> Map Local Tables: $hdt$_1:$hdt$_1:t2 Fetch Operator limit: -1 Alias -> Map Local Operator Tree: $hdt$_1:$hdt$_1:t2 TableScan alias: t2 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: string) outputColumnNames: _col0 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE HashTable Sink Operator keys: 0 _col0 (type: string) 1 substr(_col0, 4) (type: string) Stage: Stage-6 Map Reduce Map Operator Tree: TableScan Map Join Operator condition map: Inner Join 0 to 1 keys: 0 _col0 (type: string) 1 substr(_col0, 4) (type: string) outputColumnNames: _col1 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: _col1 (type: string) outputColumnNames: _col0 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Group By Operator keys: _col0 (type: string) mode: hash outputColumnNames: _col0 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe Local Work: Map Reduce Local Work Stage: Stage-5 Conditional Operator Stage: Stage-7 Map Reduce Local Work Alias -> Map Local Tables: $INTNAME Fetch Operator limit: -1 Alias -> Map Local Operator Tree: $INTNAME TableScan HashTable Sink Operator keys: 0 _col0 (type: string) 1 _col0 (type: string) Stage: Stage-4 Map Reduce Map Operator Tree: TableScan alias: t1 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: string) outputColumnNames: _col0 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Map Join Operator condition map: Left Semi Join 0 to 1 keys: 0 _col0 (type: string) 1 _col0 (type: string) outputColumnNames: _col0 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe Local Work: Map Reduce Local Work Stage: Stage-1 Map Reduce Map Operator Tree: TableScan alias: t1 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: string) outputColumnNames: _col0 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col0 (type: string) sort order: + Map-reduce partition columns: _col0 (type: string) Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE TableScan Reduce Output Operator key expressions: _col0 (type: string) sort order: + Map-reduce partition columns: _col0 (type: string) Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE Reduce Operator Tree: Join Operator condition map: Left Semi Join 0 to 1 keys: 0 _col0 (type: string) 1 _col0 (type: string) outputColumnNames: _col0 Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false Statistics: Num rows: 2 Data size: 5 Basic stats: COMPLETE Column stats: NONE table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: ListSink Time taken: 0.291 seconds, Fetched: 183 row(s) hive>