SQL 数据库对象.docx

SQL 数据库对象.docx

《SQL 数据库对象.docx》由会员分享，可在线阅读，更多相关《SQL 数据库对象.docx（29页珍藏版）》请在冰豆网上搜索。

SQL数据库对象

第十四章其它数据库对象

我们前面学习了表、视图，本章将简要的学习其它数据库对象。

＝＝＝＝＝＝＝

本章主要内容

＝＝＝＝＝＝

⊙创建、维护、使用序列器

⊙创建和维护索引

⊙创建私用和公用同义词

其中INDEX非常复杂，将来会在DBAI、中级、高级课程深入去讲

DBAI我们会初步了解索引，中级课程详细了解索引的优化，高级课程了解索引的INTERNAL结构、索引遍历及索引拆分的原则。

我们看看这些ORACLE的对象

ORACLE对象

＝＝＝＝＝＝＝

这些对象都可以在dba_objects里查的到,都是我们的SCHEMA对象,我们在第二章简单的介绍过.

⊙SEQUENCE:

用于生成类似主键的序列值,还可以应用到其它列

⊙INDEX:

索引用于提高访问数据的速度

⊙SYNONYM:

同义词用于设置一个对象的别名,等同于原始对象,以便于书写方便

我们首先来了解下SEQUENCE,我们称为”序列器”

SEQUENCE

＝＝＝＝＝

什么是SEQUENCE呢?

这个大家应该都了解一点，比如我们QQ群的学号等,我们在500群曾经有多个学生为一个学号250挣的死去活来,因为大家有并发,或者大家没有去判断别人有没有用了250号.而SEQUENCE很好的解决了这个问题。

序列是用户创建的数据库对象，它可以由多个用户共享，用来生成唯一的整数。

它通常用途是创建主键值，主键值对于每行必须是唯一的。

序列可以递增，也可以递减。

使用序列器代替应用控制，将节约时间，这是因为它可以减少编写生成序列的程序代码量，可以通过内置代码就可以实现。

SEQUENCE的好处

＝＝＝＝＝＝＝＝

⊙我们的SEQUENCE就是来解决如上的并发和性能问题。

即可以避免并发产生重复值,SEQUENCE是一个原子事务,取了值就已经生成,不可能再生成重复数据，保证每个请求生成一个唯一值。

⊙可以按规律以特定的间隔生成一个数值,而我们的SCN就是一个类似序列器来生成的

⊙就如学号，那如果没有SEQUENCE，那你必须判断学生表里学号最大的值，然后加1,这个要涉及到分组和表扫描,显然是不利的,那我们会采用把最大值保留在一个对象里，下次去就选择最大值加间隔值,这样就可以不去扫描实际的表了。

SEQUENCE特征

＝＝＝＝＝＝＝＝

我们看SEQUENCE的特征

⊙SEQUENCE是会自动按递增或者递减生成一个唯一的号，它是整型。

⊙SEQUENCE是对象，我们不同的会话可以去读取该SEQUENCE，所以它是共享对象，它会做为共享对象保存在LIBRARYCACHE中

⊙它常用在主键上生成主码

⊙可以代替应用代码实现唯一值的序列号，而且序列号在并发访问时不存在读取出重复值，它不基于外部的事务。

⊙将SEQUENCE的一批值预分配到内存，这样能更快速访问SEQUENCE，这个就是CACHE的功能。

SEQUENCE语法

＝＝＝＝＝＝＝＝

这里是SEQUENCE的语法

选项属性

⊙INCREMENTBYN，N表示每次增长多少，指定序列号之间的间隔。

⊙STARTWITHN，表示初始SEQUENCE从哪个值开始。

指定生成第一个序列号

⊙[NO]MAXVALUE用来设置序列号最大值，我们通常设置默认值，[NO]MAXVALUE默认值看到的是10^27

⊙[NO]MINVALUE定义最小值，同上面参数类似

⊙CYCLE，是一个循环的值，当你增长达到MAXVALUE时，SEQUENCE是否让它将序列号轮转回来变成1，NOCYCLE表示不轮转。

所以主键列不设置CYCLE

⊙CACHE和NOCACHE就是你每次在内存里预保存多少个SEQUENCE。

这样，你NEXTVAL时只要从内存取就行了。

这里会提高读取的效率，预计算可以减少读取时所消耗的计算资源，更大的优势是在RAC中通过预取一个比较大数量的序列号，避免多个节点访问同一块而产生PING或者内存同步产生的性能影响，减少热块。

我们看例子

有一个序列号SEQ_ACC_BANKBAT,当前值是150000，在RAC1CACHE20000个序列号，范围为150001～170000，在RAC2上CACHE20000个序列号，范围为170001～190000

那你在

RAC1>INSERTINTO...SEQUENCE.NEXTVAL是150000

RAC2>INSERTINTO...SEQUENCE.NEXTVAL是170000

这样连到两台节点的INSERT不会插入到同一索引块（因为索引键值是顺序的），

避免块冲突，也避免同一块在节点内存间同步。

但是这里要注意：

因为SEQUENCE预存在内存，可能存在着被PAGEOUT或者内存丢失.

⊙ORDER和NOORDER

这个参数表示我的SEQUENCE必须是连续的

那如前面CACHE中150000跳到170000是不允许的

所以ORDER与CACHE是冲突的

设置了CACHE就必须是NOORDER

创建SEQUENCE

＝＝＝＝＝＝＝

上图是创建SEQUENCE的例子

主键不要设置CYCLE

我们知道NEXTVAL一执行就增大一个间格，那有什么方法去查下一个值是什么?

我们可以通过数据字典来查看SEQUENCE的信息，user_sequences可以查看SEQUENCE名是不是存在，存在表示创建的正确。

如果设置了NOCACHE，可以查询下一个有效的值（LAST_NUMBER）。

如果是CACHE，在内存里的值由指针控制

那我们怎么样在应用中读取下一个值或者当前值

NEXTVAL/CURRVAL

＝＝＝＝＝＝＝＝

有两个伪列来实现上面功能

⊙NEXTVAL来返回下一个值

⊙CURRVAL来返回当前值

注意：

刚设置SEQUENCE时还没有初始化，只有一个起始值，当前还没有用到值，所以CURRVAL必须获取NEXTVAL后才会有，否则没有值

然后NEXTVAL，CURRVAL是当行操作，一次只有一个值，不能用在多行结果集或者集合运算

NEXTVAL是一个原子事务,取出了就被确认了

NEXTVAL和CURRVAL的规则

＝＝＝＝＝＝＝＝＝＝＝＝

可以使用在下面的情况

⊙非子查询的SELECT列表中

⊙INSERT语句中子查询的SELECT列表

⊙INSERT语句的VALUES子句中

⊙UPDATESET子句中

不允许出现NEXTVAL/CURRVAL的地方

⊙视图的SELECT的列表

⊙有DISTINCT限制的SELECT列表

⊙带有GROUPBY,HAVING,ORDERBY子句的SELECT语句中

⊙DELECT,UPDATE操作可更新视图中，不允许出现NEXTVAL/CURRVAL

⊙DEFAULT表达式

使用SEQUENCE

＝＝＝＝＝＝＝＝

上图是应用SEQUENCE的例子，插入一个SEQUENCE值

我们CACHESEQUENCE是为了更快速的拿到值，而且在RAC中避免索引相关列上索引块的冲突

但是因为数字CACHE到内存，就意味着有丢失的风险，因为你实际的SEQUENCE已经是内存被CACHE的最大序列值+1了

那SEQUENCE什么时候在业务数据里会产生间隙呢?

造成序列号在字段上不连续.

⊙你回滚掉事务，那SEQUENCE还是前进了,这个是不可避免的。

不放在CACHE里也会丢失。

⊙系统崩溃,那就意味着内存被释放,原来数字就没了，但是序列当前值已经是包括了丢失值之后的。

⊙SEQUENCE被另一个表使用，因为SEQUENCE实际上并不是直接跟表关联,共享SEQUENCE甚至可以被别的表使用。

⊙其实还有种可能,类似于2。

就是说把内存值被刷掉

⊕被别的共享池对象挤出去

⊕altersystemflashshared_pool

那我们有什么办法不被刷呢,就是把这些对象PIN住

你可以把SEQUENCE用DBMS_SHARED_POOL.KEEP到LIBRARYCACHE

就不会造成你刷新内存而成为GAP

SQL>execdbms_shared_pool.keep（'SEQ_ACC_BANKBAT','q'）;

PL/SQL过程已成功完成。

Q是SEQUENCE类型

我们来看一个例子

在没有KEEP到共享池时，刷新共享池后丢了2-20的序号。

在KEEP到共享池时，刷新共享池后没有丢失序列号。

维护SEQUENCE

＝＝＝＝＝＝＝＝

接下来学习SEQUENCE的维护,我们可以修改SEQUENCE的定义

最简单方法,大家可以用PLSQLDEV或者TOAD

我们看到下面几个参数是可以修改的

语法如下

我们看看修改的一些注意事项

⊙要修改SEQUENCE，你必须拥有ALTERSEQUENCE的权限

⊙只影响新的序列号,不影响老的数据

⊙如果要从不同的序号处重新开始，则必须删除原有的序列，然后重建。

⊙另外还有一些限制

比如改成序列号比最大序列号还大

我们还可以学会怎么删除SEQUENCE

删除序列器

＝＝＝＝＝＝

删除SEQUENCE

这里执行了DROPSEQUENCE后,SEQUENCE从字典里清除。

需要DROPANYSEQUENCE权限

接下面我们来学习索引

＝＝＝＝

INDEX

＝＝＝＝

这是只涉及基础知识，所以我们不会讲的太深

⊙它是一个SCHEMA对象

⊙它通过行的指针来加速获取数据

⊙能用很少的磁盘IO快速定位数据

因为有了第二条的功能才会有第三条的效果

⊙索引跟表是独立的对象,逻辑上关联。

⊙　被ORACLE自动维护

我们来看下索引结构来理解下,为什么索引能瞬速定位

B树索引的结构

＝＝＝＝＝＝＝

上图就是一个索引结构

是一个B树,也就是平衡树（B是平衡的意思）

也就是你不管扫描哪个最终的值,都需要遍历同样的高度

我们看到红色框每个叶节点都要扫三层

它的高度是3,LEVEL=3

这样就很公平,而且不会出现某些值遍历的非常高，而另一些值遍历非常高度非常短

我们看索引的DML操作

索引的才拆分

＝＝＝＝＝＝

我们上图插入了36,删除65的键值造成索引块拆分。

我们看现在30,67是根节点，15,21等是分支节点，2,7等是叶节点,我们看到蓝色框每个节点有固定的个数值（比如2个值）,如果不足两个必须用保留的空值填充值两个键值,比如78所在的节点

如果节点的KEY值有N个,那指针就有N+1个

索引的遍历

＝＝＝＝＝＝＝

那比如我们写SQL

WHEREBTREE=29

⊙索引的遍历

你首先找到根节点的最左边指针

接下来一个中间节点,15和21

应该选择在21右边的指针,该指针是21-30之间的

再接下来到叶节点从左到右扫描这个键值

因为叶节点也只有跟中间节点相同的KEY数

OK,大家可以看到用索引,只要访问3个节点块

遍历过程看黄色线

共3次索引IO加1次表IO（实际情况还要多点）

⊙全表搜索

全表搜索,搜索了6块找到了29,我们看第1个箭头处已经找到了29，但是由于表扫描不存在唯一性约束，所以可能有多个29，不知道后面还有没有29,所以一直扫描到表的最后,共有14个IO

下面就是插入2个值后，oracle自动维护这些索引节点

索引的节点拆分更加复杂，这些在高级课再讲到

创建索引

＝＝＝＝＝＝

那我们如何创建索引呢?

两种方式

⊙自动:

主键约束和唯一性约束

⊙人工:

创建非唯一性索引

ORACLE也可以人工创建唯一性索引，但是推荐建立唯一性约束来自动创建唯一性索引。

因为唯一性索引也就意味着该列不可能有重复，也就意味着对数据进行了唯一性约束

那我们看看创建语句是什么样的

上图是创建索引的语法和例子

这里你要写索引名和ON在哪个表上并括号选择在哪些列上创建索引

这个语句只是创建普通索引,其实ORACLE为支持海量数据库,还有很多索引类型

比如,BITMAP,REVERSE,IOT等

我们这里不讲

创建索引时必须有权限创建

因为要起码能访问表的权限

如果是OWNER的表,那没有问题

如果是别的用户的表，你需要

CREATE[ANY]INDEXprivilege

那什么时候该建索引,什么时候不该建

何时创建索引

＝＝＝＝＝＝

索引多并不意味着性能好，索引对于查询是有利的，但对DML是有弊的

⊙一个字段包含一个较大范围的值,也就是说重复值比较小。

比如日期2000-1-1号到2009-2-19

这是一个大的范围,我们去搜索一个小的范围或者精确找一个值的时候索引就非常有效

⊙一个列中包含有大量的NULL值。

有人说NULL值不是不存放在索引里吗（CLUSTER除外）?

那为什么要建？

如果你在这个列上经常查有值的语句时,有大量NULL值的索引效率非常高。

因为索引不包括NULL值,如果大量是NULL值,意味着这个索引非常小。

那去查询一个有效值效率非常高。

那如果查询NULL值时效果就很差了。

⊙一个或多个列在WHERE子句或者JOIN条件中频繁出现的列,你要关注是否建立索引。

但是这个是有前提条件的，条件就是下面一点。

⊙这个表非常大，大多数查询返回出来的结果集占总的结果集的2%-4%。

其实这个2%-4%只是一个评估值,有时10%也好,只需要了解索引访问成本的算法,这在CBO里讲。

这个算法会比较复杂,涉及的内容也比较多,我们就跳过了。

何时不创建索引

＝＝＝＝＝＝＝

⊙表很小时。

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

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

⊙如果该列上返回的结果集大于总结果集的2-4%,谨慎考虑建立索引，这些还是需要CBO知识。

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

数据仓库90%以上是查询，OLTP60-70%是查询。

如果DML语句超过60%建立索引不易太多,不过还是要结合实际的表，通常的OLTP还是查询多。

⊙这个索引列参考了表达式。

这通常用不到索引。

所以要看是不是建立函数索引，否则没有意义。

这里注意WHEREcolumn_nameISNULL不会使用索引。

WHEREcolumn_nameISNOTNULL会考虑索引。

我们可以通过数据字典来查看索引情况。

这里列出了两个：

一个是索引的摘要,一个是索引列的详细信息

⊙USER_INDEXES

⊙USER_IND_COLUMNS

当然还有些别的索引数据字典,比如索引统计等。

大家如果记忆不住,你可以用下面语句

selectobject_namefromdba_objectwhereobject_namelike'%IND%'

我们看下索引概要

USER_INDEXES

＝＝＝＝＝＝

它显示了索引名,索引的列和列在表中的序列号,索引是否唯一

我们接着讲另一种特殊索引叫函数索引

函数索引

＝＝＝＝

如果你在SQL语句的WHERE条件中经常用到表达式,那么你就用不到索引,

比如

有个索引字段BTREE,有一个SQL语句

WHEREBTREE/100=0.29

BTREE/100是一个表达式

它算出来的结果才去匹配索引节点上的KEY

这时索引上面没有0.29这个KEY，很显然

WHEREBTREE/100=0.29＝＝不走索引

WHEREBTREE=29      ＝＝走索引

但其实我在表里查的是同一条记录。

那我们用什么方法来解决呢?

我们可以让索引节点上的KEY变成全部缩小100倍

这就需要函数索引来解决

createindexfunc_idxonBTREE（BTREE/100）;

函数索引允许大小写不敏感，还要设置查询重写和信任关系，我们将在中级课程介绍

ORACLE自动去匹配大小写

比如

BTREE/100

btree/100

而且默认是DESC（普通索引是asc，只有函数索引是DESC，ASC跟DESC不影响索引效率）。

索引还有其它类型的索引,我们这里不讲了

可以用反转函数来实现LIKE非前导字符的匹配

如NAMELIKE‘%峰’

可以用REVERSE（NAME）

那相当于变成LIKE‘峰%’

参见第三章

删除索引

＝＝＝＝＝＝

用DROPINDEX删除索引

接下来学习下一个ORACLE对象

＝＝＝＝＝

同义词

＝＝＝＝＝

同义词是我的一个对象想给不同用户使用,而不想在前面加上USER.TABLE_NAME

比如SCOTT.BTREE

同义词就是在别的用户上建立一个别名,LINK到原用户对象表中。

这样就不用输入用户名.btree

如果全部用户都可以用这个别名就是PUBLIC同义词

同义词的维护

＝＝＝＝＝＝＝

第十四章完