Oracle 12c Study之-- 分区表新特性

我会带着你远行 2022-09-26 00:25 245阅读 0赞

**Oracle 12c Study之-- 分区表新特性**

**数据库版本：**

**12:05:07 SYS@prod>select \* from v$version;**

BANNER                                                      CON\_ID

\------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release12.1.0.2.0 - 64bit Production      0

PL/SQL Release 12.1.0.2.0 - Production                           0

CORE   12.1.0.2.0      Production                               0

TNS for Linux: Version 12.1.0.2.0 - Production                    0

NLSRTL Version 12.1.0.2.0 - Production                            0

Elapsed: 00:00:00.03

**一、   参考性分区新特性**

**在参考性分区增加间隔分区的功能，当父表增加新的分区时，子表扩展分区时，也会扩展同样的分区。**

**案例：   **

11:56:33 SCOTT@ prod>**create table new\_orders                                                                           **

**12:03:04   2   partition by range (order\_date)                                                                        **

**12:03:04   3   INTERVAL(NUMTOYMINTERVAL(1, 'month')) store in(tbs1,tbs2,tbs3,tbs4)                                    **

**12:03:04   4   (partition p1 values less than (to\_date('2005-04-01','yyyy-mm-dd'))tablespace tbs1,                   **

**12:03:04   5   partition p2 values less than (to\_date('2005-07-01','yyyy-mm-dd'))tablespace tbs2,                    **

**12:03:04   6   partition p3 values less than (to\_date('2005-10-01','yyyy-mm-dd'))tablespace tbs3,                    **

**12:03:04   7   partition p4 values less than (to\_date('2006-01-01','yyyy-mm-dd'))tablespace tbs4)                    **

**12:03:04   8   as                                                                                                     **

**12:03:04   9   select \* from orders;        **

Table created.

**插入数据：**

insert into new\_ordersvalues(101,to\_date('2005-02-03','yyyy-mm-dd'),'x1',1001,1,20000,88001,88002);

insert into new\_ordersvalues(102,to\_date('2005-05-15','yyyy-mm-dd'),'x1',1002,1,30000,88002,88002);

insert into new\_orders values(103,to\_date('2005-09-22','yyyy-mm-dd'),'x1',1003,1,40000,88003,88002);

insert into new\_ordersvalues(104,to\_date('2005-12-30','yyyy-mm-dd'),'x1',1004,1,10000,88004,88002);

12:06:24 SCOTT@ prod>col partition\_name fora20

12:06:37 SCOTT@ prod>**select segment\_name,segment\_type,PARTITION\_NAME,TABLESPACE\_NAME fromuser\_segments **

**wheresegment\_name='NEW\_ORDERS' **

SEGMENT\_NAME        SEGMENT\_TYPE      PARTITION\_NAME      TABLESPACE\_NAME

\-------------------- ------------------ --------------------

NEW\_ORDERS          TABLE PARTITION    P1                   TBS1

NEW\_ORDERS          TABLE PARTITION    P2                   TBS2

NEW\_ORDERS          TABLE PARTITION    P3                   TBS3

NEW\_ORDERS          TABLE PARTITION    P4                   TBS4

**在父表上建立主键:**

12:06:37 SCOTT@ prod>**alter table new\_orders add constraint pk\_new\_orders primarykey(order\_id); **

Table altered.

**建立参考分区：**

12:08:06 SCOTT@ prod>**CREATE TABLE order\_items**

**12:09:35   2      ( order\_id           NUMBER(12)NOT NULL,**

**12:09:35   3        line\_item\_id       NUMBER(3)  NOT NULL,**

**12:09:35   4        product\_id         NUMBER(6)  NOT NULL,**

**12:09:35   5        unit\_price         NUMBER(8,2),**

**12:09:35   6        quantity           NUMBER(8),**

**12:09:35   7        CONSTRAINT order\_items\_fk**

**12:09:35   8        FOREIGN KEY(order\_id) REFERENCESnew\_orders(order\_id)**

**12:09:35   9      )**

**12:09:35  10   PARTITION BY REFERENCE(order\_items\_fk);**

Table created.

**插入测试数据：**

insert into order\_itemsvalues(101,801,200001,200,100);

insert into order\_itemsvalues(102,802,200002,300,100);

insert into order\_itemsvalues(103,803,200003,400,100);

insert into order\_itemsvalues(104,804,200004,100,100);

**12:10:39 SCOTT@ prod>select\* from order\_items;**

ORDER\_IDLINE\_ITEM\_ID PRODUCT\_ID UNIT\_PRICE  QUANTITY

\---------- ------------ ---------- --------------------

101          801     200001        200        100

102          802     200002       300        100

103          803     200003        400        100

104          804     200004        100        100

Elapsed: 00:00:00.34

12:12:46 SCOTT@ prod>**select segment\_name,segment\_type,PARTITION\_NAME,TABLESPACE\_NAME fromuser\_segments**

12:12:53  2  **where segment\_name in ('NEW\_ORDERS','ORDER\_ITEMS');**

SEGMENT\_NAME        SEGMENT\_TYPE      PARTITION\_NAME      TABLESPACE\_NAME

\-------------------- --------------------------------------

ORDER\_ITEMS         TABLE PARTITION    P1                   TBS1

NEW\_ORDERS          TABLE PARTITION    P1                   TBS1

ORDER\_ITEMS         TABLE PARTITION    P3                   TBS3

ORDER\_ITEMS         TABLE PARTITION    P2                   TBS2

NEW\_ORDERS          TABLE PARTITION    P3                   TBS3

NEW\_ORDERS          TABLE PARTITION    P2                   TBS2

ORDER\_ITEMS         TABLE PARTITION    P4                   TBS4

NEW\_ORDERS          TABLE PARTITION    P4                   TBS4

**父表中插入数据，超越分区范围：**

12:13:00 SCOTT@ prod>insert into new\_ordersvalues(105,to\_date('2006-02-01','yyyy-mm-dd'),'x1',1005,1,10000,88005,88002);

1 row created.

12:16:31 SCOTT@ prod>commit;

Commit complete.

12:16:34 SCOTT@ prod>select \* from new\_orders;

ORDER\_IDORDER\_DAT PROD\_NAME  LINE\_ITEM\_ID  CHANNE\_ID PRODUCT\_ID UNIT\_PRICE        QTY

\---------- --------- ---------- ---------------------- ---------- ----------

10103-FEB-05 x1                 1001          1     20000      88001      88002

10215-MAY-05 x1                 1002          1     30000      88002      88002

10322-SEP-05 x1                 1003          1     40000      88003      88002

10430-DEC-05 x1                 1004          1     10000      88004      88002

10501-FEB-06 x1                 1005          1     10000      88005      88002

**父表自动扩展一个新的分区：**

12:17:17 SCOTT@ prod>**select segment\_name,segment\_type,PARTITION\_NAME,TABLESPACE\_NAME fromuser\_segments**

**12:17:34   2  where segment\_name in ('NEW\_ORDERS','ORDER\_ITEMS') order by 1;**

SEGMENT\_NAME        SEGMENT\_TYPE      PARTITION\_NAME      TABLESPACE\_NAME

\-------------------- --------------------------------------

**NEW\_ORDERS           TABLE PARTITION    SYS\_P346             TBS2**

NEW\_ORDERS          TABLE PARTITION    P2                   TBS2

NEW\_ORDERS          TABLE PARTITION    P3                   TBS3

NEW\_ORDERS          TABLE PARTITION    P4                   TBS4

NEW\_ORDERS          TABLE PARTITION    P1                   TBS1

ORDER\_ITEMS         TABLE PARTITION    P2                   TBS2

ORDER\_ITEMS         TABLE PARTITION    P3                   TBS3

ORDER\_ITEMS         TABLE PARTITION    P4                   TBS4

ORDER\_ITEMS         TABLE PARTITION    P1                   TBS1

9 rows selected.

**在子表中插入超出分区范围的数据：**

12:17:49 SCOTT@ prod>insert into order\_items values(105,805,200005,100,100);

1 row created.

12:19:55 SCOTT@ prod>commit;

Commit complete.

**子表也自动扩展一个新的分区，且分区名和父表分区名相同：**

**12:19:57 SCOTT@prod>select segment\_name,segment\_type,PARTITION\_NAME,TABLESPACE\_NAME fromuser\_segments**

**12:20:05   2 where segment\_name in ('NEW\_ORDERS','ORDER\_ITEMS') order by 1;**