分区
分区表：如果表格的数据量很大，例如有几千万行数据，放在普通表格中查询速度会比较慢，就可以考虑用分区表的方式来存储这么大的数据量（官网的建议是表格的数据超过了2个G的大小的时候）。
分区表只能是一开始就创建好的，不能把一个普通表修改成分区表
下面代码可以查询一个表格有多大
--查询一个表格有多大
select sum(bytes)/1024/1024 表格占用的MB
from dba_extents
where segment_name='大写的表名'
and owner='大写的用户名';关系型的数据库，在存储和写入数据的时候，都是通过随机IO的方式进行数据写入的，这种方式比较适合数据的写入而不是查询，如果查询的场景很多需要查询速度比较快，就会给大的表格进行分区的设置。
分区的类型
1.散列分区
散列分区是通过数据库的哈希算法来实现的。哈希就是将任何的数据内容转换成数字，用数字除以分区的数量，将相同余数的数据放在同一个分区中。
分区的字段内容没有特征时，一般就是用散列分区。
散列分区表的创建格式如下：
create table 表名(
列名 数据类型 约束条件
......
)partition by hash(要进行分区的列的列名)
partition 分区名1,
partition 分区名2,
......
);
--例子：
-- 分区表只能是一开始就创建好的，不能把一个普通表修改成分区表
create table emp_hash(
empno number,
ename varchar2(50),
job varchar2(50),
mgr number,
hiredate date,
sal number,
comm number,
deptno number
) partiton by hash(ename)(
partition p1,
partition p2,
partition p3,
partition p4
);
--使用代码块的方式添加批量的数据
begin --放在begin和end之间的我们叫做事务
for i in 1..100000 loop
insert into emp_hash select * from emp;
end loop;
commit;
end;
-- 查询和比较 按 F5 按键，查看语句的执行计划，查看需要消耗多少资源和时间才能加载出数据
select * from emp_normal where ename='KING'; --2491 30
select * from emp_hash where ename='KING'; --548 7发现分区表的查询消耗的资源和时间都比普通表要少。
2.列表分区
当分区的字段有大量的重复信息时，就使用列表分区来划分，例如性别、部门、省份、类型......
列表分区表的创建格式如下：
create table 表名(
列名 数据类型 约束条件
......
) partition by list(要分区的列的列名)(
partition 分区名1 values(分区的值1)，
partition 分区名2 values(分区的值2)
......
);
--例子：
create table emp_list(
empno number,
ename varchar2(50),
job varchar2(50),
mgr number,
hiredate date,
sal number,
comm number,
deptno number
)partition by list(deptno)(
partition d10 values(10),
partition d20 values(20),
partition d30 values(30)
);
--在列表分区中新增一个分区
alter table emp_list add partition d40 values(40);
--删除某个分区
alter table emp_list drop partition d40;3.范围分区
当字段是时间类型，或者是数值类型（工资，年龄...)时，使用范围分区进行分区表的设置。范围分区的分区划分边界只有小于这种设置方式。
范围分区表的创建格式如下：
create table 表名(
列名 数据类型 约束条件
...
) partition by range(列名)(
partition 分区名字1 values less than (分区的边界值1),
partition 分区名字2 values less than (分区的边界值2),
......
partition 分区名字n values less than (maxvalue)
);
如果范围分区有maxvalue，要添加新的分区，必须要先删除maxvalue的分区再添加
--例子：
create table emp_range(
empno number,
ename varchar2(50),
job varchar2(50),
mgr number,
hiredate date,
sal number,
comm number,
deptno number
)partition by range(sal)(
partition sal_a values less than (2000), -- <2000
partition sal_b values less than (3000), -- >=2000 and <3000
partition sal_c values less than (maxvalue) -- >=3000
);
--如果分区有maxvalue，那么就不能添加新的分区了，必须要先删除maxvalue的分区
alter table emp_range drop partition sal_c;
alter table emp_range add partition sal_c values less than(4000);
alter table emp_range add partition sal_d values less than(5000);
alter table emp_range add partition sal_e values less than(maxvalue);4.组合分区
用两两不同分区的方式（范围+列表，范围+散列，列表+散列）进行分区表设置的组合，以父子分区的结构来定义表格的，分区中的分区被称为子分区。
组合分区表的创建格式如下：
create table 表名(
列名 数据类型 约束条件
) partition by 父分区类型(列名)
subpartition by 子分区类型(列名)(
partition 父分区名字1 规则(
subpartition 子分区名字1 规则,
subpartition 子分区名字2 规则,
),
partition 父分区名字2 规则(
subpartition 子分区名字3 规则,
subpartition 子分区名字4 规则,
),
);
--例子：
--用列表+范围两个不同分区方式来构建组合分区表
create table emp_zuhe(
empno number,
ename varchar2(50),
job varchar2(50),
mgr number,
hiredate date,
sal number,
comm number,
deptno number
)partition by list(deptno)
subpartition by range(sal)
(
partition p_d_10 values(10)(
subpartition s_10_a values less than (1000),
subpartition s_10_b values less than (2000),
subpartition s_10_c values less than (3000),
subpartition s_10_d values less than (maxvalue)
),
partition p_d_20 values(20)(
subpartition s_20_a values less than (2500),
subpartition s_20_b values less than (maxvalue)
),
partition p_d_30 values(30)(
subpartition s_30_a values less than (1500),
subpartition s_30_b values less than (3000),
subpartition s_30_c values less than (maxvalue)
)
);分区表格在查询的时候，只有针对分区字段的查询，查询效率才会提升。
和当前表格有关系的一些系统表格：
- select * from user_tables; 查看到当前自己创建的所有表格
- select * from user_tab_columns; 查看创建的所有表格的字段信息
- select * from user_tab_partitions; 查看所有的分区表格的信息
- select * from user_tab_subpartitions; 查看所有组合分区中子分区的信息
单独查看某个父分区的数据：
- select * from 分区表名 partition (父分区名字);
单独查看某个子分区的数据：
- select * from 分区表名 subpartition (子分区名字);
select * from emp_list partition(d10);
select * from emp_hash partition(p1);
select * from emp_zuhe subpartition(S_10_A);单独查询分区，比使用where查询筛选效率更高
-- 单独查询分区，比使用where查询筛选效率更高
select * from emp_list partition(d10); --705 9
select * from emp_list where deptno=10; --837 11分区的操作
新增一个分区：
alter table 分区表名 add partition 分区名 values(分区值);
删除一个分区：删除分区会删除里面的所有的数据
drop table 分区名字 drop partition 分区名;
添加分区，删除分区，截断分区，合并分区，拆分分区，重命名分区
哪种分区类型使用的最多？为什么？
- 看具体情况：
- 如果需要进行数据的过期化处理，那么范围分区基本上是唯一的选择。
- 如果需要数据的均匀分布，那么可以考虑使用HASH分区。
- 如果数据的值可以很好地对应于某个分区，那么就可以考虑使用列表分区。
- 在上面的原则基础上，再结合性能的影响因素，来最终确定使用哪种类型的分区。
索引
索引相当于目录，是对某些特定列中的数据进行排序，生成索引表，在索引里面存储数据和rowid之间的关系，该列作为WHERE条件时，通过ROWID的定位快速的查找行数据，提高查询效率。
索引不是越多越好，索引太多反而会降低表格操作的速度（DML）。
索引的目的：加快表格查询的速度
查询某个表格是否存在索引：select * from user_indexes where table_name='大写的表名';
--查询emp表是否存在索引
select * from user_indexes where table_name='EMP';索引的类型
Btree索引
1.主键索引
创建表格的时候，如果字段有主键约束的话，那么字段会自动创建一个主键索引。主键索引不能单独创建的，只能根据主键约束来创建。
--给emp表的empno字段增加一个主键约束pk_empno,empno字段自动创建一个主键索引
alter table emp add constraints primary key(empno);
--删除主键约束pk_empno
alter table emp drop constraints pk_empno;2.唯一索引
创建表格的时候，如果字段有唯一约束的话，那么字段会自动创建一个唯一索引。唯一索引也可以单独创建，创建的唯一索引，会反过来在表格中生成一个唯一约束。
创建唯一索引：create unique index 索引名 on emp(要创建索引的字段);
删除索引：drop index 索引名;
--给emp表的ename字段增加一个主键约束uk_eanme,ename字段自动创建一个唯一索引
alter table emp add constraints uk_ename unique(eanme);
alter table emp drop constraints uk_ename; --删除唯一约束
--单独创建唯一索引
create unique index idx_ename on emp(ename);
drop index idx_ename;3.普通索引
表格的字段需要经常被查询，但是字段数据又没有什么特征。
--创建一个普通索引idx_mgr
create index idx_mgr on emp(mgr);
--删除索引
drop index idx_mgr ;4.组合索引
表格的某些字段，在查询的时候经常需要放在一起当成多个条件共同进行查询。
创建组合索引：create index 索引名 on emp(字段1,字段2...);
--组合索引的字段可以是N个
create index idx_n_d on emp(ename,deptno);如果我们创建了一个组合索引 （a,b,c），那么在数据库内部，会有几个子索引：
a ab ac abc
哪个字段写在组合的最前面，那么查询的时候，这个字段就必须存在。
5.函数索引
当某个字段，都是需要先进行函数的计算然后将计算结果拿出来进行查询的。如emp表中的haredate字段，我们一般都把它转成字符类型来用
--给emp表的hiredate字段创建函数索引
create index idx_year on emp(to_char(hiredate,'yyyy'));位图索引(bitmap索引)
当某个字段里面的数据全部都是重复的，例如性别、省份、类型、部门......，对这些字段进行查询，就可以创建位图索引。
--给emp表的deptno字段创建位图索引
create bitmap index bit_dept on emp(deptno);分区表中的索引（分区索引）
1.全局索引：global
全局索引中不能创建位图索引。
如果删除了分区表中的某个分区(drop partition)，那么全局索引会失效，在查询时就无法使用了。
索引因为删除分区失效之后，可以通过rebuild来重建：alter index 索引名 rebuild;
2.本地索引：local
本地索引中不能创建唯一索引。
不同的索引一般用在什么场景下？
- 主键索引：当某数据不能重复，也不能为空，建立PRIMARY KEY主键时（索引自动建立）
- 唯一索引：当某列数据不能重复，但可以为空，建立UNIQUE CONSTRAINT唯一约束时（索引自动建立）。
- 组合索引：当多列经常一起出现在WHERE条件中，创建索引。
- 位图索引：当一列有大量重复数据时建立。
- 函数索引：在WHERE 条件语句中包含函数或表达式时建立。
普通索引和位图索引有什么区别？
- 普通索引：列的内容没有什么特点，但是经常需要被进行查询创建：
- 位图索引：列中有非常多的重复的值时候创建。例如某列保存了 “性别”信息创建：
索引在什么情况下会失效？
- 1.筛选条件的左右两边，数据类型不一致导致了数据的隐性转换，索引就会失效；
- select * from emp_idx where to_char(hiredate,'yyyy')=1981;
- 2.在使用索引的时候，在索引的字段上进行数据计算
- select * from emp_idx where sal+1000=800;
- 3.对空值进行查询
- select * from customers_info where curstomer is null;
- 4.取反查询 != not
- select * from customers_info where not curstomer='冯丹';
- 5.模糊查询 like 模糊的部分在查询字符串的后面就会走索引，在前面就不会有
- select * from customers_info where curstomer like '%丹'
- 6.如果查询的字段，结果集的行数，占了表格行数的一定比例，可能就不会走索引了。
- 7.组合索引，在筛选数据的时候，没有使用组合里面的第一个字段，（a b c）没有使用a，那么索引也会失效。
- 8.全局的分区表格索引，删除了分区，索引也会失效。
- 9.如果字段使用了函数，但是函数没有定义为函数索引，字段的索引也会失效。
索引的优缺点？
- 索引优点：创建唯一索引，可以保证数据库中每一行数据的唯一性；可以加快数据的检索速度；可以加速表和表之间的连接；
- 索引缺点：创建和维护索引要耗费大量时间；索引会占物理存储空间；对表中数据进行操作，索引也要动态维护；
哪些列该建立索引？哪些列不应建立索引？
- 建立索引：经常需要搜索、排序的列；关联字段；
- 不该建立索引：很少使用的列；只有很少数据值的列；定义为TEXT,IMAGE,BIT数据类型的列；
唯一索引和主键索引有什么区别
- 主键是不能自己独立创建，和主键约束一起创建的；
- 唯一可以和约束一起创建，也可以单独创建。
- 主键是保证字段不能空不重复，唯一只能保证不重复。
索引中本地索引和全局索引的区别是什么？
- 本地索引不能创建唯一索引的类型，因为每个小的索引只能保证每个分区的数据是唯一的，不能保证整个表格的数据是唯一的。
- 全局索引不能创建位图索引的类型，统一管理的索引，无法对整个表格中，每个独立的分区的数据进行识别和判断。
怎么查看一个表格有哪些不同的索引？
- select * from user_indexes where table_name='表名';
表空间
表空间是一个逻辑概念，在一个表空间里面会有多个数据文件，在数据文件中，保存表格和数据等不同的内容。
数据库里面，默认的表空间的名字叫做 USERS。
创建一个自己的表空间：
- create tablespace 空间名字 datafile '硬盘的位置和文件的名字' size xxM;
给已经存在的表空间追加新的数据文件：
- alter tablespace 空间名字 add datafile '硬盘的位置和文件的名字' size xxM;
--例子：
--创建一个表空间ts_1
create tablespace ts_1 datafile 'E:/ora_files/t01.dbf' size 50M;
--给表空间ts_1追加新的数据文件
alter tablespace ts_1 add datafile 'E:/ora_files/t02.dbf' size 50M;
create table test17(
userid number,
username varchar2(50)
) tablespace ts_1; --创建test17表放到指定ts_1表空间中
create table test18(
userid number,
username varchar2(50),
age number
)partition by range(age)
(
partition age_1 values less than(30),
partition age_2 values less than(maxvalue)
) tablespace ts_1; --创建test18范围分区表放到指定ts_1表空间中
create table test18(
userid number,
username varchar2(50),
age number
)partition by range(age)
(
partition age_1 values less than(30) tablespace ts_1,
partition age_2 values less than(maxvalue)
); --不指定表空间，创建test19表放到默认表空间USERS中
在新增数据的时候，如果提示表空间对应的文件满了，有两种方法：
- 再次新增一个数据文件
- 修改数据文件的大小
- alter database datafile '数据文件的位置和名字' resize xxM;
alter database datafile 'E:/ora_files/t02.dbf' resize 100M;删除表空间：
- drop tablespace 表空间名字;
- drop tablespace 表空间名字 including contents;
如果表空间被分区表的某个分区使用了，还需要先删除分区表或者是这个单一分区，才能去删除表空间
drop tablespace ts_1 including contents;查看表空间和数据文件的基本信息：
- select * from dba_data_files;
查看有哪些表空间：
- select * from user_tablespaces;
--查看表空间和数据文件的信息：
--查看表空间的使用总大小
select sum(a.BYTES-b.BYTES)/1024/1024||'M' from dba_data_files a join dba_free_space b
on a.file_id=b.file_id
where b.tablespace_name='TS_1';
--查看数据文件每个文件的使用的大小
select file_name,(a.BYTES-b.BYTES)/1024/1024||'M' from dba_data_files a join dba_free_space b on a.file_id=b.file_id
where b.tablespace_name='TS_1';
1241
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
