Command SQL PARTITION BY?

Asked

Viewed 555 times

8

I am reading and it was not very clear the command PARTITION BY in the creation of 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 you:

  • To serve the command in general PARTITION BY?
  • What are the advantages and disadvantages of this command?
sql

  • There is some problem the question, it does not make sense, what I can do to improve, I have been being persecuted for some time and taking negative votes every day.

2 answers

5


Basically the 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.

Exemplifying, when creating the table imovel and define that it should be supported by the number of rooms pairs and odd, two tables would be created in the BD, "immoveToomsFor" and "immovable", which would be accessible through select * from tabelas.

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

PARTITION BY LIST

Can be used when the partition needs to be made based on defined values, for example partitioning the table usuarios for 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 partition needs to be made based on ranges, such as age group:

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 in 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.

  • I liked your answer, what would be the part of SQL? would be in charge of the bank itself to take the data of the other tables created?

  • how so "the SQL part"? It is necessary to define how this will be done division, will not be in charge of the sgbd (if I understood correctly).

  • Maybe I expressed bad, example select * from tabela where code > 30 let’s say that the data is in the auxiliary tables... ? type do not need to do anything the bank if in charge of searching the information in the auxiliary tables?

  • 1

    "would be accessible through the select * from tabelas" - beauty, this is sgbd’s responsibility. In case, the query would be written as you did (select * from tabela where code > 30) without having to tell which partition to search on.

3

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

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.