Reference table as a columnstore

Off-Topic
1

hi, there.

what does “attach partition” mean?
The table with the corresponding command output was created by configuring the reference table as a column store.

What are some examples of configuring ‘reference table’ as a column store?
Is this table also shaded and partitioned?

memsql> show partitions extended on tpch;
+---------+-----------+------+--------+--------+------+------------------+------------+--------------+
| Ordinal | Host      | Port | Role   | Locked | Info | Last Command     | Last Error | Last Message |
+---------+-----------+------+--------+--------+------+------------------+------------+--------------+
|       0 | 127.0.0.1 | 3307 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       1 | 127.0.0.1 | 3307 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       2 | 127.0.0.1 | 3307 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       3 | 127.0.0.1 | 3307 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       4 | 127.0.0.1 | 3307 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       5 | 127.0.0.1 | 3307 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       6 | 127.0.0.1 | 3307 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
|       7 | 127.0.0.1 | 3307 | Master |      0 | NULL | CREATE PARTITION |          0 |              |
+---------+-----------+------+--------+--------+------+------------------+------------+--------------+
8 rows in set (0.00 sec)

memsql> show partitions extended on test;
+---------+-----------+------+--------+--------+------+------------------+------------+--------------+
| Ordinal | Host      | Port | Role   | Locked | Info | Last Command     | Last Error | Last Message |
+---------+-----------+------+--------+--------+------+------------------+------------+--------------+
|       0 | 127.0.0.1 | 3307 | Master |      0 | NULL | ATTACH PARTITION |          0 |              |
|       1 | 127.0.0.1 | 3307 | Master |      0 | NULL | ATTACH PARTITION |          0 |              |
|       2 | 127.0.0.1 | 3307 | Master |      0 | NULL | ATTACH PARTITION |          0 |              |
|       3 | 127.0.0.1 | 3307 | Master |      0 | NULL | ATTACH PARTITION |          0 |              |
|       4 | 127.0.0.1 | 3307 | Master |      0 | NULL | ATTACH PARTITION |          0 |              |
|       5 | 127.0.0.1 | 3307 | Master |      0 | NULL | ATTACH PARTITION |          0 |              |
|       6 | 127.0.0.1 | 3307 | Master |      0 | NULL | ATTACH PARTITION |          0 |              |
|       7 | 127.0.0.1 | 3307 | Master |      0 | NULL | ATTACH PARTITION |          0 |              |
+---------+-----------+------+--------+--------+------+------------------+------------+--------------+
8 rows in set (0.00 sec)

thanks in advance.

2

Your referring to the “Last Command” column? That is the last operation ran on each partition. It can be CREATE, COPY, ATTACH or PROMOTE. ATTACH means the last thing that happened to the partition was attaching it back to the cluster (likely after a node restart). These are run at the partition level (independent of the tables involved).

REFERENCE tables aren’t sharded. You can create them as row or column stores. Every node in the cluster gets a copy of this table (more info here SingleStoreDB Cloud · SingleStore Documentation). They are typically used for smaller, rarely updated dimension tables that are commonly joined against.