Oracle partitioning technology improves query efficiency

Overview:

When the amount of data in the table is increasing, the query data slows down and the performance of the application degrades, so you should consider partitioning the table. After the table is partitioned, the logical table is still a complete table, but the data in the table is physically stored in multiple table spaces (physical files) so that querying the data does not always scan the entire table.

Here's how to use partitioning to increase query efficiency

Range Partition: is a zone partition

 create  table   SALE (product_id  varchar2  (5  ), sale_count  number  (10 , 2   by   RANGE (sale_count) (PARTITION P1  values  less THAN ( +  ) tablespace cus_ts01, PARTITION P2   VALUES  less THAN (2000   

To view the partition syntax:

　select * from User_tab_partitions; --Query all partition conditions, can be connected to where table_name= ' sale ' view partitioned table structure

　　SELECT * FROM sale partition (p1); --Querying a table for a partitioned data

After partitioning, the Sale_count field of the new data is stored in the P1 partition if it is less than 1000, if 1000 to 2000 is stored in the P2 partition.

However, if the Sale_count field value of one of our new data is greater than 2000, it cannot be stored in the table.

We can extend the partition with the following syntax:

　　ALTER TABLE SALE ADD partition P4 values less than (MaxValue); --More than 2000 will be stored in this partition, of course, you can add more partitions

You can also delete a partition with the following syntax:

　　ALTER TABLE Sale drop partition P4; --NOTE: Deleting a partition will delete the data already in the partition

However, there is still a problem, if the update partition P1 in the Sale_count value of 1500, is not successful, you need to add the following statement before the update:

　　ALTER TABLE sale enable row movement; --Enables the row to move

So we can do it again with the update.

Partition Index

After partitioning, although it can improve the efficiency of the query, but also only to improve the scope of the data, so we need to establish a partitioned index if necessary, so as to further improve efficiency.

Partitioned indexes are broadly divided into two broad categories, called local, and a class called Global.

Local: Index on each partition (typically this way)

Global: An index that is globally indexed, in a way that is not partitioned, and generally does not use

Here's a syntax demonstration:

Note: The index established on the partition must be a partition field

CREATE index Idx_count on sale (sale_count) local; --Create a partitioned index that is indexed on each partition of the sale table

select * from User_ind_partitions; --Query all partition indexes

Global index globally The notation is to replace the above local with global, but not

Sometimes, if your partition is divided into 0~1000,1000~2000, then if you say that the data in the 0~1500 range is frequently queried, and after 1500 queries are rare, you can use this custom global index to index the 0~1500. After setting MaxValue, syntax is similar to partition syntax

Global Custom globally indexed mode (prefix index):

CREATE index idxname on table (field) global

Partition by range (field)

(

Partition P1 values less than (value), ....

Partition PN values less than (MaxValue)

);

Additional zoning Information

1.hash Partitioning

Hash partition to achieve uniform load value allocation, increase the hash partition can redistribute data, simple understanding is the partition of the direct average distribution

CREATE TABLE SALE (    VARCHAR2(5),    number (ten,2     by HASH (product_id) (    PARTITION P1,    PARTITION P2)

2.list Partitioning

This partition is characterized by a column with only a few values, based on which we can take the list partition.

CREATE  TABLElisttable (IDINT  PRIMARY  KEY, nameVARCHAR( -), areaVARCHAR(Ten)) PARTITION byLIST (area) (PARTITION part1VALUES('Guangdong','Beijing') tablespace part1_tb, PARTITION part2VALUES('Shanghai','Nanjing') tablespace PART2_TB);

3. Composite partitioning (not much used)

Create TableStudent (Sno Number, Snamevarchar2(Ten)) partition byRange (SNO) subpartition byHash (sname) subpartitions4(Partition P1ValuesLess Than ( +), partition P2ValuesLess Than ( -), Partition P3ValuesLess than (MaxValue));

The composite partition is basically divided into three partition p1,p2,p3, then the hash partition inside each partition, divided into 4 parts

Query sub-partition statement:SELECT * from user_tab_subpartitions where table_name= ' student ';

4. Interval partitioning (common in work)

is a partitioning automation partition that can specify time intervals for partitioning, which is a new feature of oracle11g and is often used in practical work. is actually derived from the range partition, which finally enables the automation of the range partition.

Create Table  inttimestamp by Range (sdate) interval (numtoyminterval (1 ,'MONTH'values less than (TIMESTAMP'  2017-11-12 00:00:00.00'))

Create a partition before the specified time, and then create a partition every one months

question: If we drop the table, does the table partition still exist?

The answer is there, Oracle provides a very powerful data recovery function, there is a mechanism like Recycle Bin, after deleting the table, partition in special form still exists in user_tab_partitions, after using purge RecycleBin syntax, The Recycle Bin is emptied, and the deleted table can be recovered using the flashback table name to before drop statement.

Oracle partitioning technology improves query efficiency