Shulou(Shulou.com)06/01 Report--

I. the concept of zoning

Oracle allows tables, indexes, and indexes to organize tables into smaller slices, each of which we call partitions. The partition has its own name and storage parameters.

As shown below:

Each row of data can belong to only one partition, and the partition key determines which partition the data row belongs to. The partition key consists of one or more columns. Oracle automatically maps the DML operation of the data to the corresponding partition.

When to use partition tables:

Tables greater than 2 GB should always be considered as candidates for partitioning.

Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

When the contents of a table must be distributed across different types of storage devices.

When to use a partitioned index:

Avoid rebuilding the entire index when data is removed.

Perform maintenance on parts of the data without invalidating the entire index.

Reduce the effect of index skew caused by an index on a column with a monotonically increasing value.

Advantages of partitioning:

Partition clipping, if the data of DML operation can be located to one or some partitions, then only need to read and write to this part of the data.

Partition intelligent join avoids the scanning of full table data if only partial partitions are used in the associated query.

Easy to maintain data.

Zoning policy:

LIST, RANGE, HASH, SYSTEM, INTERVAL, reference, virtual column partition.

Table Partition Type:

Single-level partition

Combined partition

Index Partition Type:

Local partition

Global partition

Global index

Second, create a partition

Create LIST Partition

SQL > CREATE TABLE accounts (id NUMBER, account_number NUMBER, customer_id NUMBER, branch_id NUMBER, region VARCHAR (2), status VARCHAR2 (1)) PARTITION BY LIST (region) (PARTITION p_northwest VALUES ('OR',' WA'), PARTITION p_southwest VALUES ('AZ',' UT', 'NM'), PARTITION p_northeast VALUES (' NY', 'VM',' NJ'), PARTITION p_southeast VALUES ('FL' 'GA'), PARTITION p_northcentral VALUES (' SD', 'WI'), PARTITION p_southcentral VALUES (' OK', 'TX')) Table created.SQL >

Insert data

SET DEFINE OFF;Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (378326946, 894594273, 1292607495, 142784215, 'OR', 'k'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (584618757,1364130187,438768827,554439762,' WA', 'o') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (41523719, 656248010, 469613013, 1849874408, 'OR', 'o'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1484487356,1308908163,1088179442, 1201623497,' OR',') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (151951677, 2077813008, 139635883, 1324220110, 'OR', 'L'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (537989506,1256709056,5665768,1397516214,' WA', 'j') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1054752157, 511468132, 2033193426, 183536554, 'WA', 'q'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (2038555798,1761488065,1281290484, 1134105071,' OR', 'y') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1749170397, 1025971133, 1336822659, 219993587, 'OR', 'G'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1308900566248,496158144,1160914382,' OR', 'Z') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1742657481, 1697703100, 1276715127, 249769402, 'OR', 'g'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1730173163,690868494, 420676729,1001701868,' OR', 'K') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1113431343, 1306177470, 912447414, 39868117, 'OR', 'b'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1813999643,1738970302,98672809,54100636,' OR','M') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (60561891, 1229089094, 1626776049, 1373758116, 'OR', 'l'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1194099877,966075960,973792659,106417526,' WA',') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (972828393, 1964561103, 715385951, 581092182, 'AZ', 'f'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1387745036,811991623,884849418,924597781,' AZ',') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (4042014, 1008692733, 524884932, 755313715, 'NM', 'U'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (9035402,85439893,333606312,233856899,' UT', 'j') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (300439305, 809521660, 602721207, 1167124218, 'UT',' C'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1261574467,1642196402,1309279369,811128713, 'AZ',' Z') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1734634260, 1893138191, 36547218, 1696588381, 'AZ', 'd'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1393860542, 207139753, 1053973916,2015424087,' NM', 'L') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1104007962, 703835058, 1058040433, 973595416, 'NM',' X'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1829413354,1360120309,475884296,275389923, 'NM', 'o') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (644314976, 1910013550, 770148070, 1201992324, 'AZ', 'B'); Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (1479078753,501738145,2081089479,1365686422,' NM','H') Insert into SCOTT.ACCOUNTS (ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, REGION, STATUS) Values (340748485, 1276107083, 11726459, 1736071185, 'NM', 'k'); COMMIT

Query data in a partition

SQL > select count (*) from accounts; COUNT (*)-29SQL > select count (*) from accounts partition (p_northwest); COUNT (*)-16SQL > select count (*) from accounts PARTITION (p_southwest); COUNT (*)-13SQL > select count (*) from accounts PARTITION (p_northeast); COUNT (*)-0

Partition clipping test

SQL > set autotrace onSQL > select count (*) from accounts where REGION='AZ' COUNT (*)-5Execution Plan---Plan hash value: 3505378muri- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |- -- | 0 | SELECT STATEMENT | | 1 | 3 | 14 (0) | 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | 2 | PARTITION LIST SINGLE | | 5 | 15 | 14 (0) | 00:00:01 | KEY | KEY | | * 3 | TABLE ACCESS FULL | ACCOUNTS | 5 | 15 | 14 (0) | 00:00:01 | 2 | 2 |-| -

As you can see from the execution plan, Oracle queries data for only one partition.

Create RANGE Partition

CREATE TABLE SALES (PROD_ID NUMBER NOT NULL, CUST_ID NUMBER NOT NULL, TIME_ID DATE NOT NULL, CHANNEL_ID NUMBER NOT NULL, PROMO_ID NUMBER NOT NULL, QUANTITY_SOLD NUMBER) NOT NULL AMOUNT_SOLD NUMBER (NOT NULL 2) PARTITION BY RANGE (TIME_ID) (PARTITION SALES_Q1_2003 VALUES LESS THAN (TO_DATE ('2003-04-01 00 TIME_ID),' YYYY-MM-DD HH24:MI:SS')), PARTITION SALES_Q2_2003 VALUES LESS THAN (TO_DATE ('2003-07-01 00 PARTITION BY RANGE),' YYYY-MM-DD HH24:MI:SS')) PARTITION SALES_Q3_2003 VALUES LESS THAN (TO_DATE ('2003-10-01 00 YYYY-MM-DD HH24:MI:SS')), PARTITION SALES_Q4_2003 VALUES LESS THAN (TO_DATE (' 2004-01-01 00 YYYY-MM-DD HH24:MI:SS')

11g adds a new interval partition, which is essentially an extension of the RANGE partition.

CREATE TABLE SALES2 (PROD_ID NUMBER NOT NULL, CUST_ID NUMBER NOT NULL, TIME_ID DATE NOT NULL, CHANNEL_ID NUMBER NOT NULL, PROMO_ID NUMBER NOT NULL, QUANTITY_SOLD NUMBER) NOT NULL, AMOUNT_SOLD NUMBER (10Magne2) NOT NULL) PARTITION BY RANGE (TIME_ID) INTERVAL (NUMTOYMINTERVAL (3) 'MONTH')) (PARTITION SALES_Q1_2003 VALUES LESS THAN (TO_DATE (' 2003-04-01 00 TO_DATE, 'YYYY-MM-DD HH24:MI:SS')

Interval partitions automatically maintain partitions based on data. We do the following data insertion

SET DEFINE OFF;Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (15,519, TO_DATE ('01hand) 20030000 MM/DD/YYYY HH24:MI:SS'), 3,999,1, 1003.49) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (32, 12824, TO_DATE ('03Compact 13A 2003 00lad 0015),' MM/DD/YYYY HH24:MI:SS'), 3,999,173.43) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (47, 3289, TO_DATE ('01Compact 15and200300lace,' MM/DD/YYYY HH24:MI:SS'), 4,999,1,29.89) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (116,116,14,35) Values ('09Compact 23 and 2003,999,1,14.35) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (48, 1580, TO_DATE ('12 Compact 24, 2003 00 Groupe, 'MM/DD/YYYY HH24:MI:SS'), 3,999,1,13.13) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (19, 2671, TO_DATE ('03Compact 16 lemon 2003 0000 Groupe,' MM/DD/YYYY HH24:MI:SS'), 4,999,152.4) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (142,6834, TO_DATE ('02Compact 200300lace 00lace,' MM/DD/YYYY HH24:MI:SS'), 3,999,1,25.4) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (33, 5579, TO_DATE ('07Gram23) 2003 0000Ganghe,' MM/DD/YYYY HH24:MI:SS'), 2,999,1,45.71) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (35, 10985, TO_DATE ('0820, TO_DATE) 2003 0000,' MM/DD/YYYY HH24:MI:SS'), 3,999,159.78) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (28, 13125, TO_DATE ('10, 13, 2003, 00, 00, MM/DD/YYYY HH24:MI:SS'), 2,999, 1, 203.18) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (46, 6719, TO_DATE ('10pm, 20030000),' MM/DD/YYYY HH24:MI:SS'), 3,999,1,25.83) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (23, 33729, TO_DATE ('01Compact 16-hand 2003 00-00-12),' MM/DD/YYYY HH24:MI:SS'), 2,999,1,23.51) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (23, 9927, TO_DATE) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (118,818, TO_DATE ('01Compact 24Compact 2003 00Groupe,' MM/DD/YYYY HH24:MI:SS'), 2,999,1,8.86) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (135, 5860, TO_DATE ('02Compact 27 and 2003 0012,' MM/DD/YYYY HH24:MI:SS'), 3,999,1,55.43) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (41, 2292, TO_DATE) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (30, 7859, TO_DATE ('07Compact 14Compact 2003 0000Groupe,' MM/DD/YYYY HH24:MI:SS'), 3,999,1,10.48) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (128, 3266, TO_DATE ('07Gram15and20030000) MM/DD/YYYY HH24:MI:SS'), 3,999,1,30.07) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (145,3461, TO_DATE ('10pm, 20030000,' MM/DD/YYYY HH24:MI:SS'), 3,999,1,13.76) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (24, 9170, TO_DATE ('02Compa' 07A) 2003 0012 ('MM/DD/YYYY HH24:MI:SS'), 3,351,1,63.97) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (118,499, TO_DATE) ('01and29and2003001500,' MM/DD/YYYY HH24:MI:SS'), 4,999,1,17.53) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (26, 11267, TO_DATE ('06Compact 10 and 2003 MM/DD/YYYY HH24:MI:SS'), 2,999, 1, 156.71) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (31, 8630, TO_DATE ('04Compact 02Compact 2003 00lace,' MM/DD/YYYY HH24:MI:SS'), 3,999,1,8.95) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (23,392, TO_DATE ('08Compact 30A 2003 00lace,' MM/DD/YYYY HH24:MI:SS'), 4,999,1,21.22) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (47,280, TO_DATE ('0813), 2003 0012,' MM/DD/YYYY HH24:MI:SS'), 2,999,1,29.02) Insert into SALES2 (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD) Values (119,6822, TO_DATE ('08Compact 06) 2003 0012 (MM/DD/YYYY HH24:MI:SS'), 3,999,1,7.31); COMMIT

Now let's take a look at the partition of sales2

SQL > SELECT TABLE_NAME, PARTITION_NAME FROM dba_tab_partitions WHERE TABLE_OWNER = 'SCOTT' AND TABLE_NAME =' SALES2' TABLE_NAME PARTITION_NAME---SALES2 SALES_Q1_2003SALES2 SYS_P41SALES2 SYS_P42SALES2 SYS_P43

It can be seen that the system has created three new partitions for us according to the actual situation of the data, and the partitions are named by the system itself.

Create HASH Partition

CREATE TABLE sales_hash (s_productid NUMBER, s_saledate DATE, s_custid NUMBER, s_totalprice NUMBER) PARTITION BY HASH (s_productid) (PARTITION p1 TABLESPACE users, PARTITION p2 TABLESPACE users, PARTITION p3 TABLESPACE users, PARTITION p4 TABLESPACE users)

Based on the value of s_productionid, the hash value is calculated, and the data rows for are inserted into the corresponding partition. If the data of each partition is to be uniform, the number of partitions is the power of 2.

Create SYSTEM Partition

This partition is different from the others, which partition the data is inserted into has nothing to do with the data itself, and is completely user-defined.

CREATE TABLE t_system (id NUMBER, name VARCHAR2 (30)) PARTITION BY SYSTEM (PARTITION p1, PARTITION p2, PARTITION p3, PARTITION p4)

At this point, inserting data directly into the table will report an error.

SQL > insert into t_system (id,name) values (1meme Kevin'); insert into t_system (id,name) values (1Med Kevin') * ERROR at line 1:ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method

Insert data, you must specify a partition name

SQL > insert into t_system partition (p1) (id,name) values (1 row created.

And duplicate data can be inserted into different partitions

SQL > insert into t_system partition (p2) (id,name) values (1 row created).

Create a virtual column partition

Oracle11g added the virtual column feature, the value of the virtual column is derived from other columns, Oracle only keeps the source data, and this column does not take up storage space. One of the extended functions of virtual columns is the virtual column partitioning function. 11g adds support for virtual columns, which makes the partitioning feature more flexible. For example, there is a date column in the table, and you want to partition according to the date column, one partition per month, a total of 12 partitions, no matter what year it is, as long as it is that month, put it in the partition of that month.

CREATE TABLE T_PARTITION_MONTH (ID NUMBER, NAME VARCHAR2 (30), CREATE_DATE DATE, PARTITION_MONTH AS (TO_NUMBER (TO_CHAR (CREATE_DATE, 'MM')) PARTITION BY LIST (PARTITION_MONTH) (PARTITION P1 VALUES (1), PARTITION P2 VALUES (2), PARTITION P3 VALUES (3), PARTITION P4 VALUES (4), PARTITION P5 VALUES (5)) PARTITION P6 VALUES (6), PARTITION P7 VALUES (7), PARTITION P8 VALUES (8), PARTITION P9 VALUES (9), PARTITION P10 VALUES (10), PARTITION P11 VALUES (11), PARTITION P12 VALUES (12))

Create a reference column partition

The partition of the child table depends on the parent table. Let's take a look at the following example

CREATE TABLE orders (order_id NUMBER (12), order_date TIMESTAMP, order_mode VARCHAR2 (8), customer_id NUMBER (6), order_status NUMBER (2), order_total NUMBER (8), sales_rep_id NUMBER (6), promotion_id NUMBER (6) CONSTRAINT orders_pk PRIMARY KEY (order_id)) PARTITION BY RANGE (order_date) (PARTITION Q1 2005 VALUES LESS THAN (TO_DATE)), PARTITION Q2 2005 VALUES LESS THAN (TO_DATE), PARTITION Q3 2005 VALUES LESS THAN (TO_DATE)) PARTITION Q4001 2005 VALUES LESS THAN (TO_DATE ('01MurJANMI 2006)

Order table, partition the range according to order_date.

CREATE TABLE order_items (order_id NUMBER (12) NOT NULL, line_item_id NUMBER (3) NOT NULL, product_id NUMBER (6) NOT NULL, unit_price NUMBER (8), quantity NUMBER (8), CONSTRAINT order_items_fk FOREIGN KEY (order_id) REFERENCES orders (order_id)) PARTITION BY REFERENCE (order_items_fk)

Order item table, which is partitioned to refer to the main table.

View the partition information of order_items

SQL > SELECT TABLE_NAME, PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'ORDER_ITEMS' TABLE_NAME PARTITION_NAME---ORDER_ITEMS Q1_2005ORDER_ITEMS Q2_2005ORDER_ITEMS Q3_2005ORDER _ ITEMS Q4_2005

For range and hash partitions, you can specify multiple columns as partition keys. The maximum number of columns is 16.

CREATE TABLE sales_demo (year NUMBER, month NUMBER, day NUMBER, amount_sold NUMBER) PARTITION BY RANGE (year,month) (PARTITION before2001 VALUES LESS THAN (2001 VALUES LESS THAN 1), PARTITION Q1 (2001 VALUES LESS THAN), PARTITION Q2 (2001 VALUES LESS THAN (2001) 7), PARTITION Q3 (2001 VALUES LESS THAN (2001) 10), PARTITION Q4 (2001 VALUES LESS THAN (2002), PARTITION future VALUES LESS THAN (MAXVALUE,0) REM 12-DEC-2000INSERT INTO sales_demo VALUES (2000 ~ 12, 1000); REM 17-MAR-2001INSERT INTO sales_demo VALUES (2001 ~ 3, 17, 2000); REM 1-NOV-2001INSERT INTO sales_demo VALUES (2001 ~ 11, 1, 5000); REM 1-JAN-2002INSERT INTO sales_demo VALUES (2002, 1, 4000)

There are six types of combined zones:

RANGE-RANGE 、 RANGE-LIST 、 RANGE-HASH 、 LIST-RANGE 、 LIST-LIST 、 LIST-HASH .

The creation of a combined partition is similar to that of a single partition.

Create a RANGE-RANGE composite partition

CREATE TABLE shipments (order_id NUMBER NOT NULL, order_date DATE NOT NULL, delivery_date DATE NOT NULL, customer_id NUMBER NOT NULL, sales_amount NUMBER NOT NULL) PARTITION BY RANGE (order_date) SUBPARTITION BY RANGE (delivery_date) (TO_DATE ('01combe 2006)) (SUBPARTITION p06_jul_e VALUES LESS THAN (TO_DATE) SUBPARTITION p06_jul_a VALUES LESS THAN (TO_DATE), SUBPARTITION p06_jul_l VALUES LESS THAN (MAXVALUE)), PARTITION p_2006_aug VALUES LESS THAN (SUBPARTITION p06_aug_e VALUES LESS THAN (TO_DATE) SUBPARTITION p06_aug_a VALUES LESS THAN (TO_DATE), SUBPARTITION p06_aug_l VALUES LESS THAN (MAXVALUE)), PARTITION p_2006_sep VALUES LESS THAN (SUBPARTITION p06_sep_e VALUES LESS THAN (TO_DATE) SUBPARTITION p06_sep_a VALUES LESS THAN (TO_DATE), SUBPARTITION p06_sep_l VALUES LESS THAN (MAXVALUE)), PARTITION p_2006_oct VALUES LESS THAN (SUBPARTITION p06_oct_e VALUES LESS THAN (TO_DATE)) SUBPARTITION p06_oct_a VALUES LESS THAN (TO_DATE), SUBPARTITION p06_oct_l VALUES LESS THAN (MAXVALUE)), PARTITION p_2006_nov VALUES LESS THAN (SUBPARTITION p06_nov_e VALUES LESS THAN (TO_DATE)) SUBPARTITION p06_nov_a VALUES LESS THAN (TO_DATE), SUBPARTITION p06_nov_l VALUES LESS THAN (MAXVALUE)), PARTITION p_2006_dec VALUES LESS THAN (SUBPARTITION p06_dec_e VALUES LESS THAN (TO_DATE)) SUBPARTITION p06_dec_a VALUES LESS THAN (SUBPARTITION p06_dec_l VALUES LESS THAN (MAXVALUE) (TO_DATE ('01murFEBly2007)

View partition information

SQL > SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'SHIPMENTS' TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT-- SHIPMENTS P_2006_JUL 3SHIPMENTS P_2006_AUG 3SHIPMENTS P_2006_SEP 3SHIPMENTS P_2006_OCT 3SHIPMENTS P_2006_NOV 3SHIPMENTS P_2006_DEC 36 rows selected.

View subpartition information

SQL > SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME = 'SHIPMENTS' TABLE_NAME PARTITION_NAME SUBPARTITION_NAME -SHIPMENTS P_2006_DEC P06_DEC_ESHIPMENTS P_2006_DEC P06_DEC_ASHIPMENTS P_2006_DEC P06_DEC_LSHIPMENTS P_2006_NOV P06_NOV_ESHIPMENTS P_2006_NOV P06_NOV_ASHIPMENTS P_2006_NOV P06_NOV_LSHIPMENTS P_2006_OCT P06_OCT_ESHIPMENTS P_2006_OCT P06_OCT_ASHIPMENTS P_2006_ OCT P06_OCT_LSHIPMENTS P_2006_SEP P06_SEP_ESHIPMENTS P_2006_SEP P06_SEP_ATABLE_NAME PARTITION_NAME SUBPARTITION_NAME-- -SHIPMENTS P_2006_SEP P06_SEP_LSHIPMENTS P_2006_AUG P06_AUG_ESHIPMENTS P_2006_AUG P06_AUG_ASHIPMENTS P_2006_AUG P06_AUG_LSHIPMENTS P_2006_JUL P06_JUL_ESHIPMENTS P_2006_JUL P06_JUL_ASHIPMENTS P_2006_JUL P06_JUL_L18 rows selected.

Create RANGE-LIST

CREATE TABLE quarterly_regional_sales (deptno number, item_no varchar2 (20), txn_date date, txn_amount number, state varchar2 (2) TABLESPACE ts4 PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state) (PARTITION Q1, 1999 VALUES LESS THAN (TO_DATE)) (SUBPARTITION q1_1999_northwest VALUES ('OR',' WA') SUBPARTITION q1_1999_southwest VALUES ('AZ',' UT', 'NM'), SUBPARTITION q1_1999_northeast VALUES (' NY', 'VM',' NJ'), SUBPARTITION q1_1999_southeast VALUES ('FL',' GA'), SUBPARTITION q1_1999_northcentral VALUES ('SD',' WI'), SUBPARTITION q1_1999_southcentral VALUES ('OK',' TX')) PARTITION q2o1999 VALUES LESS THAN (TO_DATE ('1murJULMY 1999)) (SUBPARTITION q2_1999_northwest VALUES (' OR', 'WA'), SUBPARTITION q2_1999_southwest VALUES (' AZ', 'UT',' NM'), SUBPARTITION q2_1999_northeast VALUES ('NY',' VM', 'NJ'), SUBPARTITION q2_1999_southeast VALUES (' FL' 'GA'), SUBPARTITION q2_1999_northcentral VALUES (' SD', 'WI'), SUBPARTITION q2_1999_southcentral VALUES (' OK', 'TX'), PARTITION q3yy1999 VALUES LESS THAN (TO_DATE) (TO_DATE)) (SUBPARTITION q3_1999_northwest VALUES (' OR', 'WA'), SUBPARTITION q3_1999_southwest VALUES (' AZ') 'UT',' NM'), SUBPARTITION q3_1999_northeast VALUES ('NY',' VM', 'NJ'), SUBPARTITION q3_1999_southeast VALUES (' FL', 'GA'), SUBPARTITION q3_1999_northcentral VALUES (' SD', 'WI'), SUBPARTITION q3_1999_southcentral VALUES (' OK', 'TX') PARTITION q4o1999 VALUES LESS THAN (TO_DATE ('1murJANU2000)) (SUBPARTITION q4_1999_northwest VALUES (' OR', 'WA'), SUBPARTITION q4_1999_southwest VALUES (' AZ', 'UT',' NM'), SUBPARTITION q4_1999_northeast VALUES ('NY',' VM', 'NJ'), SUBPARTITION q4_1999_southeast VALUES (' FL' 'GA'), SUBPARTITION q4_1999_northcentral VALUES (' SD', 'WI'), SUBPARTITION q4_1999_southcentral VALUES (' OK', 'TX')

Create RANGE-HASH Partition

CREATE TABLE sales (prod_id NUMBER (6), cust_id NUMBER, time_id DATE, channel_id CHAR (1), promo_id NUMBER (6), quantity_sold NUMBER (3), amount_sold NUMBER (10L2)) PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4) (PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE ('01Collector 2006') ), PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE (01murJULMYY)), PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE (OCT2006 dd-MON-yyyy')), PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE (01RJUL 2007), PARTITION sales_q3_2006 VALUES LESS THAN (OCT2006))

A subpartition can also specify a template

As follows:

CREATE TABLE sales_range_range (prod_id NUMBER (6), cust_id NUMBER, time_id DATE, channel_id CHAR (1), promo_id NUMBER (6), quantity_sold NUMBER (3), amount_sold NUMBER (10 Magi 2)) PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL (1 meme Daya') SUBPARTITION BY RANGE (amount_sold) SUBPARTITION TEMPLATE (SUBPARTITION p_low VALUES LESS THAN (1000)) SUBPARTITION p_medium VALUES LESS THAN (4000), SUBPARTITION p_high VALUES LESS THAN (8000), SUBPARTITION p_ultimate VALUES LESS THAN (maxvalue) (PARTITION before_2000 VALUES LESS THAN (TO_DATE) or CREATE TABLE sales_range_range (cust_id NUMBER, time_id DATE, channel_id CHAR (1)) Promo_id NUMBER (6), quantity_sold NUMBER (3), amount_sold NUMBER (10 Magi 2)) PARTITION BY RANGE (time_id) SUBPARTITION BY RANGE (amount_sold) SUBPARTITION TEMPLATE (SUBPARTITION p_low VALUES LESS THAN (1000), SUBPARTITION p_medium VALUES LESS THAN (4000), SUBPARTITION p_high VALUES LESS THAN (8000), SUBPARTITION p_ultimate VALUES LESS THAN (maxvalue)) (PARTITION before_2000 VALUES LESS THAN (TO_DATE ('01Mustang 2000') ) CREATE TABLE emp_sub_template (deptno NUMBER, empname VARCHAR (32), grade NUMBER) PARTITION BY RANGE (deptno) SUBPARTITION BY HASH (empname) SUBPARTITION TEMPLATE (SUBPARTITION a TABLESPACE ts1, SUBPARTITION b TABLESPACE ts2, SUBPARTITION c TABLESPACE ts3, SUBPARTITION d TABLESPACE ts4) (PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (MAXVALUE)) CREATE TABLE stripe_regional_sales (deptno number, item_no varchar2 (20), txn_date date, txn_amount number, state varchar2 (2)) PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state) SUBPARTITION TEMPLATE (SUBPARTITION northwest VALUES ('OR',' WA') TABLESPACE tbs_1, SUBPARTITION southwest VALUES ('AZ',' UT', 'NM') TABLESPACE tbs_2, SUBPARTITION northeast VALUES (' NY', 'VM') 'NJ') TABLESPACE tbs_3, SUBPARTITION southeast VALUES (' FL', 'GA') TABLESPACE tbs_4, SUBPARTITION midwest VALUES (' SD', 'WI') TABLESPACE tbs_5, SUBPARTITION south VALUES (' AL', 'AK') TABLESPACE tbs_6, SUBPARTITION others VALUES (DEFAULT) TABLESPACE tbs_7) (PARTITION Q1 September 1999 VALUES LESS THAN (TO_DATE) PARTITION Q2y1999 VALUES LESS THAN (TO_DATE (01MurJULMYY)), PARTITION Q3L1999 VALUES LESS THAN (TO_DATE), PARTITION Q4q1999 VALUES LESS THAN (TO_DATE)

Create a partitioned index

Local index

SQL > create index idx_accounts_number on accounts (account_number) local;Index created.

When you create an index, add the local keyword, which creates a separate index for each partition of the table.

SQL > select index_name,partition_name from dba_ind_partitions where index_name='IDX_ACCOUNTS_NUMBER' INDEX_NAME PARTITION_NAME---IDX_ACCOUNTS_NUMBER P_NORTHCENTRALIDX_ACCOUNTS_NUMBER P_NORTHEASTIDX_ACCOUNTS_NUMBER P_NORTHWESTIDX_ACCOUNTS_NUMBER P_SOUTHCENTRALIDX_ACCOUNTS_NUMBER P_SOUTHEASTIDX_ACCOUNTS_NUMBER P_SOUTHWEST6 rows selected.

As you can see, if the table has several partitions, create several index partitions.

Global index

The creation method is the same as the normal index.

SQL > create index idx_accounts_id on accounts (id); Index created.SQL > select index_name,partition_name from dba_ind_partitions where index_name='IDX_ACCOUNTS_ID';no rows selected

The index has no partition.

Global partition index

SQL > create index idx_accounts_customer_id on accounts (customer_id) global partition by hash (customer_id) partitions 4 * * Index created.SQL > select index_name,partition_name from dba_ind_partitions where index_name='IDX_ACCOUNTS_CUSTOMER_ID' INDEX_NAME PARTITION_NAME---IDX_ACCOUNTS_CUSTOMER_ID SYS_P61IDX_ACCOUNTS_CUSTOMER_ID SYS_P62IDX_ACCOUNTS_CUSTOMER_ID SYS_P63IDX_ACCOUNTS_CUSTOMER_ID SYS_P64

Third, maintain the zoning

Add a partition

LIST partition

SQL > ALTER TABLE accounts ADD PARTITION p_nonmainland VALUES ('HI',' PR'); Table altered.SQL >

RANGE partition

SQL > ALTER TABLE sales ADD PARTITION sale_q1_2004 VALUES LESS THAN (TO_DATE ('2004-04-01 00 TO_DATE,' YYYY-MM-DD HH24:MI:SS')); Table altered.SQL >

HASH partition

SQL > ALTER TABLE sales_hash ADD PARTITION; Table altered.

For combined partitions

SQL > ALTER TABLE shipments ADD PARTITION p_2007_jan VALUES LESS THAN (TO_DATE), Table altered.

If you do not specify a subpartition when you add a partition, the system creates a subpartition for you by default

SQL > SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME = 'SHIPMENTS' and PARTITION_NAME='P_2007_JAN' TABLE_NAME PARTITION_NAME SUBPARTITION_NAME -SHIPMENTS P_2007_JAN SYS_SUBP82

When you add a partition, you can also specify a subpartition

SQL > ALTER TABLE shipments ADD PARTITION p_2008_jan VALUES LESS THAN (TO_DATE ('01murafouyyyy')) (SUBPARTITION p07_jan_e VALUES LESS THAN (TO_DATE)), SUBPARTITION p07_jan_a VALUES LESS THAN (TO_DATE (' 01murmur2007'), TO_DATE ('01Murmury 2007)) SUBPARTITION p07_jan_l VALUES LESS THAN (TO_DATE ('01PAPRMY')) Table altered.

At this time, the sub-partition information is as follows

SQL > SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME = 'SHIPMENTS' and PARTITION_NAME='P_2008_JAN' TABLE_NAME PARTITION_NAME SUBPARTITION_NAME -- SHIPMENTS P_2008_JAN P07_JAN_ESHIPMENTS P_2008_JAN P07_JAN_ASHIPMENTS P_2008_JAN P07_JAN_L

If a template is specified for a subpartition of a combined partition, you do not need to specify a subpartition when you add a partition.

SQL > ALTER TABLE SALES_RANGE_RANGE ADD PARTITION Play2000 VALUES LESS THAN (TO_DATE ('01MJANMY 2001 VALUES LESS THAN,' dd-MON-yyyy')); Table altered.

View partition information

SQL > SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME = 'SALES_RANGE_RANGE' and PARTITION_NAME='P_2000' TABLE_NAME PARTITION_NAME SUBPARTITION_NAME -SALES_RANGE_RANGE packs 2000 P_2000_P_LOWSALES_RANGE_RANGE packs 2000 P_2000_P_MEDIUMSALES_RANGE_RANGE packs 2000 P_2000_P_HIGHSALES_RANGE_RANGE packs 2000 P_2000_P_ULTIMATE

Delete partition

Use one of the following statements to drop a table partition or subpartition:

ALTER TABLE... DROP PARTITION to drop a table partition

ALTER TABLE... DROP SUBPARTITION to drop a subpartition of a composite *-[range | list] partitioned table

SQL > ALTER TABLE SALES DROP PARTITION SALE_Q1_2004; Table altered.

Delete subpartition

SQL > alter table sales_range_range drop subpartition table altered 2000.

Hash partition cannot be deleted

SQL > ALTER TABLE SALES_HASH DROP PARTITION SYS_P81;ALTER TABLE SALES_HASH DROP PARTITION SYS_P81 * ERROR at line 1:ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method

Hash partitions need to be merged with the COALESCE keyword

SQL > SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_HASH' 2 3 TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT---SALES_HASH P1 0SALES_HASH P2 0SALES_HASH P3 0SALES_HASH P4 0SALES_HASH SYS_P81 0SQL > ALTER TABLE SALES_HASH COALESCE PARTITION Table altered.SQL > SELECT TABLE_NAME, PARTITION_NAME,SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_HASH' 2 3 TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT---SALES_HASH P1 0SALES_HASH P2 0SALES_HASH P3 0SALES_HASH P4 0

Delete the hash subpartition with the following syntax

ALTER TABLE diving MODIFY PARTITION us_locations COALESCE SUBPARTITION

Zone switching

Exchange partition is used to convert partitioned tables to regular tables. But a regular table can only be swapped with a partition in the partition table. You can't simply turn a normal table into a partitioned table.

CREATE TABLE t (empno NUMBER, sal NUMBER) PARTITION BY RANGE (sal) (PARTITION sal_less_3000 VALUES LESS THAN (3000), PARTITION sal_between_3000_5000 VALUES LESS THAN (5000))

Generate two ordinary tables

SQL > create table t_part1 as select empno,sal from emp where sal create table t_part2 as select empno,sal from emp where sal > = 3000 and sal COL OBJECT_NAME FOR A30SQL > COL SUBOBJECT_NAME FOR A30SQL > select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_name in OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID -T SAL_LESS_3000 88777 88777T 88776T_PART2 88775 88775T_PART1 88774 88774T SAL_BETWEEN_3000_5000 88778 88778

Conduct partition swapping

SQL > alter table t exchange partition sal_less_3000 with table tweak part1: table altered.SQL > alter table t exchange partition sal_between_3000_5000 with table tweak part2: table altered.SQL > select count (*) from t; COUNT (*)-26

Check data_object_id again

SQL > select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_name in ('Turing, recording, writing, etc.) OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID -T SAL_LESS_3000 88777 88774T 88776T_PART2 88775 88778T_PART1 88774 88777T SAL_BETWEEN_3000_5000 88778 88775

It can be seen that the data_object_id of t_part1 and t_part2 is swapped with the value of the partition. Swap partition execution modifies the contents of the data dictionary. It's very efficient.

Subpartitions can also be exchanged, please refer to the official documentation.

Partition merging

Merge two partitions into one partition, with the exception of the hash partition

SQL > alter table accounts merge partitions p_northcentral, p_southcentral into partition paired centralistTable altered.

If it is an interval partition, you can use the following syntax

ALTER TABLE transactionsMERGE PARTITIONS FOR (TO_DATE), FOR (TO_DATE)), TO_DATE

Partition split

LIST partition

ALTER TABLE sales_by_region SPLIT PARTITION region_east VALUES ('CT',' MA', 'MD') INTO (PARTITION region_east_1 TABLESPACE tbs2, PARTITION region_east_2 STORAGE (INITIAL 8m)) PARALLEL 5

The literal value list for the original region_east partition was specified as:

PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')

The two new partitions are:

Region_east_1 with a literal value list of ('CT','MA','MD')

Region_east_2 inheriting the remaining literal value list of ('NY','NH','ME','VA','PA','NJ')

Range partition

ALTER TABLE vet_cats SPLIT PARTITION fee_katy at (100) INTO (PARTITION fee_katy1, PARTITION fee_katy2)

Assuming that the values of fee_katy partitions are [90-110], then the values of the two newly split partitions are fee_katy1= [90-100) and fee_katy2= [100-110).

Time interval partition

ALTER TABLE transactions SPLIT PARTITION FOR (TO_DATE ('01MAYPUR 2007)) AT (TO_DATE))

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.