[ https://issues.apache.org/jira/browse/PHOENIX-7170?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17804495#comment-17804495 ]
Kadir Ozdemir commented on PHOENIX-7170: ---------------------------------------- [~tkhurana], your example with the CASE WHEN operation can be expresses with a where clause as follows {code:java} WHERE ((ID IS BETWEEN 1 AND 100) AND CURRENT_TIME() - PHOENIX_ROW_TIMESTAMP() >= <10 days>) OR ((ID IS BETWEEN 101 AND 200) AND CURRENT_TIME() - PHOENIX_ROW_TIMESTAMP() >= <7 days>) OR ((ID IS NOT IN BETWEEN 1 AND 200) AND CURRENT_TIME() - PHOENIX_ROW_TIMESTAMP() >= <5 days>){code} I like your suggestion not because of a single where clause is not expressive enough but rather your suggestion is simpler to express and extends the current TTL attribute and so eliminates the need to introduce a new attribute. > Phoenix Row TTL > --------------- > > Key: PHOENIX-7170 > URL: https://issues.apache.org/jira/browse/PHOENIX-7170 > Project: Phoenix > Issue Type: New Feature > Reporter: Kadir Ozdemir > Priority: Major > > Deleting rows using delete markers require running delete queries to insert > them, one for each row to be deleted. Often applications need to run periodic > jobs to issue delete queries to insert delete markers. Deleting rows using > TTL is more performance optimized compared to adding delete markers in > Phoenix since TTL works without inserting delete markers. Phoenix currently > supports table and view (level) TTL. It is desirable to have a row level TTL > feature to extend the TTL future to delete a subset of rows of a table or > updatable view. > As in partial indexes, a row level TTL can be set using a where clause. This > clause can be set using CREATE and ALTER statements by adding ROW_TTL=(<where > clause>). For example, ROW_TTL = (WHERE CURRENT_TIME() - LAST_UPDATE > > 8640000 AND ID IS BETWEEN 1 AND 100) where LAST_UPDATE and ID are the columns > of the table (or updatable view). As for partial indexes, the where clause > should be evaluable on a single row. > The compaction scanner (CompactionScanner) in Phoenix can evaluate a > row-TTL-where clause on a row and decide if the row should be deleted. > Similarly, on the read path TTLRegionScanner can mask the deleted rows using > row-TTL-where clauses. The row-TTL-where clauses can be stored in SYSCAT in > header rows. -- This message was sent by Atlassian Jira (v8.20.10#820010)