I am trying this on HDP 2.5.3: create table if not exists test_table (mykey varchar primary key, col1 varchar, col2
archer);
create local index if not exists idx2 on test_table (col2); upsert into test_table (mykey, col1, col2) values('k1', 'v1-1', 'v1-2'); upsert into test_table (mykey, col1, col2) values('k2', 'v2-1', 'v2-2'); upsert into test_table (mykey, col1, col2) values('a2', 'v2-1', 'v3-2');
upsert into test_table (mykey, col1, col2) values('bv2', 'v2-1', 'v4-2');
upsert into test_table (mykey, col1, col2) values('c2', 'v2-1', 'v5-2');
upsert into test_table (mykey, col1, col2) values('x2', 'v2-1', 'v6-2'); upsert into test_table (mykey, col1, col2) values('y2', 'v2-1', 'v7-2'); select using the index
explain select col2 from test_table where col2 = 'v1-2'; +----------------------------------------------------------------------------------+
| PLAN |
+----------------------------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER TEST_TABLE [1,'v1-2'] | | SERVER FILTER BY FIRST KEY ONLY | +----------------------------------------------------------------------------------+ 2 rows selected (0.008 seconds) same thing with a hint
explain select /*+ INDEX(test_table idx2) */ col2 from test_table where col2 = 'v1-2';
Any reason it isn't using the index. We have tried adding over 400 rows with the same results. Thanks
... View more
