数据库(Database)由若干表空间(Tablespace)组成，表空间(Tablespace)由若干段(Segment)组成，段(Segment)由若干区(Extent)组成，区(Extent)又由若干块(Block)组成
Block越大，相同数据量的情况下存储的行就越多，Block需要的越少， 访问的逻辑读就越小，对应的consistent gets就越小
ps:实践情况并非Block越大越好，block越大，不同的访问的数据落在同一个Block的概率就越大，这个很容易产生热竞争
查看表空间的总体情况：
SELECT A.TABLESPACE_NAME "表空间名",
A.TOTAL_SPACE "总空间(G)",
NVL(B.FREE_SPACE, 0) "剩余空间(G)",
A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(G)",
CASE
WHEN A.TOTAL_SPACE = 0 THEN
0
ELSE
trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2)
END "剩余百分比%" --避免分母为0
FROM (SELECT TABLESPACE_NAME,
trunc(SUM(BYTES) / 1024 / 1024 / 1024, 2) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME,
trunc(SUM(BYTES / 1024 / 1024 / 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
ORDER BY 5;
Oracle表设计与调优
分区类型：分区分为范围分区、列表分区、HASH分区、组合分区四种
范围分区
关键字partition by range
create table range_part_tab (seq number,deal_date date,unit_code number,remark varchar2(100))
partition by range (deal_date)
(
partition p1 values less than (TO_DATE('2018-11-01','YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2018-12-02','YYYY-MM-DD')),
partition p3 values less than (TO_DATE('2019-01-01','YYYY-MM-DD')),
partition p4 values less than (TO_DATE('2019-02-01','YYYY-MM-DD')),
partition p5 values less than (TO_DATE('2019-03-01','YYYY-MM-DD')),
partition p6 values less than (TO_DATE('2019-04-01','YYYY-MM-DD')),
partition p7 values less than (TO_DATE('2019-05-01','YYYY-MM-DD')),
partition p8 values less than (TO_DATE('2019-06-01','YYYY-MM-DD')),
partition p9 values less than (TO_DATE('2019-07-01','YYYY-MM-DD')),
partition p10 values less than (TO_DATE('2019-08-01','YYYY-MM-DD'))
);
insert into range_part_tab
(seq, deal_date, unit_code, remark)
select rownum,
to_date(to_char(sysdate-365, 'J') +
trunc(DBMS_RANDOM.value(0, 365)),'J'),
ceil(dbms_random.value(210,220)),
rpad('*', 1, '*')
from dual
connect by rownum <= 1000;
列表分区
create table list_part_tab (seq number,deal_date date,unit_code number,remark varchar2(100))
partition by list (unit_code)
(
partition p1 values (211),
partition p2 values (212),
partition p3 values (213),
partition p4 values (214),
partition p5 values (215),
partition p6 values (216),
partition p7 values (217),
partition p8 values (218),
partition p9 values (219),
partition p10 values (220),
partition p0 values (DEFAULT)
);
insert into list_part_tab
(seq, deal_date, unit_code, remark)
select rownum,
to_date(to_char(sysdate-365, 'J') +
trunc(DBMS_RANDOM.value(0, 365)),'J'),
ceil(dbms_random.value(210,220)),
rpad('*', 1, '*')
from dual
connect by rownum <= 1000;
commit;
散列分区
散列分区也叫hash分区，partitions后接分区数，尽量设置为偶数，
create table hash_part_tab (seq number,deal_date date,unit_code number,remark varchar2(100))
partition by hash (deal_date)
partitions 12;
insert into hash_part_tab
(seq, deal_date, unit_code, remark)
select rownum,
to_date(to_char(sysdate-365, 'J') +
trunc(DBMS_RANDOM.value(0, 365)),'J'),
ceil(dbms_random.value(210,220)),
rpad('*', 1, '*')
from dual
connect by rownum <= 1000;
commit;
组合分区
主要有两种：oracle11之前只支持范围列表分区(RANGE-LIST)和范围散列分区(RANGE-HASH)，oracle11之后支持(范围范围分区)RANGE-RANGE、 (列表范围分区)LIST-RANGE、(列表散列分区)LIST-HASH、(列表列表分区)LIST-LIST这几种组合，为了避免每个主分区中都写相同的从分区，可以用模板方式(subpartition template)
create table range_list_part_tab (seq number,deal_date date,unit_code number,remark varchar2(100))
partition by range (deal_date)
subpartition by list (unit_code)
subpartition template
(subpartition s1 values (211),
subpartition s2 values (212),
subpartition s3 values (213),
subpartition s4 values (214),
subpartition s5 values (215),
subpartition s6 values (216),
subpartition s7 values (217),
subpartition s8 values (218),
subpartition s9 values (219),
subpartition s10 values (220),
subpartition s0 values (DEFAULT) )
(
partition p1 values less than (TO_DATE('2018-11-01','YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2018-12-02','YYYY-MM-DD')),
partition p3 values less than (TO_DATE('2019-01-01','YYYY-MM-DD')),
partition p4 values less than (TO_DATE('2019-02-01','YYYY-MM-DD')),
partition p5 values less than (TO_DATE('2019-03-01','YYYY-MM-DD')),
partition p6 values less than (TO_DATE('2019-04-01','YYYY-MM-DD')),
partition p7 values less than (TO_DATE('2019-05-01','YYYY-MM-DD')),
partition p8 values less than (TO_DATE('2019-06-01','YYYY-MM-DD')),
partition p9 values less than (TO_DATE('2019-07-01','YYYY-MM-DD')),
partition p10 values less than (TO_DATE('2019-08-01','YYYY-MM-DD'))
);
insert into range_list_part_tab
(seq, deal_date, unit_code, remark)
select rownum,
to_date(to_char(sysdate-365, 'J') +
trunc(DBMS_RANDOM.value(0, 365)),'J'),
ceil(dbms_random.value(210,220)),
rpad('*', 1, '*')
from dual
connect by rownum <= 1000;
commit;
