oracle的段

Oracle段（ segment ）

Oracle中最重要的一个逻辑结构，因为用户直接操作的就是对象，对它进行增删改查等。

 

 

概念：一个堆表所占用所有物理空间, 这里的 " 所有物理空间 " 就指的是一个段 .( 分区表除外 , 分区表有多个段 ) ，段是占有一定存储空间的对象 可以理解为是对占用空间的对象的另一种命名方法，表是段 , 索引也是段，上图可以看到段可以跨越多个数据文件，一个表空间由一个或多个段组成 一个段由一个或多个区组成。

在这里我主要介绍比较主要的段：

1、 表

2、 索引

3、 回滚段

 

一、 表（table ）

表是存储数据的基本单元，由行和列组成，以行为基础存储，它是段的一种，由extent 组成。表的具体存储我会在数据块那里详细讲一下，这里就不在细讲了。

表中又分为普通表和临时表，普通表比较简单就是大家经常操作的表，也是真实存储数据的，这里详细介绍一下临时表。

临时表的特点：

1、多用户操作的独立性：对于使用同一张临时表的不同用户， ORACLE 都会分配一个独立的  Temp Segment ，这样就避免了多个用户在对同一张临时表操作时发生交叉，从而保证了多个用户操作的并发性和独立性；

2、数据的临时性：既然是临时表，顾名思义，存放在该表中的数据是临时性的。比如 , 购物网站的购物车系统 , 用户选购的过程就是一个 session, 被选物品存在临时表里当结算完物品 购物车内的数据即没有意义了

 

临时表又可以分为事物级和会话级：

1、 事物级：

 On Commit Delete Rows

数据在 Transaction  期间有效，一旦 COMMIT 后， rollback, 断开连接 , 数据就被自动  TRUNCATE  掉。

 

 

2、 session级：

        On Commit Preserve Rows

数据在 Session  期间有效，一旦关闭了 Session  或  Log Off  后，数据就被  ORACLE  自动  Truncate  掉。

 

创建临时表：

create global temporary table t_tmp_tab (id number,name varchar2(20)) on commit delete rows /on commit preserve rows;

 

临时表创建默认创建在临时表空间中。

 

二、 索引（index ）

通过行的指针(ROWID) 来加速获取数据，能用很少的磁盘 IO 快速定位数据，索引跟表是独立的对象 , 逻辑上关联，被 ORACLE 自动维护  ( 不完全这样 , 有的时候索引会失效 , 我们要 rebuilt) ，是数据库自己来决定是否使用的，所有的索引都是树状结构的，是有序的。

索引可以分为：B-TREE 和 BITMAP

这里主要讲一下B-TREE 索引， BITMAP 索引暂时不在此讲解

1、 B-TREE

 

查看索引的情况：

analyze index T1_EMPNO_IDX validate structure;

Select  height， blocks ， br_blks ， lr_blks ， lf_rows ， del_lf_rows from  index_stats ；

 

快速定位的意思就是无论找图中红的哪个数据都只需要三步

三步是由索引的高度来决定的

 

一、分支节点: 实际上根节点也是分支节点块

    对于分支节点块（包括根节点块）来说，其所包含的索引条目都是按照顺序排列的，每个索引条目都具有两个基本字段

第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值

第二个字段为四个字节，表示所链接的索引块的地址，该地址指向下面一个索引块

其中的0 、 500 和 1000 分别表示这三个分支节点块所链接的键值的最小值

而B1 、 B2 和 B3 则表示所指向的三个分支节点块的地址  Branch 的 B

而L1 、 L2 和 L3 则表示所指向的三个叶子节点块的地址  Leaf 的 L

 

二、叶子节点:

对于叶子节点块来说，其所包含的索引条目与分支节点一样, 都是按照顺序排列的

每个索引条目也具有两个字段

第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的

第二个字段表示键值所对应的记录行的ROWID ，该 ROWID 是记录行在表里的物理地址

其中的0 、 29 和 190 分别表示这三个分支节点块所链接的键值的最小值

而R1 、 R2 和 R3 则表示所指向的三个键的 ROWID 的值  ROWID 的 R

如果索引是创建在非分区表上或者索引是分区表上的本地索引的话，则该ROWID 占用 6 个字节

如果索引是创建在分区表上的全局索引的话，则该ROWID 占用 10 个字节。

 

三、索引是oracle 自动维护的

当DML 发生时 我们改的是表  oracle 却在后台要维护索引的组织结构

所以说索引会增加DML 的负担

但也要有量级控制 比如从1 千万记录中 delete10 行 比例千万分之十 找到要改的记录不走索引就太艰难了 . 反之亦然 

一般来说以操作表内数据量的60% 为界限  60% 经常改 其余 40% 是被查询不修改  DML 语句的频度甚至大于 DQL 语句

低60% 则加索引 高则不加索引 而且还要参考 SQL 的

对于OLTP 来说 基本都是查多改少 .

 

四、何时建索引

索引多并不意味着性能好, 索引对查询有利 对 DML 是有负担的 .

1.一个字段包含一个较大范围的值 , 也就是说重复率比较小，就是所谓的高基数列，比如员工编号 入职日期，在一个大范围内去找小范围或精确的单个或几个值时 索引非常有效

2.一个列包含大量的 NULL 值

  索引是不存NULL 值的 , 那也就意味着这个索引很小 , 查起来很快

  但只限于查明确值，因为你写 where a=1  走索引时就意味着你写的是  where a is not null and a=1 。 但查的是 NULL 值 效率会很低

3.频繁在 where 子句中出现的列 我们要关注是否建立索引

4.大表的外键列 需要建立索引 因为主键值的 DML 操作会间接的去外键中验证是否在外键列引用

  外键列此时没索引 就要全表扫描 所以一个主键只有几条记录 外键无索引一个DML 可能会几个小时 ...

 

五、何时不创建索引

1.这个列虽然索引效果很好 , 但是你业务中很少去使用这个列去查询的 , 考虑不要建索引 , 以均衡索引的成本 .

2.如果该列上返回的结果集大于总结果集的 2-4%( 看总的数据量和 CBO 算法来定 这只是参考值 ), 谨慎考虑建立索引 .

3.表很小时。因为很小表的时候全表访问比索引访问成本还低 , 有没有索引影响不大

4.频繁更新的表 , 其频繁甚至超过查询 , 慎重考虑建立索引

5.这个索引列使用了函数表达式 需要创建函数索引

 

六、基本操作：查看索引

USER_INDEXES

user_ind_columns

index_stats

 

七、普通索引的创建

自动创建

建立主键和唯一约束时自动创建唯一索引

手动创建

CREATE INDEX index_name ON table(COL1[COL2,COL3...]);

多个column 代表复合索引

索引的命名最好有可读性

比如在emp 的 deptno 表建立索引 取名为  emp_deptno_idx

创建的条件

要对表有访问权限

有create index 权限

 

 

 

 

三、 回滚段(rollback)

回滚段创建的时候是空闲的，只有事务产生时候才会产生数据。一个事务的数据存在回滚段中如果提交则不会再有人引用该数据，一段时间后就会自动没清处。

作用：

1、 在交易的回退——保留更改前的老数据，为了后悔

2、 交易的恢复，提交已提交的，回退未提交的

3、 读一致性

A、 存储未提交的数据，其他会话隔离

B、 查询开始以后提交的不可见

C、 查询时结果已确定

4、 闪回已提交的事务

查看回滚段中关于某一表的操作版本，这些版本都可以恢复。

Select versions_starttime,versions_endtime,versions_xid,versions_operation,sal from

scott.Emp versions between timestamp minvalue and maxvalue  order by versions_endtime,versions_startime;

查看某一个操作回滚的sql 语句：

Select undo_sql from flashback_transaction_query where xid ='';

查看有那些回滚段

Select segment_name from dba_undo_extents;

查看那个回滚段是有事务的（活动的事务）

Select xidusn from v$transaction;

 

一般有10 个用户可以用回滚段，在使用时随机选择一个空闲的回滚段，当超过 10 个后

1、 空间富裕则建立新的回滚段

2、 不能建立新的情况则2 个事务使用一个回滚段

3、 没有空间则报错，缺失回退