大佬教程收集整理的这篇文章主要介绍了数据库分区、分表、分库、分片,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
一张表的查询速度已经慢到影响使用的时候。
sql经过优化
当频繁插入或者联合查询时,速度变慢
分表后,单表的并发能力提高了,磁盘I/O性能也提高了,写操作效率提高了
需要业务系统配合迁移升级,工作量较大
分区和分表的目的都是减少数据库的负担,提高表的增删改查效率。
当访问量不大,但表数据比较多时,可以只进行分区。
CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, FIRST_NAME VARCHAR2(30) NOT NULL, LAST_NAME VARCHAR2(30) NOT NULL, PHONE VARCHAR2(15) NOT NULL, EMAIL VARCHAR2(80), STATUS CHAR(1) ) PARTITION BY RANGE (CUSTOMER_ID) ( PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 )
CREATE TABLE ORDER_ACTIVITIES ( 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 LESS 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 (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03 )
CREATE TABLE RangeTable ( idd INT PRIMARY KEY , iNAME VARCHAR(10), grade INT ) PARTITION BY RANGE (grade) ( PARTITION part1 VALUES LESS THEN (1000) TABLESPACE Part1_tb, PARTITION part2 VALUES LESS THEN (MAXVALUE) TABLESPACE Part2_tb );
CREATE TABLE PROBLEM_TICKETS ( PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY, DESCRIPTION VARCHAR2(2000), CUSTOMER_ID NUMBER(7) NOT NULL, DATE_ENTERED DATE NOT NULL, STATUS VARCHAR2(20) ) PARTITION BY LIST (STATUS) ( PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01, PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02
CREATE TABLE ListTable ( id INT PRIMARY KEY , name VARCHAR (20), area VARCHAR (10) ) PARTITION BY LIST (area) ( PARTITION part1 VALUES ('guangdong','beijing') TABLESPACE Part1_tb, PARTITION part2 VALUES ('shanghai','nanjing') TABLESPACE Part2_tb ); )
CREATE TABLE HASH_TABLE ( COL NUMBER(8), INF VARCHAR2(100) ) PARTITION BY HASH (COL) ( PARTITION PART01 TABLESPACE HASH_TS01, PARTITION PART02 TABLESPACE HASH_TS02, PARTITION PART03 TABLESPACE HASH_TS03 )
CREATE TABLE emp ( empno NUMBER (4), ename VARCHAR2 (30), sal NUMBER ) PARTITION BY HASH (empno) PARTITIONS 8 STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);
CREATE TABLE SALES ( PRODUCT_ID VARCHAR2(5), SALES_DATE DATE, SALES_COST NUMBER(10), STATUS VARCHAR2(20) ) PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS) ( PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009 ( SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 ), PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009 ( SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 ) )
create table dinya_test ( transaction_id number primary key, item_id number(8) not null, item_description varchar2(300), transaction_date date ) partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) ( partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)), partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)), partition part_03 values less than(maxvalue) );
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');
ALTER TABLE SALES DROP PARTITION P3;
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
ALTER TABLE SALES TRUNCATE PARTITION P2;
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
ALTER TABLE SALES COALESCA PARTITION;
ALTER TABLE SALES RENAME PARTITION P21 TO P2;
select sum( *) from (select count(*) cn from t_table_SS PARTITION (P200709_1) union all select count(*) cn from t_table_SS PARTITION (P200709_2) );
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'
select object_name,object_type,tablespace_name,sum(value) from v$segment_statistics where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX' group by object_name,object_type,tablespace_name order by 4 desc
select * from DBA_PART_TABLES
select * from ALL_PART_TABLES
select * from USER_PART_TABLES
select * from DBA_TAB_PARTITIONS
select * from ALL_TAB_PARTITIONS
select * from USER_TAB_PARTITIONS
select * from DBA_TAB_SUBPARTITIONS
select * from ALL_TAB_SUBPARTITIONS
select * from USER_TAB_SUBPARTITIONS
select * from DBA_PART_KEY_COLUMNS
select * from ALL_PART_KEY_COLUMNS
select * from USER_PART_KEY_COLUMNS
select * from DBA_SUBPART_KEY_COLUMNS
select * from ALL_SUBPART_KEY_COLUMNS
select * from USER_SUBPART_KEY_COLUMNS
select * from user_tables a where a.partitioned='YES'
truncate table table_name;
alter table table_name truncate partition p5;
以上是大佬教程为你收集整理的数据库分区、分表、分库、分片全部内容,希望文章能够帮你解决数据库分区、分表、分库、分片所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。