IIRC the index hint is sensitive to spaces between the INDEX keyword and the parentheses. Try removing the optional whitespaces from the hint.
On Fri, Nov 22, 2024 at 4:21 PM Shuai,Ben <shu...@oclc.org> wrote: > I am experimenting phoenix and see how secondary index is used. I found > out that global secondary index was not guaranteed to be used even if hint > is used. I wondered if I missed any steps. > > > > I used hbase version 2.2.7 with phoenix 5.1.3 (the client and server jar > used are phoenix-client-embedded-hbase-2.2-5.1.3.jar and > phoenix-server-hbase-2.2-5.1.3.jar > ). Based on https://issues.apache.org/jira/browse/PHOENIX-6458, the hint > should force index to be used even if the column is not a covered column, > but it did not. > > > here is how table and its index were created, and the plan showed full > table scan: > > CREATE TABLE IF NOT EXISTS DATATABLE_CF0_NATURE_CEB2 (rowkey > VARCHAR PRIMARY KEY, C1 VARCHAR, C2 VARCHAR ); > > > > create index DATATABLE_CF0_NATURE_CEB2_IDX1 on > DATATABLE_CF0_NATURE_CEB2 (C1); > > > > Plan is also Full scan: > > explain select /*+ INDEX ( DATATABLE_CF0_NATURE_CEB2 > DATATABLE_CF0_NATURE_CEB2_IDX1 ) */ c2 from DATATABLE_CF0_NATURE_CEB2 where > c1='me'; > > > +------------------------------------------------------------------------------------+----------------+---------------+-------------+ > > | > PLAN | EST_BYTES_READ | > EST_ROWS_READ | EST_INFO_TS | > > > +------------------------------------------------------------------------------------+----------------+---------------+-------------+ > > | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN *FULL* SCAN OVER > DATATABLE_CF0_NATURE_CEB2 | null | null | null | > > | SERVER FILTER BY C1 = > 'me' | null | > null | null | > > > +------------------------------------------------------------------------------------+----------------+---------------+-------------+ > > > In another table created using the same way , the plan did show the > secondary index was used: > > CREATE TABLE pure_phoenix_poc_table ( id INTEGER not null primary key > desc, name VARCHAR (30), date DATE , m.db_utilization DECIMAL, > i.db_utilization DECIMAL ) m.DATA_BLOCK_ENCODING='DIFF'; > > create index PURE_PHOENIX_POC_TABLE_IDX_NAME on PURE_PHOENIX_POC_TABLE > (name); > > explain select /*+ INDEX(PURE_PHOENIX_POC_TABLE > PURE_PHOENIX_POC_TABLE_IDX_NAME) */ i.DB_UTILIZATION from > PURE_PHOENIX_POC_TABLE where name = '111'; > > > +---------------------------------------------------------------------------------------------------+----------------+---------------+-------------+ > > | > PLAN | EST_BYTES_READ | > EST_ROWS_READ | EST_INFO_TS | > > > +---------------------------------------------------------------------------------------------------+----------------+---------------+-------------+ > > | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN *RANGE* SCAN OVER > PURE_PHOENIX_POC_TABLE_IDX_NAME ['111'] | null | null | > null | > > | SERVER MERGE > [I.DB_UTILIZATION] > | null | null | null | > > | SERVER FILTER BY FIRST KEY > ONLY | > null | null | null | > > > +---------------------------------------------------------------------------------------------------+----------------+---------------+-------------+ > > Any help is appreciated. Thanks > > > -- *István Tóth* | Sr. Staff Software Engineer *Email*: st...@cloudera.com cloudera.com <https://www.cloudera.com> [image: Cloudera] <https://www.cloudera.com/> [image: Cloudera on Twitter] <https://twitter.com/cloudera> [image: Cloudera on Facebook] <https://www.facebook.com/cloudera> [image: Cloudera on LinkedIn] <https://www.linkedin.com/company/cloudera> ------------------------------ ------------------------------
