apache-phoenix - 如何从apache phoenix中删除不在每个索引中的字段查询

Question

我尝试执行语句

DELETE FROM statistics WHERE statistic_id is null

并出现错误:

java.sql.SQLException: ERROR 1027 (42Y86): All columns referenced in a WHERE clause must be available in every index for a table with immutable rows. tableName=STATISTICS
at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:386)
at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
at org.apache.phoenix.compile.DeleteCompiler.compile(DeleteCompiler.java:389)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:553)
at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:541)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:303)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:296)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:294)
at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1254)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:808)
at sqlline.SqlLine.begin(SqlLine.java:681)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:292)

我的主键位于字段 ID 上，辅助键位于 STATISTIC_ID

Answer 1

Phoenix 要求 [1] 当您从具有不可变行的表中删除某些内容时，要删除的行应按所有索引列进行过滤。这样做的一种方法是禁用有问题的索引

ALTER INDEX index_name ON table_name DISABLE;
DELETE FROM table_name WHERE condition;

然后重建禁用的索引:

ALTER INDEX index_name ON table_name REBUILD;

但是，请记住，此操作需要大量时间和资源。

[1] https://phoenix.apache.org/secondary_indexing.html#Immutable_Tables