Oracle creates table partitions----range partition __oracle

Creating partitions is a means of database optimization, and range partitioning is one of the table partitions.

The key word for creating a range partition is "range", where the data can be distributed according to the range specified by the partition key value, and the performance is best when the data is evenly distributed across the range.

For example, if we select a date as the partitioning key, the partition "AUG-2011" will include all the partitioning key values from 01-aug-2011 to 31-aug-2011 (assuming that the partition is scoped from the first day of the month to the last day of the month).

Note: When the table structure adopts range partitioning, first consider the method that the partitioning column should conform to the range partitioning, secondly, consider the range of the data value of the column, and finally consider the boundary problem of the column.

The following is an example to demonstrate the creation of a range partition.

Topic: Create a retail table of merchandise, and then create 4 partitions for the table based on the quarter of the sales date.

The steps are as follows:

First, create a new SQL statement window in the Oracle Database administration Tool PL/SQL developer, as shown in the following illustration:

Next, we create two table spaces, respectively tbsp_1 and tbsp_2, by entering the following statement in the SQL window:

CREATE tablespace tbsp_1 datafile ' d:\app\administrator\oradata\orcl/tbsp_1.dbf ' SIZE 10M;
CREATE tablespace tbsp_2 datafile ' d:\app\administrator\oradata\orcl/tbsp_2.dbf ' SIZE 10M;

After executing the statement, we can see the two tablespaces we just created.

Then we create a table in the SQL statement window, "retail table," which represents the sales number, sales date, and product name, and creates four partitions, as follows:

CREATE TABLE Ware_retail_part
(
    ID integer primary key,
    retail_date date,
    ware_name varchar2 (m)
partition by Range (retail_date)
(-
   -the first quarter of 2011 is par_1 partition
   partition par_01 values less than (' 2011-04-01 ', ' yyyy-mm-dd ') tablespace tbsp_1,
   --in the second quarter of 2011 for par_2 Zoning
   partition par_02 values less than (to_date (' 2011-07-01 ', ' yyyy-mm-dd ') Tablespace tbsp_1, in the
   third quarter of 2011, was par_3 Division
   partition PAR_03 values less than (' 2011-10-01 ', ' Yyyy-mm-dd ') tablespace tbsp_2,
   fourth quarter of 2011 for par_4 Zoning partition par_04
   values less than (' 2012-01-01 ', ' yyyy-mm-dd ') tablespace tbsp_2
)

Execute the above statement, the table interval is created, this is we can see the partition is already in the table space, the effect is as follows:

After you create 4 range partitions for the retail table, several records are inserted into the table below.

INSERT into Ware_retail_part values (1,to_date (' 2011-01-20 ', ' yyyy-mm-dd '), ' Tablet PC ');
INSERT into Ware_retail_part values (2,to_date (' 2011-04-15 ', ' yyyy-mm-dd '), ' Millet 3 Mobile ');
INSERT into Ware_retail_part values (3,to_date (' 2011-07-25 ', ' yyyy-mm-dd '), ' iwatch ');
INSERT into Ware_retail_part values (4,to_date (' 2011-12-17 ', ' yyyy-mm-dd '), ' Asus Notebooks ');

After the creation is complete, we inquire whether there are records in the sales table.

Finally, we expand, after inserting several data into the Sales Retail table, the user can query the data through the partition table (that is, the partitioned datasheet), which is faster than the query from the entire table, let's give an example:

Let's say we're querying all the records in the partition par2_02 in Datasheet ware_retail_part, and the SQL statement is as follows:

SELECT * from Ware_retail_part partition (PAR_02);

This is the data record we can find in the partition.