0

When a partition is done in mysql , does it create tables or does it create virtual tables (i.e. views) which are pretty much stored queries accessed by a table name?

Here is an example (although in Oracle, this is just used as an example - I am most curious about how it is handled in mysql) from http://www.devarticles.com/c/a/Oracle/Partitioning-in-Oracle/1/:

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER)
PARTITION BY RANGE(ORDER_DATE)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
;
Travis J
  • 81,153
  • 41
  • 202
  • 273
  • 1
    **What** database system, and which version?? **SQL** is just the Structured Query Language - a language used by many database systems - SQL is **NOT** a database product... stuff like this is very often vendor-specific - so we really need to know what database system you're using.... – marc_s Apr 05 '12 at 20:27
  • @marc_s - I didn't realize that different vendors took different approaches to partitioning, sorry. I would be most interested in MySQL and will update the question. – Travis J Apr 05 '12 at 20:28

1 Answers1

1

According to the Mysql 5.6 Reference Manual (16.1 Overview of Partitioning in MySQL):

Partitioning takes this notion a step further, by enabling you to distribute portions of individual tables across a file system according to rules which you can set largely as needed. In effect, different portions of a table are stored as separate tables in different locations.

Lynn Crumbling
  • 12,985
  • 8
  • 57
  • 95
  • Yes, I have read that entire manual. However, what I took issue with in that line was the `In effect` portion. It is a rather vague way to start that sentence and made it hard to interpret weather or not the table partitioned was still around, and so were a group of other partition tables. Or, if the table partitioned was no longer present and instead replaced with a group of "partition" tables. Or, if the table was still there, there were in fact no other tables, and the partitions represented virtual tables. – Travis J Apr 05 '12 at 21:22