当表中的数据量不断增大,查询数据的速度就会变慢,这时就应该考虑对表进行分区。表分区后,逻辑上表仍然与原来表一样,只是将表中的数据在物理上存放到多个表空间(物理文件上),这样查询数据时,不至于每次都扫描整张表的数据文件。
如上表就是分区前后的差异,在分区前这个表的数据全放在一个文件上,导致数据量很大,按月份分区后,把相同的月份放在一个文件存储,多个文件存储不同月份的数据,这样在做数据查询的时候,如果只查询月份为201901的数据时,则分区前需要对大文件做扫描,分区后则只要对小文件file1做查询,就像你要从1000个数里找一个数,和从10里找一个数的区别,从规模和复杂度来讲都是减少了很多。
分区表的种类:
1:范围分区:将数据基于范围映射到每一个分区,这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的,并且分区键经常采用日期。比如:你可能会将销售数据按照月份进行分区。
CREATE TABLE t_sales_part_range
(
month_code VARCHAR2(30) NOT NULL,
dept_code VARCHAR2(30) NOT NULL,
user_no VARCHAR2(30) NOT NULL,
sales_amt number
)
PARTITION BY RANGE (month_code)
(
PARTITION p_201901 VALUES LESS THAN ('201902'),
PARTITION p_201902 VALUES LESS THAN ('201903')
);
2:Hash分区:对于那些无法有效划分范围的表,可以使用hash分区。hash分区会将表中的数据平均分配到你指定的几个分区中,列所在分区是依据分区列的hash值自动分配,因此你并不能控制也不知道哪条记录会被放到哪个分区中,hash分区也可以支持多个依赖列。
3:List分区:需要指定列的值,其分区值必须明确指定,该分区列只能有一个,不能像range或者hash分区那样同时指定多个列做为分区依赖列,但它的单个分区对应值可以是多个。
CREATE TABLE t_sales_part_list
(
month_code VARCHAR2(30) NOT NULL,
dept_code VARCHAR2(30) NOT NULL,
user_no VARCHAR2(30) NOT NULL,
sales_amt number
)
PARTITION BY LIST (month_code)
(
PARTITION p_201901 VALUES('201902'),
PARTITION p_201902 VALUES('201903'),
PARTITION p_other VALUES (default)
);
4: 组合分区:跟据上面的分区做组合后进行分区,比如在做二级分区时使用,第一级我使用范围分区,第二级我使用列表分区。
CREATE TABLE t_sales_part_rang_list
(
month_code VARCHAR2(30) NOT NULL,
dept_code VARCHAR2(30) NOT NULL,
user_no VARCHAR2(30) NOT NULL,
sales_amt number
)
PARTITION BY RANGE(month_code) SUBPARTITION BY LIST (dept_code)
(
PARTITION p_201901 VALUES LESS THAN('201902')
(
SUBPARTITION p_201901_a VALUES ('a'),
SUBPARTITION p_201901_b VALUES ('b'),
SUBPARTITION p_201901_other VALUES (default)
),
PARTITION p_201902 VALUES LESS THAN('201903')
(
SUBPARTITION p_201902_a VALUES ('a'),
SUBPARTITION p_201902_b VALUES ('b'),
SUBPARTITION p_201902_other VALUES (default)
)
);
优点:
改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。
增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用。
维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可。
均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能。
缺点:
如果在分区表中使用hash类型,在后续的扩展中会麻烦,需要对历史数据重新刷一次,所以一般我们都不建议使用hash分区。
更多技术文章请关注公众号(长按后点识别图中二维码):