We are using Phoenix 4.1 with HBase 0.98.6-cdh5.2.0. After I successfully create an index and run an explain against my query that uses the index, the results state that a FULL SCAN is used against the index instead of a RANGE SCAN. What could be causing this problem? I would expect that a RANGE SCAN would be used against the index.

 

For example:

 

CREATE INDEX IF NOT EXISTS IX_PRODUCT_UPC_NAME ON TEST.PRODUCT (F.UPC, F.NAME);

2,277,977 rows affected (323.538 seconds)

 

EXPLAIN SELECT F.UPC FROM TEST.PRODUCT WHERE F.UPC=123456 and F.NAME ='A';

 

+------------+

|    PLAN    |

+------------+

| CLIENT PARALLEL 32-WAY FULL SCAN OVER TEST.IX_PRODUCT_UPC_NAME |

|     SERVER FILTER BY (TO_LONG(UPC) = 123456 AND NAME = 'A') |

+------------+

2 rows selected (0.059 seconds)

 

 

Even if I use a hint to force the query to use the index OR a range scan, the results are the same, a FULL SCAN is used. Any suggestions?

 

For example:

 

EXPLAIN SELECT /* INDEX(TEST.IX_PRODUCT_UPC_NAME) */ F.UPC FROM TEST.PRODUCT WHERE F.UPC=123456 and F.NAME ='A';

 

+------------+

|    PLAN    |

+------------+

| CLIENT PARALLEL 32-WAY FULL SCAN OVER TEST.IX_PRODUCT_UPC_NAME |

|     SERVER FILTER BY (TO_LONG(UPC) = 123456 AND NAME = 'A') |

+------------+

2 rows selected (0.056 seconds)

 

Thank you for your time,

 

Kevin Verhoeven

kevin.verhoeven@ds-iq.com