Saving all output to "!!{outputDirectory}!!/ql_rewrite_gbtoidx.q.raw". Enter "record" with no arguments to stop it.
>>>  !run !!{qFileDirectory}!!/ql_rewrite_gbtoidx.q
>>>  
>>>  DROP TABLE lineitem;
No rows affected 
>>>  CREATE TABLE lineitem (L_ORDERKEY      INT, 
L_PARTKEY       INT, 
L_SUPPKEY       INT, 
L_LINENUMBER    INT, 
L_QUANTITY      DOUBLE, 
L_EXTENDEDPRICE DOUBLE, 
L_DISCOUNT      DOUBLE, 
L_TAX           DOUBLE, 
L_RETURNFLAG    STRING, 
L_LINESTATUS    STRING, 
l_shipdate      STRING, 
L_COMMITDATE    STRING, 
L_RECEIPTDATE   STRING, 
L_SHIPINSTRUCT  STRING, 
L_SHIPMODE      STRING, 
L_COMMENT       STRING) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '|';
No rows affected 
>>>  
>>>  LOAD DATA LOCAL INPATH '../data/files/lineitem.txt' OVERWRITE INTO TABLE lineitem;
No rows affected 
>>>  
>>>  CREATE INDEX lineitem_lshipdate_idx ON TABLE lineitem(l_shipdate) AS 'org.apache.hadoop.hive.ql.index.AggregateIndexHandler' WITH DEFERRED REBUILD IDXPROPERTIES("AGGREGATES"="count(l_shipdate)");
No rows affected 
>>>  ALTER INDEX lineitem_lshipdate_idx ON lineitem REBUILD;
No rows affected 
>>>  
>>>  explain select l_shipdate, count(l_shipdate) 
from lineitem 
group by l_shipdate;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME lineitem))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL l_shipdate)) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL l_shipdate)))) (TOK_GROUPBY (TOK_TABLE_OR_COL l_shipdate))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        lineitem '
'          TableScan'
'            alias: lineitem'
'            Select Operator'
'              expressions:'
'                    expr: l_shipdate'
'                    type: string'
'              outputColumnNames: l_shipdate'
'              Group By Operator'
'                aggregations:'
'                      expr: count(l_shipdate)'
'                bucketGroup: false'
'                keys:'
'                      expr: l_shipdate'
'                      type: string'
'                mode: hash'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: string'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: string'
'                  tag: -1'
'                  value expressions:'
'                        expr: _col1'
'                        type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: count(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: string'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: string'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
69 rows selected 
>>>  
>>>  select l_shipdate, count(l_shipdate) 
from lineitem 
group by l_shipdate 
order by l_shipdate;
'l_shipdate','_c1'
'1992-04-27','1'
'1992-07-02','1'
'1992-07-10','1'
'1992-07-21','1'
'1993-04-01','1'
'1993-04-13','1'
'1993-05-14','1'
'1993-10-29','2'
'1993-11-09','2'
'1993-12-04','1'
'1993-12-09','2'
'1993-12-14','1'
'1994-01-12','1'
'1994-01-16','1'
'1994-01-26','2'
'1994-02-02','1'
'1994-02-13','1'
'1994-02-19','1'
'1994-02-21','1'
'1994-03-03','1'
'1994-03-17','1'
'1994-06-03','1'
'1994-06-06','1'
'1994-07-02','1'
'1994-07-19','1'
'1994-07-31','1'
'1994-08-08','1'
'1994-08-17','1'
'1994-08-24','1'
'1994-09-30','1'
'1994-10-03','1'
'1994-10-16','1'
'1994-10-31','1'
'1994-12-01','1'
'1994-12-24','1'
'1994-12-30','1'
'1995-04-20','1'
'1995-07-06','1'
'1995-07-17','1'
'1995-07-21','1'
'1995-08-04','1'
'1995-08-07','1'
'1995-08-14','1'
'1995-08-28','1'
'1995-10-23','1'
'1995-11-08','1'
'1995-11-26','1'
'1996-01-10','1'
'1996-01-15','1'
'1996-01-16','1'
'1996-01-19','1'
'1996-01-22','1'
'1996-01-29','1'
'1996-01-30','1'
'1996-02-01','2'
'1996-02-03','1'
'1996-02-10','1'
'1996-02-11','1'
'1996-02-21','1'
'1996-03-13','1'
'1996-03-21','1'
'1996-03-30','1'
'1996-04-12','1'
'1996-04-21','1'
'1996-05-07','1'
'1996-09-26','1'
'1996-09-29','1'
'1996-10-02','1'
'1996-10-17','1'
'1996-11-04','1'
'1996-11-14','1'
'1996-12-08','1'
'1997-01-25','1'
'1997-01-27','1'
'1997-01-28','1'
'1997-02-20','1'
'1997-03-18','1'
'1997-04-17','1'
'1997-04-19','1'
'1998-01-29','1'
'1998-02-23','1'
'1998-03-05','1'
'1998-04-10','1'
'1998-04-12','1'
'1998-05-23','1'
'1998-06-19','1'
'1998-06-24','1'
'1998-06-26','1'
'1998-06-27','1'
'1998-07-04','1'
'1998-08-11','1'
'1998-08-13','1'
'1998-10-09','1'
'1998-10-23','1'
'1998-10-30','1'
95 rows selected 
>>>  
>>>  set hive.optimize.index.groupby=true;
No rows affected 
>>>  
>>>  explain select l_shipdate, count(l_shipdate) 
from lineitem 
group by l_shipdate;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME lineitem))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL l_shipdate)) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL l_shipdate)))) (TOK_GROUPBY (TOK_TABLE_OR_COL l_shipdate))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        ql_rewrite_gbtoidx__lineitem_lineitem_lshipdate_idx__ '
'          TableScan'
'            alias: ql_rewrite_gbtoidx__lineitem_lineitem_lshipdate_idx__'
'            Select Operator'
'              expressions:'
'                    expr: l_shipdate'
'                    type: string'
'                    expr: _count_of_l_shipdate'
'                    type: bigint'
'              outputColumnNames: l_shipdate, _count_of_l_shipdate'
'              Group By Operator'
'                aggregations:'
'                      expr: sum(_count_of_l_shipdate)'
'                bucketGroup: true'
'                keys:'
'                      expr: l_shipdate'
'                      type: string'
'                mode: hash'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: string'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: string'
'                  tag: -1'
'                  value expressions:'
'                        expr: _col1'
'                        type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: string'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: string'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
71 rows selected 
>>>  
>>>  select l_shipdate, count(l_shipdate) 
from lineitem 
group by l_shipdate 
order by l_shipdate;
'l_shipdate','_c1'
'1992-04-27','1'
'1992-07-02','1'
'1992-07-10','1'
'1992-07-21','1'
'1993-04-01','1'
'1993-04-13','1'
'1993-05-14','1'
'1993-10-29','2'
'1993-11-09','2'
'1993-12-04','1'
'1993-12-09','2'
'1993-12-14','1'
'1994-01-12','1'
'1994-01-16','1'
'1994-01-26','2'
'1994-02-02','1'
'1994-02-13','1'
'1994-02-19','1'
'1994-02-21','1'
'1994-03-03','1'
'1994-03-17','1'
'1994-06-03','1'
'1994-06-06','1'
'1994-07-02','1'
'1994-07-19','1'
'1994-07-31','1'
'1994-08-08','1'
'1994-08-17','1'
'1994-08-24','1'
'1994-09-30','1'
'1994-10-03','1'
'1994-10-16','1'
'1994-10-31','1'
'1994-12-01','1'
'1994-12-24','1'
'1994-12-30','1'
'1995-04-20','1'
'1995-07-06','1'
'1995-07-17','1'
'1995-07-21','1'
'1995-08-04','1'
'1995-08-07','1'
'1995-08-14','1'
'1995-08-28','1'
'1995-10-23','1'
'1995-11-08','1'
'1995-11-26','1'
'1996-01-10','1'
'1996-01-15','1'
'1996-01-16','1'
'1996-01-19','1'
'1996-01-22','1'
'1996-01-29','1'
'1996-01-30','1'
'1996-02-01','2'
'1996-02-03','1'
'1996-02-10','1'
'1996-02-11','1'
'1996-02-21','1'
'1996-03-13','1'
'1996-03-21','1'
'1996-03-30','1'
'1996-04-12','1'
'1996-04-21','1'
'1996-05-07','1'
'1996-09-26','1'
'1996-09-29','1'
'1996-10-02','1'
'1996-10-17','1'
'1996-11-04','1'
'1996-11-14','1'
'1996-12-08','1'
'1997-01-25','1'
'1997-01-27','1'
'1997-01-28','1'
'1997-02-20','1'
'1997-03-18','1'
'1997-04-17','1'
'1997-04-19','1'
'1998-01-29','1'
'1998-02-23','1'
'1998-03-05','1'
'1998-04-10','1'
'1998-04-12','1'
'1998-05-23','1'
'1998-06-19','1'
'1998-06-24','1'
'1998-06-26','1'
'1998-06-27','1'
'1998-07-04','1'
'1998-08-11','1'
'1998-08-13','1'
'1998-10-09','1'
'1998-10-23','1'
'1998-10-30','1'
95 rows selected 
>>>  
>>>  set hive.optimize.index.groupby=false;
No rows affected 
>>>  
>>>  
>>>  explain select year(l_shipdate) as year, 
month(l_shipdate) as month, 
count(l_shipdate) as monthly_shipments 
from lineitem 
group by year(l_shipdate), month(l_shipdate) 
order by year, month;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME lineitem))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION year (TOK_TABLE_OR_COL l_shipdate)) year) (TOK_SELEXPR (TOK_FUNCTION month (TOK_TABLE_OR_COL l_shipdate)) month) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL l_shipdate)) monthly_shipments)) (TOK_GROUPBY (TOK_FUNCTION year (TOK_TABLE_OR_COL l_shipdate)) (TOK_FUNCTION month (TOK_TABLE_OR_COL l_shipdate))) (TOK_ORDERBY (TOK_TABSORTCOLNAMEASC (TOK_TABLE_OR_COL year)) (TOK_TABSORTCOLNAMEASC (TOK_TABLE_OR_COL month)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-2 depends on stages: Stage-1'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        lineitem '
'          TableScan'
'            alias: lineitem'
'            Select Operator'
'              expressions:'
'                    expr: l_shipdate'
'                    type: string'
'              outputColumnNames: l_shipdate'
'              Group By Operator'
'                aggregations:'
'                      expr: count(l_shipdate)'
'                bucketGroup: false'
'                keys:'
'                      expr: year(l_shipdate)'
'                      type: int'
'                      expr: month(l_shipdate)'
'                      type: int'
'                mode: hash'
'                outputColumnNames: _col0, _col1, _col2'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: int'
'                  sort order: ++'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: int'
'                  tag: -1'
'                  value expressions:'
'                        expr: _col2'
'                        type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: count(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1, _col2'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: int'
'                  expr: _col2'
'                  type: bigint'
'            outputColumnNames: _col0, _col1, _col2'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
''
'  Stage: Stage-2'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        file:!!{hive.exec.scratchdir}!! '
'            Reduce Output Operator'
'              key expressions:'
'                    expr: _col0'
'                    type: int'
'                    expr: _col1'
'                    type: int'
'              sort order: ++'
'              tag: -1'
'              value expressions:'
'                    expr: _col0'
'                    type: int'
'                    expr: _col1'
'                    type: int'
'                    expr: _col2'
'                    type: bigint'
'      Reduce Operator Tree:'
'        Extract'
'          File Output Operator'
'            compressed: false'
'            GlobalTableId: 0'
'            table:'
'                input format: org.apache.hadoop.mapred.TextInputFormat'
'                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
108 rows selected 
>>>  
>>>  select year(l_shipdate) as year, 
month(l_shipdate) as month, 
count(l_shipdate) as monthly_shipments 
from lineitem 
group by year(l_shipdate), month(l_shipdate) 
order by year, month;
'year','month','monthly_shipments'
'1992','4','1'
'1992','7','3'
'1993','4','2'
'1993','5','1'
'1993','10','2'
'1993','11','2'
'1993','12','4'
'1994','1','4'
'1994','2','4'
'1994','3','2'
'1994','6','2'
'1994','7','3'
'1994','8','3'
'1994','9','1'
'1994','10','3'
'1994','12','3'
'1995','4','1'
'1995','7','3'
'1995','8','4'
'1995','10','1'
'1995','11','2'
'1996','1','7'
'1996','2','6'
'1996','3','3'
'1996','4','2'
'1996','5','1'
'1996','9','2'
'1996','10','2'
'1996','11','2'
'1996','12','1'
'1997','1','3'
'1997','2','1'
'1997','3','1'
'1997','4','2'
'1998','1','1'
'1998','2','1'
'1998','3','1'
'1998','4','2'
'1998','5','1'
'1998','6','4'
'1998','7','1'
'1998','8','2'
'1998','10','3'
43 rows selected 
>>>  
>>>  set hive.optimize.index.groupby=true;
No rows affected 
>>>  
>>>  explain select year(l_shipdate) as year, 
month(l_shipdate) as month, 
count(l_shipdate) as monthly_shipments 
from lineitem 
group by year(l_shipdate), month(l_shipdate) 
order by year, month;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME lineitem))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION year (TOK_TABLE_OR_COL l_shipdate)) year) (TOK_SELEXPR (TOK_FUNCTION month (TOK_TABLE_OR_COL l_shipdate)) month) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL l_shipdate)) monthly_shipments)) (TOK_GROUPBY (TOK_FUNCTION year (TOK_TABLE_OR_COL l_shipdate)) (TOK_FUNCTION month (TOK_TABLE_OR_COL l_shipdate))) (TOK_ORDERBY (TOK_TABSORTCOLNAMEASC (TOK_TABLE_OR_COL year)) (TOK_TABSORTCOLNAMEASC (TOK_TABLE_OR_COL month)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-2 depends on stages: Stage-1'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        ql_rewrite_gbtoidx__lineitem_lineitem_lshipdate_idx__ '
'          TableScan'
'            alias: ql_rewrite_gbtoidx__lineitem_lineitem_lshipdate_idx__'
'            Select Operator'
'              expressions:'
'                    expr: l_shipdate'
'                    type: string'
'                    expr: _count_of_l_shipdate'
'                    type: bigint'
'              outputColumnNames: l_shipdate, _count_of_l_shipdate'
'              Group By Operator'
'                aggregations:'
'                      expr: sum(_count_of_l_shipdate)'
'                bucketGroup: false'
'                keys:'
'                      expr: year(l_shipdate)'
'                      type: int'
'                      expr: month(l_shipdate)'
'                      type: int'
'                mode: hash'
'                outputColumnNames: _col0, _col1, _col2'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: int'
'                  sort order: ++'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: int'
'                  tag: -1'
'                  value expressions:'
'                        expr: _col2'
'                        type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1, _col2'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: int'
'                  expr: _col2'
'                  type: bigint'
'            outputColumnNames: _col0, _col1, _col2'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
''
'  Stage: Stage-2'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        file:!!{hive.exec.scratchdir}!! '
'            Reduce Output Operator'
'              key expressions:'
'                    expr: _col0'
'                    type: int'
'                    expr: _col1'
'                    type: int'
'              sort order: ++'
'              tag: -1'
'              value expressions:'
'                    expr: _col0'
'                    type: int'
'                    expr: _col1'
'                    type: int'
'                    expr: _col2'
'                    type: bigint'
'      Reduce Operator Tree:'
'        Extract'
'          File Output Operator'
'            compressed: false'
'            GlobalTableId: 0'
'            table:'
'                input format: org.apache.hadoop.mapred.TextInputFormat'
'                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
110 rows selected 
>>>  
>>>  select year(l_shipdate) as year, 
month(l_shipdate) as month, 
count(l_shipdate) as monthly_shipments 
from lineitem 
group by year(l_shipdate), month(l_shipdate) 
order by year, month;
'year','month','monthly_shipments'
'1992','4','1'
'1992','7','3'
'1993','4','2'
'1993','5','1'
'1993','10','2'
'1993','11','2'
'1993','12','4'
'1994','1','4'
'1994','2','4'
'1994','3','2'
'1994','6','2'
'1994','7','3'
'1994','8','3'
'1994','9','1'
'1994','10','3'
'1994','12','3'
'1995','4','1'
'1995','7','3'
'1995','8','4'
'1995','10','1'
'1995','11','2'
'1996','1','7'
'1996','2','6'
'1996','3','3'
'1996','4','2'
'1996','5','1'
'1996','9','2'
'1996','10','2'
'1996','11','2'
'1996','12','1'
'1997','1','3'
'1997','2','1'
'1997','3','1'
'1997','4','2'
'1998','1','1'
'1998','2','1'
'1998','3','1'
'1998','4','2'
'1998','5','1'
'1998','6','4'
'1998','7','1'
'1998','8','2'
'1998','10','3'
43 rows selected 
>>>  
>>>  explain select lastyear.month, 
thisyear.month, 
(thisyear.monthly_shipments - lastyear.monthly_shipments) / 
lastyear.monthly_shipments as monthly_shipments_delta 
from (select year(l_shipdate) as year, 
month(l_shipdate) as month, 
count(l_shipdate) as monthly_shipments 
from lineitem 
where year(l_shipdate) = 1997 
group by year(l_shipdate), month(l_shipdate) 
)  lastyear join 
(select year(l_shipdate) as year, 
month(l_shipdate) as month, 
count(l_shipdate) as monthly_shipments 
from lineitem 
where year(l_shipdate) = 1998 
group by year(l_shipdate), month(l_shipdate) 
)  thisyear 
on lastyear.month = thisyear.month;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME lineitem))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION year (TOK_TABLE_OR_COL l_shipdate)) year) (TOK_SELEXPR (TOK_FUNCTION month (TOK_TABLE_OR_COL l_shipdate)) month) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL l_shipdate)) monthly_shipments)) (TOK_WHERE (= (TOK_FUNCTION year (TOK_TABLE_OR_COL l_shipdate)) 1997)) (TOK_GROUPBY (TOK_FUNCTION year (TOK_TABLE_OR_COL l_shipdate)) (TOK_FUNCTION month (TOK_TABLE_OR_COL l_shipdate))))) lastyear) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME lineitem))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION year (TOK_TABLE_OR_COL l_shipdate)) year) (TOK_SELEXPR (TOK_FUNCTION month (TOK_TABLE_OR_COL l_shipdate)) month) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL l_shipdate)) monthly_shipments)) (TOK_WHERE (= (TOK_FUNCTION year (TOK_TABLE_OR_COL l_shipdate)) 1998)) (TOK_GROUPBY (TOK_FUNCTION year (TOK_TABLE_OR_COL l_shipdate)) (TOK_FUNCTION month (TOK_TABLE_OR_COL l_shipdate))))) thisyear) (= (. (TOK_TABLE_OR_COL lastyear) month) (. (TOK_TABLE_OR_COL thisyear) month)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL lastyear) month)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL thisyear) month)) (TOK_SELEXPR (/ (- (. (TOK_TABLE_OR_COL thisyear) monthly_shipments) (. (TOK_TABLE_OR_COL lastyear) monthly_shipments)) (. (TOK_TABLE_OR_COL lastyear) monthly_shipments)) monthly_shipments_delta))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-2 depends on stages: Stage-1, Stage-3'
'  Stage-3 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        lastyear:ql_rewrite_gbtoidx__lineitem_lineitem_lshipdate_idx__ '
'          TableScan'
'            alias: lastyear:ql_rewrite_gbtoidx__lineitem_lineitem_lshipdate_idx__'
'            Filter Operator'
'              predicate:'
'                  expr: (year(l_shipdate) = 1997)'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: l_shipdate'
'                      type: string'
'                      expr: _count_of_l_shipdate'
'                      type: bigint'
'                outputColumnNames: l_shipdate, _count_of_l_shipdate'
'                Group By Operator'
'                  aggregations:'
'                        expr: sum(_count_of_l_shipdate)'
'                  bucketGroup: false'
'                  keys:'
'                        expr: year(l_shipdate)'
'                        type: int'
'                        expr: month(l_shipdate)'
'                        type: int'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1, _col2'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    sort order: ++'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    tag: -1'
'                    value expressions:'
'                          expr: _col2'
'                          type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1, _col2'
'          Select Operator'
'            expressions:'
'                  expr: _col1'
'                  type: int'
'                  expr: _col2'
'                  type: bigint'
'            outputColumnNames: _col1, _col2'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
''
'  Stage: Stage-2'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        $INTNAME '
'            Reduce Output Operator'
'              key expressions:'
'                    expr: _col1'
'                    type: int'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: _col1'
'                    type: int'
'              tag: 0'
'              value expressions:'
'                    expr: _col1'
'                    type: int'
'                    expr: _col2'
'                    type: bigint'
'        $INTNAME1 '
'            Reduce Output Operator'
'              key expressions:'
'                    expr: _col1'
'                    type: int'
'              sort order: +'
'              Map-reduce partition columns:'
'                    expr: _col1'
'                    type: int'
'              tag: 1'
'              value expressions:'
'                    expr: _col1'
'                    type: int'
'                    expr: _col2'
'                    type: bigint'
'      Reduce Operator Tree:'
'        Join Operator'
'          condition map:'
'               Inner Join 0 to 1'
'          condition expressions:'
'            0 {VALUE._col1} {VALUE._col2}'
'            1 {VALUE._col1} {VALUE._col2}'
'          handleSkewJoin: false'
'          outputColumnNames: _col1, _col2, _col4, _col5'
'          Select Operator'
'            expressions:'
'                  expr: _col1'
'                  type: int'
'                  expr: _col4'
'                  type: int'
'                  expr: ((_col5 - _col2) / _col2)'
'                  type: double'
'            outputColumnNames: _col0, _col1, _col2'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-3'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        thisyear:ql_rewrite_gbtoidx__lineitem_lineitem_lshipdate_idx__ '
'          TableScan'
'            alias: thisyear:ql_rewrite_gbtoidx__lineitem_lineitem_lshipdate_idx__'
'            Filter Operator'
'              predicate:'
'                  expr: (year(l_shipdate) = 1998)'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: l_shipdate'
'                      type: string'
'                      expr: _count_of_l_shipdate'
'                      type: bigint'
'                outputColumnNames: l_shipdate, _count_of_l_shipdate'
'                Group By Operator'
'                  aggregations:'
'                        expr: sum(_count_of_l_shipdate)'
'                  bucketGroup: false'
'                  keys:'
'                        expr: year(l_shipdate)'
'                        type: int'
'                        expr: month(l_shipdate)'
'                        type: int'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1, _col2'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    sort order: ++'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    tag: -1'
'                    value expressions:'
'                          expr: _col2'
'                          type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1, _col2'
'          Select Operator'
'            expressions:'
'                  expr: _col1'
'                  type: int'
'                  expr: _col2'
'                  type: bigint'
'            outputColumnNames: _col1, _col2'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
213 rows selected 
>>>  
>>>  explain  select l_shipdate, cnt 
from (select l_shipdate, count(l_shipdate) as cnt from lineitem group by l_shipdate 
union all 
select l_shipdate, l_orderkey as cnt 
from lineitem) dummy;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_UNION (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME lineitem))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL l_shipdate)) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL l_shipdate)) cnt)) (TOK_GROUPBY (TOK_TABLE_OR_COL l_shipdate)))) (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME lineitem))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL l_shipdate)) (TOK_SELEXPR (TOK_TABLE_OR_COL l_orderkey) cnt))))) dummy)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL l_shipdate)) (TOK_SELEXPR (TOK_TABLE_OR_COL cnt)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-3 is a root stage'
'  Stage-2 depends on stages: Stage-3'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-3'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        null-subquery1:ql_rewrite_gbtoidx__lineitem_lineitem_lshipdate_idx__ '
'          TableScan'
'            alias: null-subquery1:ql_rewrite_gbtoidx__lineitem_lineitem_lshipdate_idx__'
'            Select Operator'
'              expressions:'
'                    expr: l_shipdate'
'                    type: string'
'                    expr: _count_of_l_shipdate'
'                    type: bigint'
'              outputColumnNames: l_shipdate, _count_of_l_shipdate'
'              Group By Operator'
'                aggregations:'
'                      expr: sum(_count_of_l_shipdate)'
'                bucketGroup: true'
'                keys:'
'                      expr: l_shipdate'
'                      type: string'
'                mode: hash'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: string'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: string'
'                  tag: -1'
'                  value expressions:'
'                        expr: _col1'
'                        type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: string'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: string'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
''
'  Stage: Stage-2'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        file:!!{hive.exec.scratchdir}!! '
'          TableScan'
'            Union'
'              Select Operator'
'                expressions:'
'                      expr: _col0'
'                      type: string'
'                      expr: _col1'
'                      type: bigint'
'                outputColumnNames: _col0, _col1'
'                File Output Operator'
'                  compressed: false'
'                  GlobalTableId: 0'
'                  table:'
'                      input format: org.apache.hadoop.mapred.TextInputFormat'
'                      output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
'        null-subquery2:dummy-subquery2:lineitem '
'          TableScan'
'            alias: lineitem'
'            Select Operator'
'              expressions:'
'                    expr: l_shipdate'
'                    type: string'
'                    expr: l_orderkey'
'                    type: int'
'              outputColumnNames: _col0, _col1'
'              Union'
'                Select Operator'
'                  expressions:'
'                        expr: _col0'
'                        type: string'
'                        expr: _col1'
'                        type: bigint'
'                  outputColumnNames: _col0, _col1'
'                  File Output Operator'
'                    compressed: false'
'                    GlobalTableId: 0'
'                    table:'
'                        input format: org.apache.hadoop.mapred.TextInputFormat'
'                        output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
116 rows selected 
>>>  
>>>  CREATE TABLE tbl(key int, value int);
No rows affected 
>>>  CREATE INDEX tbl_key_idx ON TABLE tbl(key) AS 'org.apache.hadoop.hive.ql.index.AggregateIndexHandler' WITH DEFERRED REBUILD IDXPROPERTIES("AGGREGATES"="count(key)");
No rows affected 
>>>  ALTER INDEX tbl_key_idx ON tbl REBUILD;
No rows affected 
>>>  
>>>  EXPLAIN select key, count(key) from tbl where key = 1 group by key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL key)))) (TOK_WHERE (= (TOK_TABLE_OR_COL key) 1)) (TOK_GROUPBY (TOK_TABLE_OR_COL key))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        ql_rewrite_gbtoidx__tbl_tbl_key_idx__ '
'          TableScan'
'            alias: ql_rewrite_gbtoidx__tbl_tbl_key_idx__'
'            Filter Operator'
'              predicate:'
'                  expr: (key = 1)'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: int'
'                      expr: _count_of_key'
'                      type: bigint'
'                outputColumnNames: key, _count_of_key'
'                Group By Operator'
'                  aggregations:'
'                        expr: sum(_count_of_key)'
'                  bucketGroup: true'
'                  keys:'
'                        expr: key'
'                        type: int'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: int'
'                    sort order: +'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: int'
'                    tag: -1'
'                    value expressions:'
'                          expr: _col1'
'                          type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
75 rows selected 
>>>  EXPLAIN select key, count(key) from tbl group by key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL key)))) (TOK_GROUPBY (TOK_TABLE_OR_COL key))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        ql_rewrite_gbtoidx__tbl_tbl_key_idx__ '
'          TableScan'
'            alias: ql_rewrite_gbtoidx__tbl_tbl_key_idx__'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: int'
'                    expr: _count_of_key'
'                    type: bigint'
'              outputColumnNames: key, _count_of_key'
'              Group By Operator'
'                aggregations:'
'                      expr: sum(_count_of_key)'
'                bucketGroup: true'
'                keys:'
'                      expr: key'
'                      type: int'
'                mode: hash'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                  tag: -1'
'                  value expressions:'
'                        expr: _col1'
'                        type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
71 rows selected 
>>>  
>>>  EXPLAIN select count(1) from tbl;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION count 1)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Select Operator'
'              Group By Operator'
'                aggregations:'
'                      expr: count(1)'
'                bucketGroup: false'
'                mode: hash'
'                outputColumnNames: _col0'
'                Reduce Output Operator'
'                  sort order: '
'                  tag: -1'
'                  value expressions:'
'                        expr: _col0'
'                        type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: count(VALUE._col0)'
'          bucketGroup: false'
'          mode: mergepartial'
'          outputColumnNames: _col0'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: bigint'
'            outputColumnNames: _col0'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
50 rows selected 
>>>  EXPLAIN select count(key) from tbl;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL key))))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: int'
'              outputColumnNames: key'
'              Group By Operator'
'                aggregations:'
'                      expr: count(key)'
'                bucketGroup: false'
'                mode: hash'
'                outputColumnNames: _col0'
'                Reduce Output Operator'
'                  sort order: '
'                  tag: -1'
'                  value expressions:'
'                        expr: _col0'
'                        type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: count(VALUE._col0)'
'          bucketGroup: false'
'          mode: mergepartial'
'          outputColumnNames: _col0'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: bigint'
'            outputColumnNames: _col0'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
55 rows selected 
>>>  
>>>  EXPLAIN select key FROM tbl GROUP BY key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key))) (TOK_GROUPBY (TOK_TABLE_OR_COL key))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: int'
'              outputColumnNames: key'
'              Group By Operator'
'                bucketGroup: false'
'                keys:'
'                      expr: key'
'                      type: int'
'                mode: hash'
'                outputColumnNames: _col0'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                  tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'            outputColumnNames: _col0'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
60 rows selected 
>>>  EXPLAIN select key FROM tbl GROUP BY value, key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key))) (TOK_GROUPBY (TOK_TABLE_OR_COL value) (TOK_TABLE_OR_COL key))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Select Operator'
'              expressions:'
'                    expr: value'
'                    type: int'
'                    expr: key'
'                    type: int'
'              outputColumnNames: value, key'
'              Group By Operator'
'                bucketGroup: false'
'                keys:'
'                      expr: value'
'                      type: int'
'                      expr: key'
'                      type: int'
'                mode: hash'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: int'
'                  sort order: ++'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: int'
'                  tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col1'
'                  type: int'
'            outputColumnNames: _col0'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
70 rows selected 
>>>  EXPLAIN select key FROM tbl WHERE key = 3 GROUP BY key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key))) (TOK_WHERE (= (TOK_TABLE_OR_COL key) 3)) (TOK_GROUPBY (TOK_TABLE_OR_COL key))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Filter Operator'
'              predicate:'
'                  expr: (key = 3)'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: int'
'                outputColumnNames: key'
'                Group By Operator'
'                  bucketGroup: false'
'                  keys:'
'                        expr: key'
'                        type: int'
'                  mode: hash'
'                  outputColumnNames: _col0'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: int'
'                    sort order: +'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: int'
'                    tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'            outputColumnNames: _col0'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
64 rows selected 
>>>  EXPLAIN select key FROM tbl WHERE value = 2 GROUP BY key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key))) (TOK_WHERE (= (TOK_TABLE_OR_COL value) 2)) (TOK_GROUPBY (TOK_TABLE_OR_COL key))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Filter Operator'
'              predicate:'
'                  expr: (value = 2)'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: int'
'                outputColumnNames: key'
'                Group By Operator'
'                  bucketGroup: false'
'                  keys:'
'                        expr: key'
'                        type: int'
'                  mode: hash'
'                  outputColumnNames: _col0'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: int'
'                    sort order: +'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: int'
'                    tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'            outputColumnNames: _col0'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
64 rows selected 
>>>  EXPLAIN select key FROM tbl GROUP BY key, substr(key,2,3);
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key))) (TOK_GROUPBY (TOK_TABLE_OR_COL key) (TOK_FUNCTION substr (TOK_TABLE_OR_COL key) 2 3))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: int'
'              outputColumnNames: key'
'              Group By Operator'
'                bucketGroup: false'
'                keys:'
'                      expr: key'
'                      type: int'
'                      expr: substr(key, 2, 3)'
'                      type: string'
'                mode: hash'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: string'
'                  sort order: ++'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: string'
'                  tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: string'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'            outputColumnNames: _col0'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
68 rows selected 
>>>  
>>>  EXPLAIN select key, value FROM tbl GROUP BY value, key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_TABLE_OR_COL value))) (TOK_GROUPBY (TOK_TABLE_OR_COL value) (TOK_TABLE_OR_COL key))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Select Operator'
'              expressions:'
'                    expr: value'
'                    type: int'
'                    expr: key'
'                    type: int'
'              outputColumnNames: value, key'
'              Group By Operator'
'                bucketGroup: false'
'                keys:'
'                      expr: value'
'                      type: int'
'                      expr: key'
'                      type: int'
'                mode: hash'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: int'
'                  sort order: ++'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: int'
'                  tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col1'
'                  type: int'
'                  expr: _col0'
'                  type: int'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
72 rows selected 
>>>  EXPLAIN select key, value FROM tbl WHERE value = 1 GROUP BY key, value;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_TABLE_OR_COL value))) (TOK_WHERE (= (TOK_TABLE_OR_COL value) 1)) (TOK_GROUPBY (TOK_TABLE_OR_COL key) (TOK_TABLE_OR_COL value))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Filter Operator'
'              predicate:'
'                  expr: (value = 1)'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'                outputColumnNames: key, value'
'                Group By Operator'
'                  bucketGroup: false'
'                  keys:'
'                        expr: key'
'                        type: int'
'                        expr: value'
'                        type: int'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    sort order: ++'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: int'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
76 rows selected 
>>>  
>>>  EXPLAIN select DISTINCT key FROM tbl;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECTDI (TOK_SELEXPR (TOK_TABLE_OR_COL key)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: int'
'              outputColumnNames: key'
'              Group By Operator'
'                bucketGroup: false'
'                keys:'
'                      expr: key'
'                      type: int'
'                mode: hash'
'                outputColumnNames: _col0'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                  tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'            outputColumnNames: _col0'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
60 rows selected 
>>>  EXPLAIN select DISTINCT key FROM tbl;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECTDI (TOK_SELEXPR (TOK_TABLE_OR_COL key)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: int'
'              outputColumnNames: key'
'              Group By Operator'
'                bucketGroup: false'
'                keys:'
'                      expr: key'
'                      type: int'
'                mode: hash'
'                outputColumnNames: _col0'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                  tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'            outputColumnNames: _col0'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
60 rows selected 
>>>  EXPLAIN select DISTINCT key FROM tbl;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECTDI (TOK_SELEXPR (TOK_TABLE_OR_COL key)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: int'
'              outputColumnNames: key'
'              Group By Operator'
'                bucketGroup: false'
'                keys:'
'                      expr: key'
'                      type: int'
'                mode: hash'
'                outputColumnNames: _col0'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                  tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'            outputColumnNames: _col0'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
60 rows selected 
>>>  EXPLAIN select DISTINCT key, value FROM tbl;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECTDI (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_TABLE_OR_COL value)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: int'
'                    expr: value'
'                    type: int'
'              outputColumnNames: key, value'
'              Group By Operator'
'                bucketGroup: false'
'                keys:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'                mode: hash'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: int'
'                  sort order: ++'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: int'
'                  tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: int'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
72 rows selected 
>>>  EXPLAIN select DISTINCT key, value FROM tbl WHERE value = 2;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECTDI (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_TABLE_OR_COL value))) (TOK_WHERE (= (TOK_TABLE_OR_COL value) 2))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Filter Operator'
'              predicate:'
'                  expr: (value = 2)'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'                outputColumnNames: key, value'
'                Group By Operator'
'                  bucketGroup: false'
'                  keys:'
'                        expr: key'
'                        type: int'
'                        expr: value'
'                        type: int'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    sort order: ++'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: int'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
76 rows selected 
>>>  EXPLAIN select DISTINCT key, value FROM tbl WHERE value = 2 AND key = 3;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECTDI (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_TABLE_OR_COL value))) (TOK_WHERE (AND (= (TOK_TABLE_OR_COL value) 2) (= (TOK_TABLE_OR_COL key) 3)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Filter Operator'
'              predicate:'
'                  expr: ((value = 2) and (key = 3))'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'                outputColumnNames: key, value'
'                Group By Operator'
'                  bucketGroup: false'
'                  keys:'
'                        expr: key'
'                        type: int'
'                        expr: value'
'                        type: int'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    sort order: ++'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: int'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
76 rows selected 
>>>  EXPLAIN select DISTINCT key, value FROM tbl WHERE value = key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECTDI (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_TABLE_OR_COL value))) (TOK_WHERE (= (TOK_TABLE_OR_COL value) (TOK_TABLE_OR_COL key)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Filter Operator'
'              predicate:'
'                  expr: (value = key)'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'                outputColumnNames: key, value'
'                Group By Operator'
'                  bucketGroup: false'
'                  keys:'
'                        expr: key'
'                        type: int'
'                        expr: value'
'                        type: int'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    sort order: ++'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: int'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
76 rows selected 
>>>  EXPLAIN select DISTINCT key, substr(value,2,3) FROM tbl WHERE value = key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECTDI (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_FUNCTION substr (TOK_TABLE_OR_COL value) 2 3))) (TOK_WHERE (= (TOK_TABLE_OR_COL value) (TOK_TABLE_OR_COL key)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Filter Operator'
'              predicate:'
'                  expr: (value = key)'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'                outputColumnNames: key, value'
'                Group By Operator'
'                  bucketGroup: false'
'                  keys:'
'                        expr: key'
'                        type: int'
'                        expr: substr(value, 2, 3)'
'                        type: string'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: string'
'                    sort order: ++'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: string'
'                    tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: string'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: string'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
76 rows selected 
>>>  EXPLAIN select DISTINCT key, substr(value,2,3) FROM tbl;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECTDI (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_FUNCTION substr (TOK_TABLE_OR_COL value) 2 3)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: int'
'                    expr: value'
'                    type: int'
'              outputColumnNames: key, value'
'              Group By Operator'
'                bucketGroup: false'
'                keys:'
'                      expr: key'
'                      type: int'
'                      expr: substr(value, 2, 3)'
'                      type: string'
'                mode: hash'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: string'
'                  sort order: ++'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                        expr: _col1'
'                        type: string'
'                  tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: string'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: string'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
72 rows selected 
>>>  
>>>  EXPLAIN select * FROM (select DISTINCT key, value FROM tbl) v1 WHERE v1.value = 2;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECTDI (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_TABLE_OR_COL value))))) v1)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF)) (TOK_WHERE (= (. (TOK_TABLE_OR_COL v1) value) 2))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        v1:tbl '
'          TableScan'
'            alias: tbl'
'            Filter Operator'
'              predicate:'
'                  expr: (value = 2)'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: int'
'                      expr: value'
'                      type: int'
'                outputColumnNames: key, value'
'                Group By Operator'
'                  bucketGroup: false'
'                  keys:'
'                        expr: key'
'                        type: int'
'                        expr: value'
'                        type: int'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    sort order: ++'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: int'
'                          expr: _col1'
'                          type: int'
'                    tag: -1'
'      Reduce Operator Tree:'
'        Group By Operator'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'                expr: KEY._col1'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: int'
'            outputColumnNames: _col0, _col1'
'            Select Operator'
'              expressions:'
'                    expr: _col0'
'                    type: int'
'                    expr: _col1'
'                    type: int'
'              outputColumnNames: _col0, _col1'
'              File Output Operator'
'                compressed: false'
'                GlobalTableId: 0'
'                table:'
'                    input format: org.apache.hadoop.mapred.TextInputFormat'
'                    output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
83 rows selected 
>>>  
>>>  DROP TABLE tbl;
No rows affected 
>>>  
>>>  CREATE TABLE tblpart (key int, value string) PARTITIONED BY (ds string, hr int);
No rows affected 
>>>  INSERT OVERWRITE TABLE tblpart PARTITION (ds='2008-04-08', hr=11) SELECT key, value FROM srcpart WHERE ds = '2008-04-08' AND hr = 11;
'_col0','_col1'
No rows selected 
>>>  INSERT OVERWRITE TABLE tblpart PARTITION (ds='2008-04-08', hr=12) SELECT key, value FROM srcpart WHERE ds = '2008-04-08' AND hr = 12;
'_col0','_col1'
No rows selected 
>>>  INSERT OVERWRITE TABLE tblpart PARTITION (ds='2008-04-09', hr=11) SELECT key, value FROM srcpart WHERE ds = '2008-04-09' AND hr = 11;
'_col0','_col1'
No rows selected 
>>>  INSERT OVERWRITE TABLE tblpart PARTITION (ds='2008-04-09', hr=12) SELECT key, value FROM srcpart WHERE ds = '2008-04-09' AND hr = 12;
'_col0','_col1'
No rows selected 
>>>  
>>>  CREATE INDEX tbl_part_index ON TABLE tblpart(key) AS 'org.apache.hadoop.hive.ql.index.AggregateIndexHandler' WITH DEFERRED REBUILD IDXPROPERTIES("AGGREGATES"="count(key)");
No rows affected 
>>>  
>>>  ALTER INDEX tbl_part_index ON tblpart PARTITION (ds='2008-04-08', hr=11) REBUILD;
No rows affected 
>>>  EXPLAIN SELECT key, count(key) FROM tblpart WHERE ds='2008-04-09' AND hr=12 AND key < 10 GROUP BY key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tblpart))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL key)))) (TOK_WHERE (AND (AND (= (TOK_TABLE_OR_COL ds) '2008-04-09') (= (TOK_TABLE_OR_COL hr) 12)) (< (TOK_TABLE_OR_COL key) 10))) (TOK_GROUPBY (TOK_TABLE_OR_COL key))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tblpart '
'          TableScan'
'            alias: tblpart'
'            Filter Operator'
'              predicate:'
'                  expr: (key < 10)'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: int'
'                outputColumnNames: key'
'                Group By Operator'
'                  aggregations:'
'                        expr: count(key)'
'                  bucketGroup: false'
'                  keys:'
'                        expr: key'
'                        type: int'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: int'
'                    sort order: +'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: int'
'                    tag: -1'
'                    value expressions:'
'                          expr: _col1'
'                          type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: count(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
73 rows selected 
>>>  
>>>  ALTER INDEX tbl_part_index ON tblpart PARTITION (ds='2008-04-08', hr=12) REBUILD;
No rows affected 
>>>  ALTER INDEX tbl_part_index ON tblpart PARTITION (ds='2008-04-09', hr=11) REBUILD;
No rows affected 
>>>  ALTER INDEX tbl_part_index ON tblpart PARTITION (ds='2008-04-09', hr=12) REBUILD;
No rows affected 
>>>  EXPLAIN SELECT key, count(key) FROM tblpart WHERE ds='2008-04-09' AND hr=12 AND key < 10 GROUP BY key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tblpart))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL key)))) (TOK_WHERE (AND (AND (= (TOK_TABLE_OR_COL ds) '2008-04-09') (= (TOK_TABLE_OR_COL hr) 12)) (< (TOK_TABLE_OR_COL key) 10))) (TOK_GROUPBY (TOK_TABLE_OR_COL key))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        ql_rewrite_gbtoidx__tblpart_tbl_part_index__ '
'          TableScan'
'            alias: ql_rewrite_gbtoidx__tblpart_tbl_part_index__'
'            Filter Operator'
'              predicate:'
'                  expr: (key < 10)'
'                  type: boolean'
'              Select Operator'
'                expressions:'
'                      expr: key'
'                      type: int'
'                      expr: _count_of_key'
'                      type: bigint'
'                outputColumnNames: key, _count_of_key'
'                Group By Operator'
'                  aggregations:'
'                        expr: sum(_count_of_key)'
'                  bucketGroup: false'
'                  keys:'
'                        expr: key'
'                        type: int'
'                  mode: hash'
'                  outputColumnNames: _col0, _col1'
'                  Reduce Output Operator'
'                    key expressions:'
'                          expr: _col0'
'                          type: int'
'                    sort order: +'
'                    Map-reduce partition columns:'
'                          expr: _col0'
'                          type: int'
'                    tag: -1'
'                    value expressions:'
'                          expr: _col1'
'                          type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.TextInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
75 rows selected 
>>>  
>>>  DROP INDEX tbl_part_index on tblpart;
No rows affected 
>>>  DROP TABLE tblpart;
No rows affected 
>>>  
>>>  CREATE TABLE tbl(key int, value int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|';
No rows affected 
>>>  LOAD DATA LOCAL INPATH '../data/files/tbl.txt' OVERWRITE INTO TABLE tbl;
No rows affected 
>>>  
>>>  CREATE INDEX tbl_key_idx ON TABLE tbl(key) AS 'org.apache.hadoop.hive.ql.index.AggregateIndexHandler' WITH DEFERRED REBUILD IDXPROPERTIES("AGGREGATES"="count(key)");
No rows affected 
>>>  ALTER INDEX tbl_key_idx ON tbl REBUILD;
No rows affected 
>>>  
>>>  set hive.optimize.index.groupby=false;
No rows affected 
>>>  explain select key, count(key) from tbl group by key order by key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL key)))) (TOK_GROUPBY (TOK_TABLE_OR_COL key)) (TOK_ORDERBY (TOK_TABSORTCOLNAMEASC (TOK_TABLE_OR_COL key)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-2 depends on stages: Stage-1'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        tbl '
'          TableScan'
'            alias: tbl'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: int'
'              outputColumnNames: key'
'              Group By Operator'
'                aggregations:'
'                      expr: count(key)'
'                bucketGroup: false'
'                keys:'
'                      expr: key'
'                      type: int'
'                mode: hash'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                  tag: -1'
'                  value expressions:'
'                        expr: _col1'
'                        type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: count(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
''
'  Stage: Stage-2'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        file:!!{hive.exec.scratchdir}!! '
'            Reduce Output Operator'
'              key expressions:'
'                    expr: _col0'
'                    type: int'
'              sort order: +'
'              tag: -1'
'              value expressions:'
'                    expr: _col0'
'                    type: int'
'                    expr: _col1'
'                    type: bigint'
'      Reduce Operator Tree:'
'        Extract'
'          File Output Operator'
'            compressed: false'
'            GlobalTableId: 0'
'            table:'
'                input format: org.apache.hadoop.mapred.TextInputFormat'
'                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
94 rows selected 
>>>  select key, count(key) from tbl group by key order by key;
'key','_c1'
'1','1'
'2','3'
'3','2'
'4','2'
'6','1'
'7','1'
6 rows selected 
>>>  set hive.optimize.index.groupby=true;
No rows affected 
>>>  explain select key, count(key) from tbl group by key order by key;
'Explain'
'ABSTRACT SYNTAX TREE:'
'  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME tbl))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL key)) (TOK_SELEXPR (TOK_FUNCTION count (TOK_TABLE_OR_COL key)))) (TOK_GROUPBY (TOK_TABLE_OR_COL key)) (TOK_ORDERBY (TOK_TABSORTCOLNAMEASC (TOK_TABLE_OR_COL key)))))'
''
'STAGE DEPENDENCIES:'
'  Stage-1 is a root stage'
'  Stage-2 depends on stages: Stage-1'
'  Stage-0 is a root stage'
''
'STAGE PLANS:'
'  Stage: Stage-1'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        ql_rewrite_gbtoidx__tbl_tbl_key_idx__ '
'          TableScan'
'            alias: ql_rewrite_gbtoidx__tbl_tbl_key_idx__'
'            Select Operator'
'              expressions:'
'                    expr: key'
'                    type: int'
'                    expr: _count_of_key'
'                    type: bigint'
'              outputColumnNames: key, _count_of_key'
'              Group By Operator'
'                aggregations:'
'                      expr: sum(_count_of_key)'
'                bucketGroup: true'
'                keys:'
'                      expr: key'
'                      type: int'
'                mode: hash'
'                outputColumnNames: _col0, _col1'
'                Reduce Output Operator'
'                  key expressions:'
'                        expr: _col0'
'                        type: int'
'                  sort order: +'
'                  Map-reduce partition columns:'
'                        expr: _col0'
'                        type: int'
'                  tag: -1'
'                  value expressions:'
'                        expr: _col1'
'                        type: bigint'
'      Reduce Operator Tree:'
'        Group By Operator'
'          aggregations:'
'                expr: sum(VALUE._col0)'
'          bucketGroup: false'
'          keys:'
'                expr: KEY._col0'
'                type: int'
'          mode: mergepartial'
'          outputColumnNames: _col0, _col1'
'          Select Operator'
'            expressions:'
'                  expr: _col0'
'                  type: int'
'                  expr: _col1'
'                  type: bigint'
'            outputColumnNames: _col0, _col1'
'            File Output Operator'
'              compressed: false'
'              GlobalTableId: 0'
'              table:'
'                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat'
'                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
''
'  Stage: Stage-2'
'    Map Reduce'
'      Alias -> Map Operator Tree:'
'        file:!!{hive.exec.scratchdir}!! '
'            Reduce Output Operator'
'              key expressions:'
'                    expr: _col0'
'                    type: int'
'              sort order: +'
'              tag: -1'
'              value expressions:'
'                    expr: _col0'
'                    type: int'
'                    expr: _col1'
'                    type: bigint'
'      Reduce Operator Tree:'
'        Extract'
'          File Output Operator'
'            compressed: false'
'            GlobalTableId: 0'
'            table:'
'                input format: org.apache.hadoop.mapred.TextInputFormat'
'                output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
''
'  Stage: Stage-0'
'    Fetch Operator'
'      limit: -1'
''
''
96 rows selected 
>>>  select key, count(key) from tbl group by key order by key;
'key','_c1'
'1','1'
'2','3'
'3','2'
'4','2'
'6','1'
'7','1'
6 rows selected 
>>>  DROP TABLE tbl;
No rows affected 
>>>  !record