Comando SQL PARTITION BY?

Question:

I'm reading and it was not very clear the PARTITION BY command when creating tables in structured databases ( SQL ), a basic example :

CREATE TABLE employees  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(25) NOT NULL    
)   
PARTITION BY RANGE(id)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN (15),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

I ask:

  • What is the PARTITION BY command for in general?
  • What are the advantages and disadvantages of this command?

Answer:

Basically, PARTITION BY creates a table with one or more partitions; in other words, physical tables are created that will be accessed through the specified table.

For example, when creating the table imovel and set it to be partitioned by the amount of even and odd rooms, would be created in the database two tables, "imovelQuartosPares" and "imovelQuartosImpares" which would be accessible through select * from tabelas .

This partitioning can be controlled in several ways, only two will be shown to help understanding, but a list of possibilities and explanations can be found at this link :

PARTITION BY LIST

It can be used when the partition needs to be made based on defined values, for example partition the usuarios table by sex:

CREATE TABLE usuarios
( id NUMBER, nome VARCHAR2(50), idade NUMBER, sexo VARCHAR2(1))
PARTITION BY LIST (sexo) (
    PARTITION masculino VALUES ('M'),
    PARTITION feminino VALUES ('F')
);

PARTITION BY RANGE

Used when partitioning needs to be done based on ranges, such as age range:

CREATE TABLE usuarios
( id NUMBER, nome VARCHAR2(50), idade NUMBER, sexo VARCHAR2(1))
PARTITION BY RANGE (idade) (
    PARTITION crianca VALUES LESS THAN (18),
    PARTITION adulto VALUES LESS THAN (65),
    PARTITION idoso VALUES LESS THAN MAXVALUE
);

Understanding the example of the question:

CREATE TABLE employees  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(25) NOT NULL    
)   
PARTITION BY RANGE(id)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN (15),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

Created the table employees partitioned into four other tables:

  • p0 – records with id less than 5 ;
  • p1 – records with id greater than or equal to 5 and less than 10 ;
  • p1 – records with id greater than or equal to 10 and less than 15 ;
  • p3 – records with id greater than or equal to 15 .
Post Views: 0
Scroll to Top