MySQL Forums
Forum List  »  Partitioning

Re: Get Partition Tables
Posted by: Rick James
Date: January 23, 2011 11:54AM

I could interpret your question multiple ways.

Here are the partitioned tables in my database called 'try':
SELECT  *
    FROM  information_schema.TABLES
    WHERE  TABLE_SCHEMA = 'try'
      and  CREATE_OPTIONS like '%partition%'
\G

  TABLE_CATALOG: NULL
   TABLE_SCHEMA: try
     TABLE_NAME: part_tab
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 8000000
 AVG_ROW_LENGTH: 31
    DATA_LENGTH: 255999996
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 12288
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2010-12-02 20:46:36
    UPDATE_TIME: 2010-12-02 21:13:09
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: partitioned
  TABLE_COMMENT:

And here is the list of partitions in that table:
mysql> 
SELECT  *
    FROM  information_schema.PARTITIONS
    WHERE  TABLE_SCHEMA = 'try' an d TABLE_NAME = 'part_tab' \G

*************************** 1. row ***************************
                TABLE_CATALOG: NULL
                 TABLE_SCHEMA: try
                   TABLE_NAME: part_tab
               PARTITION_NAME: p0
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: year(c3)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 1995
                   TABLE_ROWS: 0
               AVG_ROW_LENGTH: 0
                  DATA_LENGTH: 0
              MAX_DATA_LENGTH: 281474976710655
                 INDEX_LENGTH: 1024
                    DATA_FREE: 0
                  CREATE_TIME: 2010-12-02 20:46:36
                  UPDATE_TIME: 2010-12-02 20:46:36
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
*************************** 2. row ***************************
                TABLE_CATALOG: NULL
                 TABLE_SCHEMA: try
                   TABLE_NAME: part_tab
               PARTITION_NAME: p1
            SUBPARTITION_NAME: NULL
   PARTITION_ORDINAL_POSITION: 2
SUBPARTITION_ORDINAL_POSITION: NULL
             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: year(c3)
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 1996
                   TABLE_ROWS: 798458
               AVG_ROW_LENGTH: 32
                  DATA_LENGTH: 25550656
              MAX_DATA_LENGTH: 281474976710655
                 INDEX_LENGTH: 1024
                    DATA_FREE: 0
                  CREATE_TIME: 2010-12-02 20:46:36
                  UPDATE_TIME: 2010-12-02 21:13:09
                   CHECK_TIME: NULL
                     CHECKSUM: NULL
            PARTITION_COMMENT:
                    NODEGROUP: default
              TABLESPACE_NAME: NULL
(etc)

Or, perhaps this is all you wanted?
SHOW CREATE TABLE try.part_tab \G

CREATE TABLE `part_tab` (
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(30) DEFAULT NULL,
  `c3` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
 /*!50100 PARTITION BY RANGE (year(c3)) (
PARTITION p0 VALUES LESS THAN (1995) ENGINE = MyISAM, 
PARTITION p1 VALUES LESS THAN (1996) ENGINE = MyISAM, 
PARTITION p2 VALUES LESS THAN (1997) ENGINE = MyISAM, 
PARTITION p3 VALUES LESS THAN (1998) ENGINE = MyISAM, 
...
PARTITION p10 VALUES LESS THAN (2010) ENGINE = MyISAM, 
PARTITION p11 VALUES LESS THAN MAXVALUE 
ENGINE = MyISAM) */

Options: ReplyQuote


Subject
Views
Written By
Posted
2359
January 20, 2011 05:49AM
Re: Get Partition Tables
1496
January 23, 2011 11:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.