Hive Indexes vs ORC Indexes

TL;DR - Doing “CREATE INDEX” produces slower queries on ORC tables – because you turn a distributed process (of looking up row-indexes) into a single threaded process (during query planning).

Then how do they speed up queries with conditions on other columns.

ORC contains secondary row-indexes. They are always generated for all ORC files, unless you explicitly turn them off.

You can turn off the fast scans in ORC, by setting "orc.create.index"=“false” in the TBLPROPERTIES of a table.

In the latest unreleased builds, the column row-indexes are either min-max range or BLOOM filters.

The effectiveness of the row-indexes reduces the more number of distinct values you end up with in a single index stride (10,000 rows by default).

I usually insert my unpartitioned tables with “SORT BY” clauses, to produce a UNIFORM distribution of data across splits (remember, uniform split distribution beats skews produced by CLUSTER BY or table bucketing – that is very different from MPP).