Oracle（表分区,执行计划与扫描方式）12.23

分区

Oracle的表分区功能 通过改善可管理性、性能、和可用性，从而为个事应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外，分区还可以极大简化常见的管理任务，分区使构建千兆字节数据系统或超高可用性系统的关键工具。分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段即可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表于与非分区表完全相同，使用sql  dml 命令访问分区后的表时，无需任何修改。
什么时候用分区表：
1，表的大小超过2GB。
2，表中包含历史数据，新的数据被增加到新的分区中。

表分区的优缺点：
	优点：
		1，改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
		2，增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
		3，维护方便：如果表的额某个分区出现故障，需要修复数据，只修复该分区即可；
		4，均衡 i/o：可以把不同的分区映射到不同磁盘以平衡i/o，改善整个系统性能。
	缺点：
		分区表相关: 已经存在的表没有方法可以直接转化为分区表。不过oracle  提供了在线重定义的表的功能

范围分区

有序
	范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定。这种分区方式是最为常用的，并且分区键经常采用日期。举个例子：你可能会将销售数据按照月份进行分区。
当使用范围分区时，请考虑以下几个规则：
1，每一个分区都必须有一个values  less  than 子句，他指定了一个不包括该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
2，所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的上限值。
3，在最高的分区中，maxvalue被定义。maxvalue 代表了一个不确定的值，这个值高于其他分区中的任何分区键的值。也可以理解为高于任何分区中指定的value less than的值，同时包括空值。

create table  table_name(
	a  varchar2(10) ,
	b  varchar2(20),
	c   varchar2(3),
	d   number
)
partiton  by  range( D )(	
	partition  rangen_name1 values  less  than ( 60),  --range_name1
	partition  rangen_name2 values  less  than ( 85),  --range_name2
	partition  rangen_name3 values  less  than ( maxvalue),  --range_name3

)

select  *  from table_name   partiton( rang-name);

列表分区（约束作用）

注：明确知道值。要规定位置，插入的数据必须在分区范围内，
该分区的特点时某列的值只有几种，不支持多字段处理
  	
  	create    table   table_name（
  	a  varchar2(10) ,
  b  varchar2(20),
  c   varchar2(3),
  d   number
 ）
	partiton  by  range( D )( 
		partition list_name1  values(val1),
		partition list_name2  values(val2),
		 partition list_name3  values(val3),
	）
	select  *  from  table_name  partition （list_name2）;

hash分区（快速单条数据搜索，缺点：没顺序，）

注：适合种类多
这类分区是在列值上使用散列算法，以确定将行放入那个分区种。当列的值没有合适的条件时，建议使用散列分区，
散列分区通过指定分区标号来均匀分布数据的一种分区类型，因为通过在i/o设备上进行散列分区，使得这些分区大小一致，
create  table  grafercord(
		a  varchar2(10) ,
		  b  varchar2(20),
		  c   varchar2(3),
		  d   number
	)
partiton  by hash (a )( 
   partition hash_name1,
   partition hash_name2 ,
    partition hash_name3 ,

）;
select * from graderecord partiton(hash_name3);

组合范围列表分区

这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后在按某列进行列表分区，分区中的分区被称为子分区，
范围-hash
	create  table  grafercord(
	   a  varchar2(10) ,
	     b  varchar2(20),
	     c   varchar2(3),
	     d   int
	  )

partiton  by range (a )  subpartition   by  hash（b，c）（
	partition  rangen_name1  values  less  than ( 75)（ subpartition   hash_name1,subpartition  hash_name2）,
	partition  rangen_name2   values  less  than (maxvalue)（ subpartition   hash_name3,subpartition  hash_name4）

）;
select * from graderecord partition(range_name1);
select * from graderecord subpartition (hash_name1);

范围-列表
create table table_name（
a varchar2(10) ,
b varchar2(20),
c varchar2(3)
）
partiton by range (a，b ) subpartition by hash（c）（
partition rangen_name1 values less than ( ‘200705’,‘012’）(
subpartition list_name1 values(‘01’,‘02’,‘03’,‘04’,‘05’,‘06’, ‘07’ , ‘08’ , ‘09’ , ‘10’),
subpartition list_name2 values(‘11’,‘12’,‘13’,‘14’,‘15’,16’, ‘17’ , ‘18’ , ‘19’ , ‘20’),
subpartition list_name3 values(‘21’,‘22’,‘23’,‘24’,‘25’,26’, ‘27’ , ‘28’ , ‘29’ , ‘30’)
),
partition rangen_name2 values less than ( ‘200705’,‘012’）(
subpartition list_name4 values(‘01’,‘02’,‘03’,‘04’,‘05’,‘06’, ‘07’ , ‘08’ , ‘09’ , ‘10’),
subpartition list_name5 values(‘11’,‘12’,‘13’,‘14’,‘15’,16’, ‘17’ , ‘18’ , ‘19’ , ‘20’),
subpartition list_name6 values(‘21’,‘22’,‘23’,‘24’,‘25’,26’, ‘27’ , ‘28’ , ‘29’ , ‘30’)
),
partition rangen_name3 values less than ( ‘200705’,‘012’）(
subpartition list_name7 values(‘01’,‘02’,‘03’,‘04’,‘05’,‘06’, ‘07’ , ‘08’ , ‘09’ , ‘10’),
subpartition list_name8 values(‘11’,‘12’,‘13’,‘14’,‘15’,16’, ‘17’ , ‘18’ , ‘19’ , ‘20’),
subpartition list_name9 values(‘21’,‘22’,‘23’,‘24’,‘25’,26’, ‘27’ , ‘28’ , ‘29’ , ‘30’)
)
select * from graderecord partition(range_name1);
select * from graderecord subpartition(list_name7)
）

开启分区转移

alert  table  table_name   enable   row  movement

11g后

虚拟列

1，只能在堆组织表（普通表）上创建虚拟列，不能再索引组织表、外部表、临时表上创建虚拟列；
2，虚拟列不能是lob或者raw类型；
3，虚拟列的值并不是真实存在的，只有在用到时，才根据表达式计算出虚拟列的值，磁盘并不存放。虚拟列的值有Oracle根据表达式自动计算得出，不可以手动去修改和指定虚拟列的值。
4，可以在虚拟列上建立索引。
	create  index  inx_test  on  test1(v1);
5，如果在已经创建的表中增加虚拟列时，若没有指定虚拟列的字段类型；Oracle会根据generated  always  as  后面的表达式计算的结果自动设置该字段的类型。
6，虚拟列的值由Oracle 根据表达式自动计算得出，不可以做udate 和insert操作，可以对虚拟列做delete操作
7，表达式中的所有列必须在同一张表
8，表达式不能使用其他虚拟列
create  table  t1(
col1  number(),
col2   number(),
col3[ type ]  as  ("col1"+"col2")
)

虚拟列分区

可把虚拟列当做分区关键字建立分区表，这时Oracle  11g的另一个新特性——虚拟列分区，可创建带有虚拟列的分区表：
create  table  test(n1 number,c1  varchar2(82), n2  number  generated  always  as(n1*0.8)) 
//创建带有虚拟列的分区。
create  table  test(
	n1 number,
	c1  varchar2(82), 
	n2  varchar2(2)   generated  always  as(substrate（c1,1,1）) 
	)
partition  by   list  (v1)
(
	partition   vl1   values('i'),
	  partition   vl2   values('o'),
	    partition   vl3   values('e'),
	      partition   vl4   values(default）,    
)

系统分区

在系统分区中是不需要指定任何分区键的，数据会进入那个分区完全是由sql语句本身决定，即在insert 语句中要指定插入哪个分区。
在插入数据时，如果没有指定分区就会报错。由于系统分区的特殊性，其不支持分区分裂（partition  split）操作和ctas操作
创建系统分区表
	create table  table_name(
		col1  type,
		col2   type
)
  partition   by  system(
	partition    p_name1,
	partition    p_name2
)

插入数据
	insert into  table_name  partition   (p_name1) values

关联分区

在11g之前的版本中，一个主表和多个子表均要做分区，我们就需要将这个分区键子冗余到子表中，才能实现相同的分区策略，在11g中，可以通过关联分区解决，但是分区不能指定引用分区的上下限，一旦父表发生变化，子表分区也会自适应，而单独修改子分区时不允许的。
----------------------------父表----------------------------------------------------
create   table   fu_table(
	fupk  type,
	co2 type,
	col. type
)
partition    by  range (col2)(
	partition    p1   values   less  than ('200000101'),
	partition    p2   values   less  than (maxvalue),
)
enable   row   movement
----------------------------子表----------------------------------------------------
create   table   zi_table(
  zipk  type,
  co2 type,
  col. type
  constraint   fk1   foreign  key(col2)  references  fu_table(fupk)
 )
partition    by  reference (fk1)
enable  row  movement

间隔查询

11g推出了interval 建个分区，以往的分区是需要手工或半自动化脚本实现分区扩展，但这种间隔分区的出现，将分区扩展的工作彻底解放出来

create   table  table_name(
	col1  number,
	col2  number,
	col3  date
)
partition      by  range(col3)
interval ( numtoyminterval(1,'month'))
store  in  (users)(	
	partition     pname1  values  less  than  ( to_date('200001101','yyyymmdd'))   tablespace  users,
	partition     pname2  values  less  than  ( to_date('200001101','yyyymmdd'))   tablespace  users
)

store in 定义可选用的表空间
1，设置store in ，未设置预定义分区表空间，则预定义分区使用默认表空间 users，扩展分区循环使用store in中定义分区。
2，未设置store in，设置预定义分区表空间，则预定义分区使用定义的表空间，扩展分区使用默认表空间 users
3，设置store in，这是预定义分区表空间，则预定义分区和扩展分区均会使用store in中定义分区
4，store in参数的作用域就是扩展分区，预定义分区需要明确写出表空间，否则使用的使用户默认表空间

数据库链接|跨库访问

创建连接
create public database 1ink dblink_ NAME connect to USERNAME 1dentified by PASSWORD using’ IP/dbNAME’ ;
删除连接
drop public database 1ink dblink NAME;
使用连接
select * from table_ NANE@db1 ink_ NANE

执行计划与扫描方式

explain plan for SQL;
select * from table (dbms_ xplan. display)

1, Count
分析或执行语句的次数以及为语句发出的提取谓用数
2, CPU
每个阶段的处理时间以秒为单位如果在共享池中找到该语句对于分析阶段为0
3, E1 apsed
占用时间以秒为单位通常不是非常有用因为其它进程影响占用时间
4, Disk
从数据库文件读取的物理数据块如果该数据被缓冲则该统计可能很低
5, Query
为持续读取检索的逻辑缓冲区通常用于SELECT 语句
6 , Current
在当前模式下检索的逻辑缓冲区通常用于DML语句
7, Rows
外部语句所处理的行对于SELECT 语句在提取阶段显示它对于DML语句在执行阶段显示它

Operation;当前操作的内容。
Name:操作对象
Rows:也就是10g版本以前的Cardinality(基数)，0racle 估计当前操作的返回结果集行数。
Bytes:
表示执行该步骤后返回的字节数。
Cost (CPU) :表示执行到该步骤的一个执行成本，用于说明sQL执行的代价。
Time: Oracle 估计当前操作的时间。

oracle访问数据的存取方法
1)全表扫描(Fu11 Table Scans， FTS)
全表扫描就是在数据查询过程中，对整张表的全部低于高水位线的数据库进行读取。对于大表来说造成过
多的I/0开销
2)通过ROWID的表存取(Table Access by ROWID 或rowid lookup)
利用rowid来查询记录，其实就是根据数据行实际存储的位置来获取数据。通过rowid查询记录是查询最
快的方法
3)索引扫描(Index Scan或index lookup) 有4种类型的索引扫描:
(1) 索引唯-扫描(index unique scan)
索引唯一扫描只能发生在唯一索引上，通过唯一索引查找数据往往返回单个rowid,从根节点到枝节
点到叶节点存储这一个对应的rowid
(2)索引范围扫描 ( index range scan)
在非唯一索引上都使用索引范围扫描。使用index rang scan的3种情祝: .
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)
(b)在组合索引上， 只使用部分列进行查询，导致查询出多行
© 对非唯- 索引列上进行的任何查询。
(3) 索引全扫描(index full scan)
先扫描索引中全部叶节点和条目，在选择对应数据进行排序输出。索引全扫描只在cbo模式下有效
(a) 表和表进行排序合并联立查询时，要求:排序的列必须是存在索引中。
(b)查询中有 order by和group by子句时，要求:子句中所有的列必须存在于索引中
(4) 索引快速扫描(index fast full scan)
索引快速扫描是扫描索引中的所有数据库与全表扫描类似，显著区别在于它不对查询出的结果排序,既数据不是以排序顺 序返回。可以使用多块度功能，或者使用并行度。获取最大I/0吞吐速度，同时扫描最少数据单元
(5) 索引跳跃扫描( index skip scan)
索引跳跃扫描是9i版本的特性只在cbo模式下有效,前置索引列distinct较少时，如果只查询该索引的后置列，那么就跳过该前置索隐裂，将索引逻辑划分为多个自索引。但是对于一个合理的表结构索引结构设计，不会出现这种情况
(6) 索引组合扫描( index combine)
索引组合扫描是9i版本的特性只在cbo模式下有效，如果where子句中包含多个筛选条件，那么将多个索引组合使用

三种删除语句

1，delete
2，truncate（可以释放空间。而delete不同。当delete没有查询语句的时候表面和truncate相同，但是delete不=不能释放空间）
3，drop

相同点：
	1，truncate 和不带where  子句的delete，以及drop都会删除表内的数据。
	2，drop，truncate 都是DDL（数据定义语言）语句，执行后会自动提交，
	
不同点：
	1，truncate 和 delete 值删除数据不删除表的结构（定义），drop语句将删除表的结构被以来的约束（constrain），触发器（trigger），索引（index）；依赖于该表的存储过程/函数将保留，但是变为invalid状态。
	2. delete语句是dml,这个操作会放到rollback segement中，事务提交之后才生效;如果有相应的triger,执行的时候将被触发truncate, drop是dd1，操作立即生效,原数据不放到rol1back segment 中，不能回滚.操作不触发trigger.
	3.delete语句不影响表所占用的extent,高水线(highwatermark)保持原位置不动
	drop语句将表所占用的空间全部释放
	truncate语句缺省情说下见空间释放到minextents个extent,除非使用reuse storage; truncate会将高水线复位(回到最开始).
	4，速度，一般来说：drop>truncate >delete
	5，安全性：
		小心使用drop 和 truncate ，尤其没有备份的时候，使用上，像删除部分数据行用delete，注意带上where子句，回滚段要足够大。
		想删表。当然用drop
		想保留表而将所有的数据删除，如果事务无关，用truncate即可，如果和事务有关，或者想触发 trigger ，还是用delete。
		如果是整理表内部的碎片，可以用truncate 跟上reuse stroage，在重新导入/插入数据
	6，delete 是DML语句 ，不会自动提交
		drop，truncate都是 DDL（数据定义语言）语句，执行后会自动提交

表的范式设计（设计时扩容，拓展性nice）和字段设计（要考虑热点字段的容量大小）

分布式数据库的CAP理论三元悖论分布式系统无法同时满足三个需求。
一致性（1对1对1对1）: 任何一个读操作总是能读取到之前完成的写操作结果，也就是分布式环境中，多点的数据是一致的。
可用性:  每一个操作总是能够在确定的事件内返回，也就是系统随时都可用的。
分区可容忍性: 在出现分区不一致的情况下，系统也能正常工作

基表提倡范式，其他推荐反范式
范式化与反范式化：
	范式化的优势：
		1，范式化能获得比较好的写性能，因为没有过多重复的数据，通常来说更新和新建操作，其和通过操作恒少量的数据完成。
		2，范式化的表通常也会比较小，便于缓存。
		3，因为没有过多重复性的数据，只需要去更少去重和分组操作。
	范式化劣势：
		1，表数量过多，关联性太强，强耦合性不够灵活。
		2，一个简单的业务可能需要联立多个表，性能不佳
		3，一些核心表面临高并发的时候，常常出现热点争用，拖累整库性能。
完全范式化建模的数据库在处理高并发问题时候，往往是力不从心，会出现忙的表忙的要死，闲的表闲的要死。
反范式化就是无视范式规则，建立数据冗余的表结构。可以避免表联立的性能损耗，这种思路是为了解决高并发时快速
响应的业务需求，通过一张反范式表实现单表单业务，即一个表只服务一项业务，-项业务尽可能通过一个表完成。

总结：
	无论是反范式的单库设计，都是一种“空间换取时间”的做法。都应该把保证数据的一致性作为第一考虑,可以弱化，但不能忽视。
	1、拆分前段表(直接面对业务核心)和后端表(基础核心)区别对待
	2、对于后端核心的东西，使用范式设计，保障数据一致性，这部分业务数据一般不会直 接面对高并发的压力。
	3、对于前段的东西，尽可能采用分库分表，进行分区设计，可以适当弱化数据库- 致性，提高并发性能。
	4、前段损失的数据一致性必须可以通过后端追溯回来(服务器业务层对各冗余数据强制同步或者数据库定时将后端表数据刷新到前端表)

字段顺序，这个问题很可能被忽略，但是它的影响却是不小的。在初期的设计中也容易被忽视，一些重要的字段不得不排在后面，甚至审计字段后面。这样可能会造成性能或高并发问题。
要尽量保障热点字段在前，尽量最小化行宽。
字段过多没有冗余的表，可以根据业务划分组合，分别保存在子表中，防止排在后面的字段磁盘的扫描范围过广，Io命中率低。导致读取效率低。在高并 发情况下代价更高。

数据冗余与一致性

互联网数据量大的业务场景，常常：
	使用水平切分来降低单库数据量
	使用数据冗余的反范式设计来满足不同维度的查询需求

冗余数据三种方案：
	（1）服务同步双写法能够很容易的实现数据冗余
         (2)为了降低时延，可以优化为服务异步双写法
	(3)为了屏蔽“冗余数据”对服务带来的复杂性，可以优化为线下异步双写法

保证数据一致性的方案:
(1)最简单的方式，线下脚本扫全量数据比对
(2)提高效率的方式，线下脚本扫增量数据比对
(3)最实时的方式，线上检测“消息对”

---

服务同步同步双写：
	由服务层同步与冗余数据，业务方调用服务，新增数据，服务先插入t1数据，服务在插入t2数据，服务返回业务方新增数据成功。

优点:
不复杂，服务层由单次写，变两次写
数据一致性相对较高(因为双写成功才返回)
缺点:
请求的处理时间增加(要插入两次，时间加倍)
数据仍可能不一致，例如第二步写入T1完成后服务重启，则数据不会写入T2

---

服务异步双写:
数据的双写并不再由服务来完成，服务层异步发出一个消息，通过消息总线发送给一个专门的数据复制服务来写入冗余数据，业务方调用服务，新增数据，服务先插入T1数据，服务向消息总线发送一一个异步消息(发出即可，不用等返回，通常很快就能完成)，服务返回业务方新增数据成功，消息总线将消息投递给数据同步中心，数据同步中心插入T2数据。

优点:
请求处理时间短(只插入1次)
缺点:
系统的复杂性增加了，多引入了一个组件(消息总线)和一个服务(专用的数据复制服务)
因为返回业务线数据插入成功时，数据还不一定插入到T2中，因此数据有一一个不一致时间窗口(这个窗口很短，最终是一.致的)
在消息总线丢失消息时，冗余表数据会不-致

---

线下异步双写:
为了屏蔽“冗余数据”对服务带来的复杂性，数据的双写不再由服务层来完成，而是由线下的一个服务或者任务来完成，业务方调用服务，新增数据，服务先插入T1数据，服务返回业务方新增数据成功，数据会被写入到数据库的1og中，线下服务或者任务读取数据库的1og，线下服务或者任务插入T2数据。

	优点:
数据双写与业务完全解耦
请求处理时间短(只插入1次)
	缺点:
返回业务线数据插入成功时，数据还不一定插入到T2中，因此数据有一个不一致时间窗口(这个窗口很短，最终是一致的)
数据的一致性依赖于线下服务或者任务的可靠性

---

线下扫描全量数据法:
线下启动一个离线的扫描工具，不停的比对正表T1和反表T2，如果发现数据不一致，就进行补偿修复。

	优点:
比较简单，开发代价小
线上服务无需修改，修复工具与线上服务解耦
	缺点:
扫描效率低，会扫描大量的“已经能够保证一致”的数据
由于扫描的数据量大，扫描一轮的时间比较长，即数据如果不一致，不一致的时间窗口比较长

线下扫描增量数据法: 
每次只扫描增量的日志数据，就能够极大提高效率，缩短数据不一致的时间窗口，写入正表T1,第-步成功后，写入旧志logl, 写入反表T2，第二步成功后，写入日志1og2.还需要-个离线的扫描工具，不停的比对日志1og1和日志1og2，如果发现数据不-致，就进行补偿修复。

	优点:
虽比方法一复杂，但仍然是比较简单的
数据扫描效率高，只扫描增量数据
	缺点:
线上服务略有修改(代价不高，多写了2条日志)
虽然比方法一更实时，但时效性还是不高，不一致窗口取决于扫描的周期	

线上实时检测消息对法:
	向消息总线发送消息，写入正表T1，第一步成功后，发送消息msg1,写入反表T2，第二步成功后，发送消息msg2.需要一个实时订阅消息的服务不停的收消息。假设正常情况下，msgl和msg2的接收时间应该在3s以内，如果检测服务在收到msg1后没有收到msg2,就尝试检测数据的一致性， 不一致时进行补偿修复。
	优点:
效率高
实时性高
	缺点:
方案比较复杂，上线引入了消息总线这个组件
线下多了一个订阅总线的检测服务