SQL command PARTITION BY?

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 imovel table and defining that it should be partitioned by the number of quarters and odd , two tables would be created in the DB < and "ImmovelQuartosParas" , which would be accessible through select * from tabelas .

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

PARTITION BY LIST

Can be used when the partition needs to be made based on defined values, for example partitioning the usuarios table by gender:

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 the partition needs to be partitioned, 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 employees table 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 .

Partitioning a table means dividing a table, which is logically large, into several physically small tables with the goal of improving the performance of applications that use the database. For example you can partition a sales table into several tables each one referring to a year-month of sale, logically you have all sales but physically the accesses to each sales year-month will be optimized.