Oracle Partition Partition Detailed summary ____oracle

Oracle Partition Partition Detailed summary
This article from the following aspects to collate the concept and operation of the partition table:         1. Table space and the concept of partitioned tables         2. Table partitioning specific role     &N Bsp   3. Table partitioning advantages and disadvantages         4. Table partitioning of several types and operating methods         5. Maintenance Operations on table partitions. (1) Table space and partition table concept table space: is a collection of one or more data files, all data objects are stored in the specified table space, but the main storage is the table, so called table space. Partitioned tables: When the amount of data in a table grows, the query data slows down, and the application's performance drops, 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 when querying the data, it does not scan the entire table every time.   (2). The specific role of table partitioning Oracle's Table partitioning feature provides great benefits for a wide variety of applications by improving manageability, performance, and availability. In general, partitioning can greatly improve the performance of certain queries and maintenance operations. In addition, partitioning can greatly simplify common administrative tasks, and partitioning is a key tool for building a gigabit data system or a hyper-high-availability system. The   partitioning feature can further subdivide a table, index, or index organization table into segments, which are segments called partitions. Each partition has its own name, and you can choose your own storage attributes. From a database administrator's point of view, an object with a partition has multiple segments, which can be managed collectively or separately, giving the database administrator considerable flexibility in managing the objects after the partition. However, from an application perspective, the partitioned table is exactly the same as a partitioned table, and the SQL DML command is used to access the partitioned table without any modification.   When to use the partition table: 1, the size of the table more than 2GB. 2, the table contains historical data, new data is added to the new partition.     (3). Table partitioning advantages and disadvantages table partitioning has the following benefits: 1, improve query performance: Query on partitioned objects can only search their own areas of concern, improve the speed of retrieval. 2. Enhance usability: If one of the table's partitions fails, the table's data in other partitions is still available; 3. Easy to maintain: if a partition of a table fails, the data needs to be repaired, and only the partition can be repaired; 4. Balanced I/O: can map different partitions to disk to balance I/O to improve overall system performance.   Cons: Partitioned table Related: Existing tables do not have methods that can be directly converted into partitioned tables. However, Oracle   provides the ability to redefine tables online.   (4). Several types of table partitions and their operating methods   I. Range Partitioning: Range partitioning maps data to each partition based on scope, which is determined by the partitioning key that you specify when you create the partition. This partitioning method is most commonly used, and the partitioning key often takes a date. For example, you might be able to partition sales data by month. When using range partitioning, consider the following rules: 1. Each partition must have a values less then clause that specifies an upper bound value that is not included in the partition. Any record of a partition key that is equal to or greater than this upper bound value is added to the next higher partition. 2. All partitions, except the first one, will have an implicit lower bound value, which is the upper bound value of the previous partition of this partition. 3, in the highest partition, MaxValue is defined. The MaxValue represents an indeterminate value. This value is higher than any partition key in other partitions, and can be understood to be higher than the value less then specified in any partition, including null values. Example one: Suppose there is a customer table with 200000 rows of data, we partition the table through customer_id, each partition stores 100000 rows, and we save each partition in a separate tablespace so that the data file can span multiple physical disks. The following are the code to create tables and partitions, as follows: Create TABLE CUSTOMER (    customer_id number not NULL PRIMARY KEY,     FIRST_NAME&N Bsp VARCHAR2 NOT NULL,     last_name   VARCHAR2 (a) not NULL,     PHONE   &N bsp;    VARCHAR2 () not NULL,     email        VARCHAR2 (     status      CHAR (1)) PARTITION by RANGE (customer_id) (    PARTITION cus_part1 VALUES less THAN (100000) tablespace     PARTITION Cus_part2 VALUES less THAN (200000) tablespace Cus_ts02) Example two: CREATE TABLE by Time Order_activiti ES (    order_id      number (7) Not NULL,     order_date    DATE,     total_amount number,     custotmer_id number (7),     paid            CHAR (1)) PARTITION by RANGE (order_date) (  PARTITION ord_act_part01 VALUES LE SS THAN (to_date (' 01-may-2003 ', ' dd-mon-yyyy ')) tablespaceord_ts01,   PARTITION ord_act_part02 VALUES less THAN (to_ DATE (' 01-jun-2003 ', ' dd-mon-yyyy ')) tablespace ord_ts02,   PARTITION ord_act_part02 VALUES less THAN (' 01- JUL-2003 ', ' dd-mon-yyyy ') tablespace Ord_ts03) example three: MAXVALUE CREATE TABLE rangetable (  idd   INT PRIMARY K EY,   Iname VARCHAR (10),   Grade INT) partition  by  RANGE (grade)       partition  part1 values  less& nbsp THEN (1000) tablespace  part1_tb,       partition  part2 values  less  THEN (MAXVALUE) tablespace  PART2_TB);   two. List partition: The feature of the partition is that there are only a few values in a column, so we can take a list partition based on this feature. Example a CREATE TABLE problem_tickets (    problem_id   number (7) Not NULL PRIMARY KEY,     DESCR iption  VARCHAR2,     customer_id  number (7) Not NULL,     date_entered DATE NOT NULL,     status       VARCHAR2) PARTITION by LIST (STATUS) (      PARTITION prob_active   VALUES (' ACTIVE ') tablespace prob_ts01,       PARTITION prob_inactive VA Lues (' INACTIVE ') tablespace prob_ts02 case two create  table  listtable (    id    INT&nbsp ; primary  KEY,     name  VARCHAR (20),    area  VARCHAR ()) partition  by  LIST (area) (    partition  part1 VALUES (' Guangdong ', ' Beijing ') tablespace  PART1_TB,     partition  part2 VALUES (' Shanghai ', ' Nanjing ')   tablespace  PART2_TB);   Three. Hash partitioning: This type of partition uses a hashing algorithm on the column value to determine which partition to put the row into. A hash partition is recommended when the value of a column does not have an appropriate condition. A hash partition is a type of partition that distributes data evenly by specifying the partition number, because these partitions are identical in size by hashing partitions on the I/O device. Example one: CREATE TABLE hash_table (  COL number (8),   INF VARCHAR2 (MB)) PARTITION by HASH (COL) (  PARTITION PART01 tablespace hash_ts01,   PARTITION PART02 tablespace hash_ts02,   PARTITION PART03 tablespace hash_ts03) Shorthand: CREATE TABLE emp (    EMPNO number (4),     ename VARCHAR2 (),     sal   NU mber) PARTITION by  HASH (empno) Partitions 8 STORE in (EMP1,EMP2,EMP3,EMP4,EMP5,EMP6,EMP7,EMP8);   Hash partition The most important mechanism is based on the hash algorithm to calculate a specific record should be inserted into which partition, the hash algorithm is the most significant hash function, Oracle if you want to use the hash partition, you can only specify the number of partitions. It is recommended that the number of partitions be 2 n-th, so that data can be distributed across partitionsMore evenly.   Four. Combined range hash partition this partition is based on a range partition and a list partition, the table is first scoped by a column, then the list is partitioned by a column, and the partitions in the partition are called sub partitions. CREATE TABLE SALES (product_id VARCHAR2 (5), sales_date DATE, sales_cost number (), STATUS VARCHAR2) PARTITION by RA NGE (sales_date) subpartition by LIST (STATUS)    partition P1 VALUES less THAN (to_date (' 2003-01-01 ', ' yyyy-mm- DD ')) tablespace rptfact2009           (              Subparti tion p1sub1 VALUES (' ACTIVE ') tablespace rptfact2009,               Subpartition P1SUB2 VALUES (' INACTIVE ') tablespace rptfact2009          ),    partition P2 VALUES less TH An (to_date (' 2003-03-01 ', ' yyyy-mm-dd ')) tablespace rptfact2009           (      & nbsp       subpartition p2sub1 VALUES (' ACTIVE ') tablespace rptfact2009,       &NB