[
https://issues.apache.org/jira/browse/TAJO-475?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13861981#comment-13861981
]
Min Zhou commented on TAJO-475:
-------------------------------
Here is the meta table for paritions in mysql, I perfer this design. They don't
store the number of partitions, just each partition has one row.
{noformat}
mysql> desc PARTITIONS;
+-------------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default |
Extra |
+-------------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | YES | | NULL |
|
| TABLE_SCHEMA | varchar(64) | NO | | |
|
| TABLE_NAME | varchar(64) | NO | | |
|
| PARTITION_NAME | varchar(64) | YES | | NULL |
|
| SUBPARTITION_NAME | varchar(64) | YES | | NULL |
|
| PARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL |
|
| SUBPARTITION_ORDINAL_POSITION | bigint(21) unsigned | YES | | NULL |
|
| PARTITION_METHOD | varchar(12) | YES | | NULL |
|
| SUBPARTITION_METHOD | varchar(12) | YES | | NULL |
|
| PARTITION_EXPRESSION | longtext | YES | | NULL |
|
| SUBPARTITION_EXPRESSION | longtext | YES | | NULL |
|
| PARTITION_DESCRIPTION | longtext | YES | | NULL |
|
| TABLE_ROWS | bigint(21) unsigned | NO | | 0 |
|
| AVG_ROW_LENGTH | bigint(21) unsigned | NO | | 0 |
|
| DATA_LENGTH | bigint(21) unsigned | NO | | 0 |
|
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL |
|
| INDEX_LENGTH | bigint(21) unsigned | NO | | 0 |
|
| DATA_FREE | bigint(21) unsigned | NO | | 0 |
|
| CREATE_TIME | datetime | YES | | NULL |
|
| UPDATE_TIME | datetime | YES | | NULL |
|
| CHECK_TIME | datetime | YES | | NULL |
|
| CHECKSUM | bigint(21) unsigned | YES | | NULL |
|
| PARTITION_COMMENT | varchar(80) | NO | | |
|
| NODEGROUP | varchar(12) | NO | | |
|
| TABLESPACE_NAME | varchar(64) | YES | | NULL |
|
+-------------------------------+---------------------+------+-----+---------+-------+
{noformat}
If I create a partitioned table in mysql like this way
{noformat}
mysql> CREATE TABLE IF NOT EXISTS `user` (
-> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'user ID',
-> `name` varchar(50) NOT NULL DEFAULT '' COMMENT user name',
-> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 for maleļ¼1 for female',
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
-> PARTITION BY RANGE (id) (
-> PARTITION p0 VALUES LESS THAN (3),
-> PARTITION p1 VALUES LESS THAN (6),
-> PARTITION p2 VALUES LESS THAN (9),
-> PARTITION p3 VALUES LESS THAN (12),
-> PARTITION p4 VALUES LESS THAN MAXVALUE
-> );
{noformat}
The metadata will be like this
{noformat}
mysql> select TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION,
PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, NODEGROUP from
PARTITIONS where TABLE_NAME='user';
+------------+----------------+----------------------------+------------------+----------------------+-----------------------+-----------+
| TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | PARTITION_METHOD |
PARTITION_EXPRESSION | PARTITION_DESCRIPTION | NODEGROUP |
+------------+----------------+----------------------------+------------------+----------------------+-----------------------+-----------+
| user | p0 | 1 | RANGE |
id | 3 | default |
| user | p1 | 2 | RANGE |
id | 6 | default |
| user | p2 | 3 | RANGE |
id | 9 | default |
| user | p3 | 4 | RANGE |
id | 12 | default |
| user | p4 | 5 | RANGE |
id | MAXVALUE | default |
+------------+----------------+----------------------------+------------------+----------------------+-----------------------+-----------+
{noformat}
This is quite good for partition pruning and memory cached table .
Does it make sense?
> Table partition catalog recap
> -----------------------------
>
> Key: TAJO-475
> URL: https://issues.apache.org/jira/browse/TAJO-475
> Project: Tajo
> Issue Type: Sub-task
> Components: catalog
> Reporter: Min Zhou
> Assignee: Min Zhou
>
> Query master need to know where partitions of memory cached table locate.
> At least we need a meta table contain such information
> |partition_id|
> |partition_value|
> |ordinal_position|
> |locations|
> Any suggestion?
>
--
This message was sent by Atlassian JIRA
(v6.1.5#6160)
