oracle实验3：创建分区表

xy 201810414224 软工2班

实验3：创建分区表

实验目的：

掌握分区表的创建方法，掌握各种分区方式的使用场景。

实验内容：

• 本实验使用3个表空间：USERS,USERS02,USERS03。在表空间中创建两张表：订单表(orders)与订单详表(order_details)。
• 使用你自己的账号创建本实验的表，表创建在上述3个分区，自定义分区策略。
• 你需要使用system用户给你自己的账号分配上述分区的使用权限。你需要使用system用户给你的用户分配可以查询执行计划的权限。
• 表创建成功后，插入数据，数据能并平均分布到各个分区。每个表的数据都应该大于1万行，对表进行联合查询。
• 写出插入数据的语句和查询数据的语句，并分析语句的执行计划。
• 进行分区与不分区的对比实验。
  CREATE PLUGGABLE DATABASE xydb FROM pdborcl file_name_convert=(’/home/oracle/app/oracle/oradata/orcl/pdborcl’,’/home/student/pdb/xydb’);

实验参考步骤

给xy权限

建表

在主表orders和从表order_details之间建立引用分区
在study用户中创建两个表：orders（订单表）和order_details（订单详表），两个表通过列order_id建立主外键关联。orders表按范围分区进行存储，order_details使用引用分区进行存储。
创建orders表的部分语句是：

SQL> CREATE TABLE orders 
(
 order_id NUMBER(10, 0) NOT NULL 
 , customer_name VARCHAR2(40 BYTE) NOT NULL 
 , customer_tel VARCHAR2(40 BYTE) NOT NULL 
 , order_date DATE NOT NULL 
 , employee_id NUMBER(6, 0) NOT NULL 
 , discount NUMBER(8, 2) DEFAULT 0 
 , trade_receivable NUMBER(8, 2) DEFAULT 0 
) 
TABLESPACE USERS 
PCTFREE 10 INITRANS 1 
STORAGE (   BUFFER_POOL DEFAULT ) 
NOCOMPRESS NOPARALLEL 
PARTITION BY RANGE (order_date) 
(
 PARTITION PARTITION_BEFORE_2016 VALUES LESS THAN (
 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
 'NLS_CALENDAR=GREGORIAN')) 
 NOLOGGING 
 TABLESPACE USERS 
 PCTFREE 10 
 INITRANS 1 
 STORAGE 
( 
 INITIAL 8388608 
 NEXT 1048576 
 MINEXTENTS 1 
 MAXEXTENTS UNLIMITED 
 BUFFER_POOL DEFAULT 
) 
NOCOMPRESS NO INMEMORY  
, PARTITION PARTITION_BEFORE_2017 VALUES LESS THAN (
TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
'NLS_CALENDAR=GREGORIAN')) 
NOLOGGING 
TABLESPACE USERS02 
...
);

建表截图

数据数量查询

建立分区表order_details

创建order_details表的部分语句如下：

SQL> CREATE TABLE order_details 
(
id NUMBER(10, 0) NOT NULL 
, order_id NUMBER(10, 0) NOT NULL
, product_id VARCHAR2(40 BYTE) NOT NULL 
, product_num NUMBER(8, 2) NOT NULL 
, product_price NUMBER(8, 2) NOT NULL 
, CONSTRAINT order_details_fk1 FOREIGN KEY  (order_id)
REFERENCES orders  (  order_id   )
ENABLE 
) 
TABLESPACE USERS 
PCTFREE 10 INITRANS 1 
STORAGE (   BUFFER_POOL DEFAULT ) 
NOCOMPRESS NOPARALLEL
PARTITION BY REFERENCE (order_details_fk1)
(
PARTITION PARTITION_BEFORE_2016 
NOLOGGING 
TABLESPACE USERS --必须指定表空间,否则会将分区存储在用户的默认表空间中
...
) 
NOCOMPRESS NO INMEMORY, 
PARTITION PARTITION_BEFORE_2017 
NOLOGGING 
TABLESPACE USERS02
...
) 
NOCOMPRESS NO INMEMORY  
);

建表结果

查看数据库的使用情况

以下样例查看表空间的数据库文件，以及每个文件的磁盘占用情况。

执行计划分析

有分区sql

#查看不同分区的数据
set autotrace on
select * from xy.orders where order_date
between to_date('2010-1-1','yyyy-mm-dd') and to_date('2021-4-5','yyyy-mm-dd');

无分区sql

set autotrace on
select * from xy.orders_details where order_date
between to_date('2010-1-1','yyyy-mm-dd') and to_date('2021-4-6','yyyy-mm-dd');

实验总结

通过本次实验，我了解并且掌握分区表的创建方法，并且还掌握各种分区方式的使用场景。就数据量来说，根据有分区和无分区sql语句的比较，在orders数据量为10000，order_details数据量为30000时，有分区比无分区查找数据优势会更大。如果数据量大，分区表的优势明显加大。 如果数据量小，有分区与无分区差别不是很大，甚至无分区可能更快。