数据库Oracle第4章.ppt

数据库应用技术第四章数据库对象与数据库设计,4.4索引,4.4.1索引的作用与使用,耗费硬盘空间、影响性能、不影响正确性好处创建唯一性索引，保证表中每行数据的唯一性。加快检索数据的速度，最主要的原因。加速表间的连接，在实现数据参考完整性方面特别有意义。检索数据时，显著减少分组和排序的时间。查询过程中，使用优化器，提高系统的性能。,为表中的每一个列都创建一个索引？坏处创建和维护索引要耗费时间，这种时间随着数据量的增加而增加。索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，需要的空间就会更大。当对表中的数据进行增加、删除和修改的时候，也要动态地维护索引，降低维护数据的速度。,4.4.2建立和使用索引的原则,建立索引取值范围较大的字段（B*树）NULL值比例较大的字段经常作为查询或连接条件的字段经常需要排序的字段期望返回的行数较少索引本身有足够的信息（WHERE和SELECT子句）能回答较多的查询时，相当于瘦版的表。,4.4.2建立和使用索引的原则,不建立索引较小的表经常更新的表不常作为条件或不常出现在条件表达式内的字段期望返回行数较多,使用索引装载数据后再建立索引。不应在一张表上建立太多的索引。删除不经常使用的索引。导入大量数据时，最好先删除索引，数据全部插入后，再重新创建索引。在经常存取的多个字段上建立复合索引，顺序应参照字段使用的频率确定。合理的索引要建立在对各种查询的分析和预测中，也取决于DBA的所设计的数据库结构。,4.4.3创建与维护索引,建立索引CREATEUNIQUEINDEXON()REVERSECOMPRESSUNIQUE：唯一性索引。REVERSE：反向键索引。COMPRESS：索引键压缩。,4.4.3创建与维护索引,删除索引DROPINDEX;修改索引ALTERINDEXREBUILD|REBUILDONLINE;REBUILD：重建索引，若其他用户正在表上执行DML，则重建索引失败。此时，可以使用REBUILDONLINE。,4.4.4索引的分类,逻辑设计单列索引和组合索引唯一性索引和非唯一性索引基于函数的索引物理实现分区索引和非分区索引B*树索引、位图索引和位图联接索引B*树：常规索引（正向键）和反向键索引,B*树索引扇出多，每步读入多，步骤少，适合数据库上的查找。举例：“性别”、“地区”“东北”,“西北”,“华北”,“地区”中有8个键值，若用B-Tree索引，可能一步查找到叶结点，但每个叶结点对应1/8的记录，要存表中1/8记录的指针。B*树索引对OR操作没什么益处。,位图索引(Bitmap)低基数，键值取值范围较少。对每个值建立一个位图，长度与表中记录个数相同。适合OR操作，修改代价大，应用在修改较少的字段上。,举例字段sidNUMBER(6)，取值类似000001000099，前四位较少用，此时若从后往前（右左）看，则更快。正常键：反向键：针对B*树上自然词序上下，左右。翻转键，适合使用较少值的情况。另外，对多个用户，通常将顺序打乱，插找树。当用ORDERBY子句时，需进行后排序。,4.5唯一性编号,目的：自动产生唯一编号。举例发票invoices(Invoices_noNUMBER(10)新来一张发票，为其定义下一个invoices_noSELECTMAX(invoices_no)+1ASv_noFROMinvoices;INSERTINTOinvoices(invoices_no,)VALUES(v_no,);问题：1、A、B用户同时添加新发票，将可能产生相同的invoices_no，并发处理问题。解决较复杂。2、无法解决“不断号”。如A申请101中死机，期间B申请了102，101断号。3、空表时：NVL(MAX(invoice_no),0)+1,Wrong！,目的：一般用来作为主键共享对象，标号发生器不依服于任何一个表。与MSSQL中类似的“自增长字段”不同。减少应用程序代码性能提高,创建序列，使用时取值CREATESEQUENCEINCREMENTBYSTARTWITHMAXVALUE|NOMAXVALUEMINVALUE|NOMINVALUECYCLE|NOCYCLECACHE|NOCACHEINCREMENTBY：序列号间隔，可为正或负整数，不可为0。缺省升序，为1。STARTWITH：第一个序列号。缺省为0。MAXVALUE：可生成的最大值。NOMAXVALUE：升序最大值1027，降序最大值-1。,创建序列，使用时取值CREATESEQUENCEINCREMENTBYSTARTWITHMAXVALUE|NOMAXVALUEMINVALUE|NOMINVALUECYCLE|NOCYCLECACHE|NOCACHEMINVALUE：序列的最小值。NOMINVALUE：升序最小值1，降序最小值-1026。NOCYCLE：累加到头后不循环，报错，手工恢复值。CACHE：预先分配个值到缓存中，加速。,修改序列ALTERSEQUENCEINCREMENTBYMAXVALUE|NOMAXVALUEMINVALUE|NOMINVALUE;修改未来序列值的增量。设置或撤消最小值或最大值。改变缓冲序列的数目。可以ALTER除初始值以外的所有参数，如果想要改变初始值，必须DROP后重新CREATE。,删除序列DROPSEQUENCE;使用序列.NEXTVAL.CURRVAL第一次NEXTVAL返回的是初始值,举例CREATESEQUENCEinv_no_seq;INSERTINTOinvoices(invoices_no,)VALUES(inv_no_seq.NEXTVAL,);NEXTVAL对序列的访问是修改式的，如撕日历。当多个表同步操作时，需要同一个值，怎么取？MASTER-DETAIL模式如：发票头，发票内可能有多行采购的详细信息。应用做为一个表，数据冗余将导致不一致。解决办法：分为两个表，一个记录发票头信息，另一个记录发票详细信息。,INSERTINTOinvoices_detail(invoices_no,line_no,)VALUES(inv_no_seq.CURRVAL,1,);CURRVAL对序列的访问时只读式的，去除刚刚产生的号。删除序列DROPSEQUENCE;,4.6索引,4.6.1索引的作用,要消耗一定的硬盘空间；对程序执行性能有所提高，但有无索引不影响程序的正确性。一个数据库对象（有物理存储）加快查找速度与表相互独立（物理、逻辑对立）基于表中某一个或及格字段（在字段的词序下，比较查找）被数据库自动使用。索引不一定能提高查询速度，数据库根据查询条件自动使用某个索引。减慢DML速度。如：插入操作，不需要索引，但却要为索引也插入行，以保持同步。,4.6.2索引的分类,创建索引自动：数据库自动创建索引，PK主键，UNIQUE唯一键；手工：对其他非PK、UNIQUE字段创建。CREATEBITMAPINDEXON();索引分类：唯一：加速，在CREATE后加UNIQUE不唯一：速度慢索引中不存放NULL值，索引字段值若为NULL,将不会放入索引,（1）B*树索引：（2）位图索引：CREATE后加BITMAP二叉查找为内查找方法，每一部查找都要从磁盘上读入新结点。二叉查找不适合数据库，因为从磁盘调到内存中的信息太过于频繁，使得查找麻烦。（1）B*树:扇出多，每部读入多，步骤少，适合数据库上的查找。二叉扇出为2有图,位图索引：低基数，键值取值范围较少，如：“胜别”，销售记录的分区，“东北”，集合无素数“西北”，“华北”，8个若用B-Tree,可能一步插找叶结点，每个叶结点对应勒1/8的记录，要寸1/8表记录的指针。且B-Tree对OR操作没什么益处。有图对每个值建立一个位图，长度与表中记录个数相同。适合OR操作，修改代价大，应用在修改较少的字段上。正常键：翻转键：,4.6.3索引的创建与维护,针对B-Tree上自然词序上下，左右。存在问题：如PIDNUMBER(6),前四位较少用，000001000099，此时若从后往前（右左）看，则更快。翻转键，适合使用较少值的情况。另外，对多个用户，通常将顺序打乱，插找树。当用ORDERBY子句时，需进行后排序。删除索引DROPINDEX名字；,在往库中导入大量数据时，最好删掉索引，全部插入后，再重新创建索引。插入多个结点的树的平衡性不如重建创建索引树的。,索引使用原则A.建立索引：（1）字段取值范围较大（B-Tree）（2）字段中NULL值比例较大（3）字段经常做为查询或连接条件（4）多数查询期望返回行数较少B.不建立索引（1）表较小（2）表经常更新（3）字段不常做为条件或出现在表达式内（4）期望返回行数较多,4.7其他数据库对象,4.7.1聚簇(cluster),如果一组表有一些共同的列，则将这样一组表存储在相同的数据库块中把相关的数据存储在同一个块上索引聚簇表(indexclusteredtable)散列聚簇表(hashclusteredtable)B*树聚簇索引(B*treeclusterindex),4.7.2分区(partition),将一个表或索引物理地分解为多个更小、更可管理得部分。每个分区都是一个独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。分区键(partitionkey)用于根据某个区间值(或范围值)、特定值列表或散列函数值执行数据的聚集。,4.8数据库设计初步,数据库设计初步,数据库设计的过程概念设计逻辑设计物理设计范式1NF：不存在多值的字段。（不能违背）2NF：多个主键时，不存在只由一个主键决定的信息如在orders里面加入顾客姓名3NF：不存在由非主键决定的其他信息如在AGENTS表中既有LID，又有CITY,违反范式的危害,违反范式可能产生两个问题：1.数据冗余数据冗余必然会带来数据不一致2.更新异常更新异常包括插入、修改和删除带来的异常也就是对一个表的修改，可以会破坏其他表的信息，或造成信息不一致，或者信息丢失。,实体设计注意的几个问题,实体某些属性实际上是多值的如姓名某些属性的值域是一个小的离散集合如省份，对应取值不应该直接存储，而应该设计一个对应的代码表，然后存储代码推广：一般数据库系统中都有一个类似的代码总表实体一般要有一个状态标志意味着实体的生存周期，也就是创建、生存和作废在生产数据库中一般不删除实体信息。还要有时间字段标志实体的产生时间。还很可能有一个录入员的信息。某些属性需要仔细考虑其划分的层次粒度如姓名、地址,主键的选择,业务主键和逻辑主键业务主键就是将业务中有唯一属性的字段挑出来做主键逻辑主键无实在意义的唯一性编号,选择原则,一、对于业务数据，最好采用逻辑主键;二、对于业务复合主键有多个字段，需要采用逻辑主键;三、对于基础数据，基于多方面考虑，是可以采用业务主键的。这类表初始化以后数据不会经常发生改变。四、取消业务主键后，在查询经常会用到的相关的业务字段建立INDEX，可以提高查询效率;五、使用逻辑主键,表的业务数据唯一性由程序来检查控制，使业务数据重复这类脏数据控制在业务允许的范围;六、业务数据的重复这类脏数据也可以通过分析结果数据得到;七、业务数据的逻辑主键使用numeric自增长型，在迁移数据时，取消目标表的自增长，数据迁移完成后，再重建逻辑主键。,