SQL 数据库对象.docx
- 文档编号:27944282
- 上传时间:2023-07-06
- 格式:DOCX
- 页数:29
- 大小:1.15MB
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同义词
同义词的维护
=======
第十四章完
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 数据库对象 数据库 对象