mirror of
https://github.com/scylladb/scylladb.git
synced 2026-04-19 16:15:07 +00:00
360f7b3d33
This commit moves the Features page from the section for developers to the top level in the page tree. This involves: - Moving the source files to the *features* folder from the *using-scylla* folder. - Moving images into *features/images* folder. - Updating references to the moved resources. - Adding redirections to the moved pages. Closes scylladb/scylladb#20401
143 lines
4.6 KiB
ReStructuredText
143 lines
4.6 KiB
ReStructuredText
Row range deletions
|
|
-------------------
|
|
|
|
Range deletions are statements in which you delete a set of rows specified by a continuous range of clustering keys.
|
|
They work within one partition, so you must provide a single partition key when performing a range deletion.
|
|
|
|
The following is an example of a row range deletion with CDC enabled.
|
|
|
|
#. Start with a basic table and insert some data into it.
|
|
|
|
.. code-block:: cql
|
|
|
|
CREATE TABLE ks.t (pk int, ck int, v int, PRIMARY KEY (pk, ck)) WITH cdc = {'enabled':'true'};
|
|
INSERT INTO ks.t (pk,ck,v) VALUES (0,0,0);
|
|
INSERT INTO ks.t (pk,ck,v) VALUES (0,1,1);
|
|
INSERT INTO ks.t (pk,ck,v) VALUES (0,2,2);
|
|
INSERT INTO ks.t (pk,ck,v) VALUES (0,3,3);
|
|
|
|
#. Display the contents of the table
|
|
|
|
.. code-block:: cql
|
|
|
|
SELECT * FROM ks.t;
|
|
|
|
returns:
|
|
|
|
.. code-block:: none
|
|
|
|
pk | ck | v
|
|
----+----+---
|
|
0 | 0 | 0
|
|
0 | 1 | 1
|
|
0 | 2 | 2
|
|
0 | 3 | 3
|
|
|
|
(4 rows)
|
|
|
|
#. Remove all rows where the primary key is 0 and the clustering key is in the ``(0, 2]`` range (left-opened, right-closed).
|
|
|
|
.. code-block:: cql
|
|
|
|
DELETE FROM ks.t WHERE pk = 0 AND ck <= 2 and ck > 0;
|
|
|
|
#. Display the contents of the table after the delete:
|
|
|
|
.. code-block:: cql
|
|
|
|
SELECT * FROM ks.t;
|
|
|
|
returns:
|
|
|
|
.. code-block:: none
|
|
|
|
pk | ck | v
|
|
----+----+---
|
|
0 | 0 | 0
|
|
0 | 3 | 3
|
|
|
|
(2 rows)
|
|
|
|
#. Display the contents of the CDC log table:
|
|
|
|
.. code-block:: cql
|
|
|
|
SELECT "cdc$batch_seq_no", pk, ck, v, "cdc$operation" FROM ks.t_scylla_cdc_log;
|
|
|
|
returns:
|
|
|
|
.. code-block:: none
|
|
|
|
cdc$batch_seq_no | pk | ck | v | cdc$operation
|
|
------------------+----+----+------+---------------
|
|
0 | 0 | 0 | 0 | 2
|
|
0 | 0 | 1 | 1 | 2
|
|
0 | 0 | 2 | 2 | 2
|
|
0 | 0 | 3 | 3 | 2
|
|
0 | 0 | 0 | null | 6
|
|
1 | 0 | 2 | null | 7
|
|
|
|
(6 rows)
|
|
|
|
A range deletion appears in CDC as a batch of up to two entries into the log table: an entry corresponding to range's left bound (if specified), and an entry corresponding to the range's right bound (if specified). The appropriate values of ``cdc$operation`` are as follows:
|
|
|
|
* ``5`` denotes an inclusive left bound,
|
|
* ``6`` denotes an exclusive left bound,
|
|
* ``7`` denotes an inclusive right bound,
|
|
* ``8`` denotes an exclusive right bound.
|
|
|
|
In the example above we've used the range ``(0, 2]``, with an exclusive left bound and inclusive right bound. This is presented in the CDC log as two log rows: one with ``cdc$operation = 6`` and the other with ``cdc$operation = 7``.
|
|
|
|
The values for non-key base columns are ``null`` in these entries.
|
|
|
|
If you specify a one-sided range:
|
|
|
|
.. code-block:: cql
|
|
|
|
DELETE FROM ks.t WHERE pk = 0 AND ck < 3;
|
|
|
|
then two entries will appear in the CDC log: one with ``cdc$operation = 5`` and ``null`` clustering key, indicating the range is not left-bounded and the other with ``cdc$operation = 8``, representing the right bound of the range:
|
|
|
|
.. code-block:: none
|
|
|
|
cdc$batch_seq_no | pk | ck | v | cdc$operation
|
|
------------------+----+------+------+---------------
|
|
0 | 0 | null | null | 5
|
|
1 | 0 | 3 | null | 8
|
|
|
|
|
|
|
|
Range deletions with multi-column clustering keys
|
|
+++++++++++++++++++++++++++++++++++++++++++++++++
|
|
|
|
In the case of multi-column clustering keys, range deletions allow to specify an equality relation for some prefix of the clustering key, followed by an ordering relation on the column that follows the prefix.
|
|
|
|
#. Start with a basic table:
|
|
|
|
.. code-block:: cql
|
|
|
|
CREATE TABLE ks.t (pk int, ck1 int, ck2 int, ck3 int, v int, primary key (pk, ck1, ck2, ck3)) WITH cdc = {'enabled':'true'};
|
|
|
|
#. Remove the rows where the primary key is 0, a prefix of the clustering key (``ck1``) is ``0``, and the following column of the clustering key (``ck2``) is in the range ``(0, 3)``:
|
|
|
|
.. code-block:: cql
|
|
|
|
DELETE FROM ks.t WHERE pk = 0 and ck1 = 0 AND ck2 > 0 AND ck2 < 3;
|
|
|
|
#. Display the contents of the table:
|
|
|
|
.. code-block:: cql
|
|
|
|
SELECT "cdc$batch_seq_no", pk, ck1, ck2, ck3, v, "cdc$operation" FROM ks.t_scylla_cdc_log;
|
|
|
|
returns:
|
|
|
|
.. code-block:: none
|
|
|
|
cdc$batch_seq_no | pk | ck1 | ck2 | ck3 | v | cdc$operation
|
|
------------------+----+-----+-----+------+------+---------------
|
|
0 | 0 | 0 | 0 | null | null | 6
|
|
1 | 0 | 0 | 3 | null | null | 8
|
|
|
|
(2 rows)
|