[
https://issues.apache.org/jira/browse/IGNITE-16591?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Yury Gerzhedovich updated IGNITE-16591:
---------------------------------------
Epic Link: IGNITE-16603
> DDL syntax COLOCATE BY
> ----------------------
>
> Key: IGNITE-16591
> URL: https://issues.apache.org/jira/browse/IGNITE-16591
> Project: Ignite
> Issue Type: Improvement
> Components: sql
> Reporter: Yury Gerzhedovich
> Priority: Major
> Labels: ignite-3
>
> Add COLOCATED BY for CREATE TABLE syntaxis:
> h2. Colocation by non-composite primary key
> As a typical example, let’s assume we have the following schema:
> {code:sql}
> CREATE TABLE Accounts (
> account_id INT,
> name VARCHAR,
> balance DOUBLE,
> PRIMARY KEY (account_id)
> )
> {code}
> {code:sql}
> CREATE TABLE Transactions (
> tx_id INT,
> account_id INT,
> amount DOUBLE,
> PRIMARY KEY (tx_id)
> )
> {code}
> Most of the operations on such a schema will be isolated within a single
> account. Examples:
> 1. Add a new transaction, and update the balance of the corresponding account.
> 2. Run through all the transactions for an account, and calculate the average
> amount.
> To achieve better performance, we want each of these operations to execute on
> a single node. Therefore, we need to group all transactions belonging to the
> same account within the same Raft group (partition, range, etc).
> Corresponding records that represent the account itself should also reside in
> the same group.
> To achieve this, we want to perform affinity mapping for the Transactions
> table using the account_id field (as opposed to the tx_id, as it would happen
> currently). We need to allow users to specify the field used for colocation.
> For example, like this:
> {code:sql}
> CREATE TABLE Transactions (
> tx_id INT,
> account_id INT,
> amount DOUBLE,
> PRIMARY KEY (tx_id, account_id)
> )
> COLOCATE BY (account_id)
> {code}
> NOTE: the affinity key must always be a part of the primary key. Otherwise,
> an exception is thrown.
> NOTE: Primary key columns, and therefore affinity key columns, MUST NOT
> permit NULL values according to the SQL standard.
> h2. Colocation by composite primary key
> In case of using the composite primary key the tables also could be
> colocated. For example:
> {code:sql}
> CREATE TABLE Orders (
> order_id INT,
> department_id INT,
> create_date TIMESTAMP,
> PRIMARY KEY (order_id, department_id)
> )
> {code}
> {code:sql}
> CREATE TABLE OrderItems (
> order_item_id INT,
> order_id INT,
> department_id INT,
> amount INT,
> PRIMARY KEY (order_item_id)
> )
> {code}
> For such data model the OrderItems table could be colocated with the Orders
> table in the following way:
> {code:sql}
> CREATE TABLE OrderItems (
> order_item_id INT,
> order_id INT,
> department_id INT,
> amount INT,
> PRIMARY KEY (order_item_id, order_id, department_id)
> )
> COLOCATE BY (order_id, department_id)
> {code}
> Note: COLOCATE BY clause of colocated table (OrderItems table in the example
> above) must contain the same set of columns and in the same order as PRIMARY
> KEY clause of the main table (Orders table in the example above) in order to
> properly colocate the data.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
