[
https://issues.apache.org/jira/browse/PHOENIX-7170?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17804495#comment-17804495
]
Kadir Ozdemir edited comment on PHOENIX-7170 at 1/8/24 10:50 PM:
-----------------------------------------------------------------
[~tkhurana], your example with the CASE WHEN operation can be expressed by 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.
was (Author: kadir):
[~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)
