tajo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Min Zhou (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (TAJO-475) Table partition catalog recap
Date Fri, 03 Jan 2014 22:53:51 GMT

    [ 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)

Mime
View raw message