Saving all output to "!!{outputDirectory}!!/index_auto_unused.q.raw". Enter "record" with no arguments to stop it. >>> !run !!{qFileDirectory}!!/index_auto_unused.q >>> -- test cases where the index should not be used automatically >>> >>> CREATE INDEX src_index ON TABLE src(key) as 'COMPACT' WITH DEFERRED REBUILD; No rows affected >>> ALTER INDEX src_index ON src REBUILD; No rows affected >>> >>> SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; No rows affected >>> SET hive.optimize.index.filter=true; No rows affected >>> SET hive.optimize.index.filter.compact.minsize=5368709120; No rows affected >>> SET hive.optimize.index.filter.compact.maxsize=-1; No rows affected >>> >>> -- min size too large (src is less than 5G) >>> EXPLAIN SELECT * FROM src WHERE key > 80 AND key < 100 ORDER BY key; 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF)) (TOK_WHERE (AND (> (TOK_TABLE_OR_COL key) 80) (< (TOK_TABLE_OR_COL key) 100))) (TOK_ORDERBY (TOK_TABSORTCOLNAMEASC (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:' ' src ' ' TableScan' ' alias: src' ' filterExpr:' ' expr: ((key > 80.0) and (key < 100.0))' ' type: boolean' ' Filter Operator' ' predicate:' ' expr: ((key > 80.0) and (key < 100.0))' ' type: boolean' ' Select Operator' ' expressions:' ' expr: key' ' type: string' ' expr: value' ' type: string' ' outputColumnNames: _col0, _col1' ' Reduce Output Operator' ' key expressions:' ' expr: _col0' ' type: string' ' sort order: +' ' tag: -1' ' value expressions:' ' expr: _col0' ' type: string' ' expr: _col1' ' type: string' ' 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' '' '' 53 rows selected >>> SELECT * FROM src WHERE key > 80 AND key < 100 ORDER BY key; 'key','value' '82','val_82' '83','val_83' '83','val_83' '84','val_84' '84','val_84' '85','val_85' '86','val_86' '87','val_87' '90','val_90' '90','val_90' '90','val_90' '92','val_92' '95','val_95' '95','val_95' '96','val_96' '97','val_97' '97','val_97' '98','val_98' '98','val_98' 19 rows selected >>> >>> SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; No rows affected >>> SET hive.optimize.index.filter=true; No rows affected >>> SET hive.optimize.index.filter.compact.minsize=0; No rows affected >>> SET hive.optimize.index.filter.compact.maxsize=1; No rows affected >>> >>> -- max size too small >>> EXPLAIN SELECT * FROM src WHERE key > 80 AND key < 100 ORDER BY key; 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF)) (TOK_WHERE (AND (> (TOK_TABLE_OR_COL key) 80) (< (TOK_TABLE_OR_COL key) 100))) (TOK_ORDERBY (TOK_TABSORTCOLNAMEASC (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:' ' src ' ' TableScan' ' alias: src' ' filterExpr:' ' expr: ((key > 80.0) and (key < 100.0))' ' type: boolean' ' Filter Operator' ' predicate:' ' expr: ((key > 80.0) and (key < 100.0))' ' type: boolean' ' Select Operator' ' expressions:' ' expr: key' ' type: string' ' expr: value' ' type: string' ' outputColumnNames: _col0, _col1' ' Reduce Output Operator' ' key expressions:' ' expr: _col0' ' type: string' ' sort order: +' ' tag: -1' ' value expressions:' ' expr: _col0' ' type: string' ' expr: _col1' ' type: string' ' 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' '' '' 53 rows selected >>> SELECT * FROM src WHERE key > 80 AND key < 100 ORDER BY key; 'key','value' '82','val_82' '83','val_83' '83','val_83' '84','val_84' '84','val_84' '85','val_85' '86','val_86' '87','val_87' '90','val_90' '90','val_90' '90','val_90' '92','val_92' '95','val_95' '95','val_95' '96','val_96' '97','val_97' '97','val_97' '98','val_98' '98','val_98' 19 rows selected >>> >>> SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; No rows affected >>> SET hive.optimize.index.filter=true; No rows affected >>> SET hive.optimize.index.filter.compact.minsize=0; No rows affected >>> SET hive.optimize.index.filter.compact.maxsize=-1; No rows affected >>> >>> -- OR predicate not supported by compact indexes >>> EXPLAIN SELECT * FROM src WHERE key < 10 OR key > 480 ORDER BY key; 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF)) (TOK_WHERE (OR (< (TOK_TABLE_OR_COL key) 10) (> (TOK_TABLE_OR_COL key) 480))) (TOK_ORDERBY (TOK_TABSORTCOLNAMEASC (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:' ' src ' ' TableScan' ' alias: src' ' filterExpr:' ' expr: ((key < 10.0) or (key > 480.0))' ' type: boolean' ' Filter Operator' ' predicate:' ' expr: ((key < 10.0) or (key > 480.0))' ' type: boolean' ' Select Operator' ' expressions:' ' expr: key' ' type: string' ' expr: value' ' type: string' ' outputColumnNames: _col0, _col1' ' Reduce Output Operator' ' key expressions:' ' expr: _col0' ' type: string' ' sort order: +' ' tag: -1' ' value expressions:' ' expr: _col0' ' type: string' ' expr: _col1' ' type: string' ' 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' '' '' 53 rows selected >>> SELECT * FROM src WHERE key < 10 OR key > 480 ORDER BY key; 'key','value' '0','val_0' '0','val_0' '0','val_0' '2','val_2' '4','val_4' '481','val_481' '482','val_482' '483','val_483' '484','val_484' '485','val_485' '487','val_487' '489','val_489' '489','val_489' '489','val_489' '489','val_489' '490','val_490' '491','val_491' '492','val_492' '492','val_492' '493','val_493' '494','val_494' '495','val_495' '496','val_496' '497','val_497' '498','val_498' '498','val_498' '498','val_498' '5','val_5' '5','val_5' '5','val_5' '8','val_8' '9','val_9' 32 rows selected >>> >>> SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; No rows affected >>> SET hive.optimize.index.filter=true; No rows affected >>> SET hive.optimize.index.filter.compact.minsize=0; No rows affected >>> SET hive.optimize.index.filter.compact.maxsize=-1; No rows affected >>> >>> -- columns are not covered by indexes >>> DROP INDEX src_index on src; No rows affected >>> CREATE INDEX src_val_index ON TABLE src(value) as 'COMPACT' WITH DEFERRED REBUILD; No rows affected >>> ALTER INDEX src_val_index ON src REBUILD; No rows affected >>> >>> EXPLAIN SELECT * FROM src WHERE key > 80 AND key < 100 ORDER BY key; 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF)) (TOK_WHERE (AND (> (TOK_TABLE_OR_COL key) 80) (< (TOK_TABLE_OR_COL key) 100))) (TOK_ORDERBY (TOK_TABSORTCOLNAMEASC (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:' ' src ' ' TableScan' ' alias: src' ' filterExpr:' ' expr: ((key > 80.0) and (key < 100.0))' ' type: boolean' ' Filter Operator' ' predicate:' ' expr: ((key > 80.0) and (key < 100.0))' ' type: boolean' ' Select Operator' ' expressions:' ' expr: key' ' type: string' ' expr: value' ' type: string' ' outputColumnNames: _col0, _col1' ' Reduce Output Operator' ' key expressions:' ' expr: _col0' ' type: string' ' sort order: +' ' tag: -1' ' value expressions:' ' expr: _col0' ' type: string' ' expr: _col1' ' type: string' ' 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' '' '' 53 rows selected >>> SELECT * FROM src WHERE key > 80 AND key < 100 ORDER BY key; 'key','value' '82','val_82' '83','val_83' '83','val_83' '84','val_84' '84','val_84' '85','val_85' '86','val_86' '87','val_87' '90','val_90' '90','val_90' '90','val_90' '92','val_92' '95','val_95' '95','val_95' '96','val_96' '97','val_97' '97','val_97' '98','val_98' '98','val_98' 19 rows selected >>> >>> DROP INDEX src_val_index on src; No rows affected >>> >>> SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; No rows affected >>> SET hive.optimize.index.filter=true; No rows affected >>> SET hive.optimize.index.filter.compact.minsize=0; No rows affected >>> SET hive.optimize.index.filter.compact.maxsize=-1; No rows affected >>> >>> -- required partitions have not been built yet >>> CREATE INDEX src_part_index ON TABLE srcpart(key) as 'COMPACT' WITH DEFERRED REBUILD; No rows affected >>> ALTER INDEX src_part_index ON srcpart PARTITION (ds='2008-04-08', hr=11) REBUILD; No rows affected >>> >>> EXPLAIN SELECT * FROM srcpart WHERE ds='2008-04-09' AND hr=12 AND key < 10 ORDER BY key; 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME srcpart))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF)) (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_ORDERBY (TOK_TABSORTCOLNAMEASC (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:' ' srcpart ' ' TableScan' ' alias: srcpart' ' filterExpr:' ' expr: (((ds = '2008-04-09') and (hr = 12.0)) and (key < 10.0))' ' type: boolean' ' Filter Operator' ' predicate:' ' expr: (key < 10.0)' ' type: boolean' ' Select Operator' ' expressions:' ' expr: key' ' type: string' ' expr: value' ' type: string' ' expr: ds' ' type: string' ' expr: hr' ' type: string' ' outputColumnNames: _col0, _col1, _col2, _col3' ' Reduce Output Operator' ' key expressions:' ' expr: _col0' ' type: string' ' sort order: +' ' tag: -1' ' value expressions:' ' expr: _col0' ' type: string' ' expr: _col1' ' type: string' ' expr: _col2' ' type: string' ' expr: _col3' ' type: string' ' 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' '' '' 61 rows selected >>> SELECT * FROM srcpart WHERE ds='2008-04-09' AND hr=12 AND key < 10 ORDER BY key; 'key','value','ds','hr' '0','val_0','2008-04-09','12' '0','val_0','2008-04-09','12' '0','val_0','2008-04-09','12' '2','val_2','2008-04-09','12' '4','val_4','2008-04-09','12' '5','val_5','2008-04-09','12' '5','val_5','2008-04-09','12' '5','val_5','2008-04-09','12' '8','val_8','2008-04-09','12' '9','val_9','2008-04-09','12' 10 rows selected >>> >>> DROP INDEX src_part_index on srcpart; No rows affected >>> !record