《索引和约束条件》PPT课件.ppt

使用索引和约束条件,人民银行郑州培训学院信息部潘汉杰2005年10月26-27日,开始新内容之前先完成以下建表操作：,表名：选课表:xkb列名：课程号：kchchar(4)课程名称：kcmcvarchar2(20)学分：xfnumber(2)建表及数据录入请自已使用create和insert命令进行。,使用索引和约束条件,索引的概念数据库的索引可以将索引概念应用到数据库表上。当一个表含有大量的记录时，Oracle查找该表中的特写记录要花很长的时间就像花很长时间翻看全书来查找某个主题一样。Oracle有一个易于使用的功能，即可以建立一个次隐藏表，该表包含主表中的一个或多个重要的列，以及在主表中的指相应行的指针。在这里，与书的页码相对应，该隐藏的次表（即索引表）中的指针就是行号。通过访问索引，Oracle可以准确地知道要,使用索引和约束条件,查找的特定数据在哪一行上。由于索引比引用它的表要小得多，因此用索引查找表中的数据比不用索引的表明赤地快。在一个拥有百万条记录的表中选择与某值相匹配的记录花了18.9秒，当建立该表的索引后，同样的查询只需要0.6秒就能完成。在一个大表中增加索引能使查询速度快几十倍。看图1中的一个索引表:,图1带有一个索引表,使用索引和约束条件,一旦建立了表的索引，Oracle自动使该表与表保持同步。对该表的任何INSERT、UPDATE或DELETE操作也自动修改索引，并且，如果索引中含有SELECT语句所需的列，则表的任何SELECT操作都将自动经过索引。增加或删除索引不会影响表的操作以前使用该表的任何程序现在仍将可以运行，但是操作速度可能变慢。如果删除表，则相关的索引也将自动删除，因为没相关表的索引是没有意义的。删除索引的语法如下：DROPINDEXindex_name,使用索引和约束条件,索引何时有用对于必须读取表中内容的命令，索引改善了它们的响应时间。这意味着SELECT、UPDATE和DELETE命令都能够更快地进行运行，如果该表有对应于这些命令的列的索引。但是增加表的索引并不能提高INSERT命令的输入数据的速度，相反还要降低运行速度。因为索引本身实际上是一个表，因此当对表添加一个记录时，Oracle必须做两次插入。这样，组表增加一个索引将导致插入操作要两倍多的时间（两倍的时间用于两次插入，另外还需要一点时间处理二表之间的同步）。增加两个索引将使用插入操作花三倍时间，三个索引使插入操作花四倍的时间，等等。因此索引的使用要权衡利弊。它们会使数据输入花费更长的时间，但却使读取数据的速度提高。所以，需要快速进行数据输入的应用程序最好不,使用索引和约束条件,要增加表的索引。例如，超市中的销售系统，应使收很机尽可能快地围绕销售事务进行工作（即直接插入到数据库中）。这时，若给存储事务的表增加一个索引将是错误的，因为它会使插入变变慢。另一方面，在同一时刻可能会有经营管理人员想要执行查询来分析事务。记住：表越大，从表的索引中获得的好处就越多。表2是在一个百万条记录的表上使用和不使用索引时，执行各种DML操作所花费的时间：,使用索引和约束条件,如何建立索引建立索引很简单，命令的语法如下：CREATEINDEXindex_nameONtable_name(column_name);如果想要索引包含一个以上的表列，其语法如下：CREATEINDEXindex_nameONtable_name(first_column_name,second_column_name);,使用索引和约束条件,用上面建立索引的命令建立索引：createindexcode_indexonperson(person_code);createindexperson_name_indexonperson(last_name,first_name);请对建立的其它表建立索引进行测试。说明：在标准Oracle索引中所能包含的最大的列数为32。,使用索引和约束条件,索引类型1、B*树索引Oracle组织记录的缺省索引类型称为B*树。图3示出B*树索引是如何组织记录的。,图3B*-树索引如何组织记录,使用索引和约束条件,当建立一个B*-树索引时，Oracle分析被索引的列的值，确定如何将表分成记录数量相等的叶块，然后建立枝块层，以便使叶块层的记录用尽可能少的步骤确定。上图的例子中，枝块是按字母顺序均匀分割的。在实际中，分枝点由记录的值确定。例如，如果一个表含有的以“A”开头的记录比其他字母的多得多，那么可能一个完整的枝块都是“A”的，下一个枝块则从“B”开始。B*-树索引的优点是它可使Oracle快速地确定不需要读取的记录。通过使必须读取的数据数量最小化，Oracle将更快地返回结果。,使用索引和约束条件,由于B*-树索引通过根据内容将数据划分成集合和子集合来工作，因此这种类型的索引适合于被索引的列中包含大量不同值的情况，则位图索引更适合。2、位图索引B*-树索引结构最适合于检索含有大量不同值的列，那么，显而易见，另一种索引结构对于只包含很少的不同值的列更有效。例如，性别的列将只含有三种可能值，即“M”（男）、“F”（女）或“U”（未知）。将数量很少的不同值放入B*-树索引结构中没有意义，因为B*-树的“逐步划分成子组”的方法对只有少量的不同值的情形几乎无用。在这种情况下，使用位图索引更有意义。图4给出了对如何设计位图索引的一个简单描述。,使用索引和约束条件,图4位图索引如何组织记录,使用索引和约束条件,在WHERE子句为一个低基数列的SELECT查询中，预先建立该列的位图索引可大大减少检索花费时间。该速度的提高是两方面的结果：1）位图索引相当地小；2）存储在位图索引中的“1”或“0”值可以被计算机很快地计算。建立位图索引的语法如下：CREATEBITMAPINDEXindex_nameONtable_name(column_name);保持数据的完整性：约束条件什么是约束条件约束条件（constraint)是定义一个或多个条件的一种方法，用户的输入在被Oracle接收进表之前必须满足这些条件。,使用索引和约束条件,怎样建立约束条件我们将学习如何建立三种不同的约束条件。当一起使用时，这些约束条件对于确保表不的数据是“干净”的将大有帮助。1）NOTNULL非空，一旦使用该项约束条件，那么被约束的列不能为空值。在建立表的时候，使用“非空”约束。例如：CREATETABLEtest_constraint(product_namevarchar2(20)NOTNULL,product_priceNUMBER(4,2)NOTNULL,purchase_dateDATE);,使用索引和约束条件,如果表已经建立，则可以通过更改已有的表，使得当插入或更新记录时，列不再接收空值。将已有列修改为NOTNULL状态的语法如下：ALTERTABLEtable_nameMODIFY(column_nameNOTNULL);例如：ALTERTABLEpersonMODIFY(last_nameNOTNULL);ALTERTABLEpersonMODIFY(last_nameNOTNULL);通过输入代码可以测试约束条件。,使用索引和约束条件,2）UNIQUE唯一值约束条件。添加唯一值约束条件的语法结构如下：ALTERTABLEtable_nameADDCONSTRAINTconstraint_nameUNIQUE(column_name);例如：ALTERTABLEpersonADDCONSTRAINTunique_testUNIQUE(first_name,last_name);可通过使用插入记录的语句进行测行。,使用索引和约束条件,3）Check检查（check）约束条件允许定义为了输入的数据被Oracle数据库接收，数据必须满足的条件。可以给表的每个列定义一个检查约束条件。例如，可以要求价格列的数据必须为正值，日期列的值在某个范围内。检查约束条件是保证数据库为干净数据的最有力的工具之一。在一个已有表中建立某列检查约束条件的语法如下：ALTERTABLEtable_nameADDCONSTRAINTconstraint_nameCHECK(column_namecondition_to_satisfy);,使用索引和约束条件,完成以下操作认真观察运行结果：例1：createtabletest(agenumber(2);altertabletestadd(constrainttest_checkcheck(ageisnotnullandage10);insertintotestvalues(12);insertintotestvalues(10);insertintotestvalues(9);commit;,使用索引和约束条件,例2：altertablepurchaseadd(constraintcheck_purchase_datecheck(purchase_dateisnotnullandto_char(purchase_date,YYYYMMDD)20020630);insertintopurchasevaluse(SmallWidget,10,28-5月-02,GA);insertintopurchasevaluse(SmallWidget,9,28-6月-02,GA);insertintopurchasevaluse(SmallWidget,10,28-7月-02,GA);,使用索引和约束条件,启用和禁止已有的约束条件约束条件可以禁止使用也可以重新加载，而不用永久地删除它。语法如下：ALTERTABLEtable_nameDISABLECONSTRAINTconstraint_name;重新启用约束条件的语法为：ALTERTABLEtable_nameENABLECONSTRAINTconstraint_name;例如：altertabletestdisableconstrainttest_check;insertintotestvalues(7);insertintotestvalues(9);altertabletestenableconstrainttest_check;（为什么出错？如何插入下列数据？）insertintotestvalues(12);insertintotestvalues(13);,使用索引和约束条件,更改和删除已有的约束条件生活不可预测，需求在不断地改变，某些时候需要修改或删除某个表的已有约束条件。修改表的约束条件的语法如下：ALTERTABLEtable_nameMODIFY(column_nameNULL);例如：altertabletestmodify(test_checknull);altertabletestmodify(test_checknotnull);如果想要删除整个约束条件，可使用以下语法完成：ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name;注意：删除约束条件是一个永久性的操作。如果你认为今后还会需要该约束条件可考虑禁用它来代替删除它。,使用索引和约束条件,例如：altertabletestdropconstrainttest_check;表之间的关系考察下面三个表之间的联系,使用索引和约束条件,使用约束条件加强表之间的联系为了使两个表之间存在联系，必须使两件事正确：1）父表必须有一列（或一组列）唯一标识它所包含的每个记录。（称为主码或主键）2）子表必须有一个相同的列（或一组列）包含唯一标识父记录的值。（你为外码或外键）,使用索引和约束条件,（1）建立主键如果表已存在用如下语句：ALTERTABLEtable_nameADDPRIMARYKEY(column_name1,column_name2,);例如：createtablest(st_nonumber(4),namechar(10);altertablestaddprimarykey(st_no);当建立主键时，Oracle自动利用该主键在该列（或几列）上建立一个索引。,使用索引和约束条件,完成以下操作建立学员情况表(xyqkb)：createtablexyqkb(xhnumber(4),xmchar(10),dhchar(15);insertintoxyqkb(2001,张三,037165648733);insertintoxyqkb(2002,李四,037165648734);insertintoxyqkb(2003,王五,037165648735);insertintoxyqkb(2004,赵六,037165648736);insertintoxyqkb(2005,吴七,037165648737);commit;altertablexyqkbaddprimarykey(xh);,使用索引和约束条件,主键也可以在建表时直接建立：如createtablexyqkb(xhnumber(4)primarykey,xmchar(10),dhchar(15);,使用索引和约束条件,（2）建立一个外键约束条件主键和外键是建立表之间联系的物理组成部分。但是，由它们自己并不能保证联系的完整性也就是说，即使主键和外键的列都有完全相同的名字和数据类型，但是，Oracle并不认为它们之间有关系，除非你加以确定。即必须在子表中定义一个约束条件，使得它在接收数据进入其外键之前先检查父表的主键。如果没有这样的约束条件，用户可能会将父表中实际不存在的值输入给子表的外键。,使用索引和约束条件,建立外键约束条件的语法如下：ALTERTABLEchild_table_nameADDCONSTRAINTconstraint_nameFOREIGNKEY(column_name(s)_in_child_table)REFERENCESparent_table_name;完成下面的操作建立一个学员成绩表(xycjb)：createtablexycjb(xhnumber(4),kcbhnumber(4),cjnumber(3)；altertablexycjbaddconstraintxh_fkfroeignkey(xh)referencesxyqkb;,使用索引和约束条件,观察并分析出错情况：insertintoxycjbvalues(2000,1001,90);insertintoxycjbvalues(2001,1003,85);insertintoxycjbvalues(2002,1002,88);insertintoxycjbvalues(2001,1001,84);insertintoxycjbvalues(2003,1002,80);insertintoxycjbvalues(2002,1002,67);insertintoxycjbvalues(2003,1003,87);insertintoxycjbvalues(2004,1004,89);insertintoxycjbvalues(2004,1001,83);insertintoxycjbvalues(2005,1002,82);insertintoxycjbvalues(2005,1004,88);,使用索引和约束条件,完成下面的操作建立一个选课表(xkb)：cteatetablexkb(kcbhnumber(4)primarykey,kcmcvarchar2(20),xfnumber(3);试试能不能完成以下操作，为什么？altertablexycjbaddconstraintkcbh_fkforeignkey(kcbh)referencesxkb;,使用索引和约束条件,向选课表（XKB)中插入记录：insertintoxkbvalues(1001,高等数学,5);insertintoxkbvalues(1002,线性代数,4);insertintoxkbvalues(1003,普通物理,5);insertintoxkbvalues(1004,程序设计,6);commit;altertablexycjbaddconstraintkcbh_fkforeignkey(kcbh)referencesxkb;,使用索引和约束条件,由系统自动维护，与用户有关的几个表：user_tablesuser_constraintsuser_indexes查看一下这三个表的结构。,使用索引和约束条件,编写SELECT语句显示多个表中的数据从多个表中选取数据的语法格式如下：SELECTtable1.column_name,table2.column_nameFROMtable1,table2WHERE父表.主键=子表.外键selectxyqkb.xm,xycjb.kcbh,xycjb.cjfromxyqkb,xycjbwherexyqkb.xh=xycjb.xh;,使用索引和约束条件,selectxyqkb.xm,xkb.kcmc,xycjb.cjfromxyqkb,xycjb,xkbwherexyqkb.xh=xycjb.xhandxkb.kcbh=xycjb.kcbh;使用别名，如下所示：selecta.xm,b.kcmc,c.cjfromxyqkba,xkbb,xycjbcwherea.xh=c.xhandb.kcbh=c.kcbh;,使用索引和约束条件,外部连接观察下面两个操作过程的结果有何区别：a）selecta.xm,b.kcbh,b.cjfromxyqkba,xycjbbwherea.xybh=b.xybh;b）selecta.xm,b.kcbh,b.cjfromxyqkba,xycjbbwherea.xybh=b.xybh(+);,使用索引和约束条件,完成以下操作：createtabletest_m(m_nonumber(4)primarykey,namevarchar2(10),agenumber(2);insertintotest_mvalues(1001,a001,34);insertintotest_mvalues(1002,a002,32);insertintotest_mvalues(1003,a003,43);insertintotest_mvalues(1004,a004,36);insertintotest_mvalues(1005,a005,39);,使用索引和约束条件,createtabletest_s(m_nonumber(4),slnumber(4);altertabletest_saddconstrainttest_fkforeignkey(m_no)referencestest;insertintotest_svalues(1001,56);insertintotest_svalues(1001,256);insertintotest_svalues(1002,153);insertintotest_svalues(1003,1256);,使用索引和约束条件,观察下面两种操作结果的差异：a）selecta.name,b.slfromtest_ma,test_sbwherea.m_no=b.m_no；b）selecta.name,b.slfromtest_ma,test_sbwherea.m_no=b.m_no(+)；小结：从第二种情况的运行结果可以看出：即使在子表中没有与父表对应的记录，也要把父表中的记录列出来。但子表中绝不允许出现没有指向父表对应记录的记录。操作（b)即称为外部连接。,使用索引和约束条件,连接运算符1）UNION用来返回前后两个查询的结果，如果有重复则只返回其中一个。连接运算符通常是在两个表的结构类似或一致但表的内容不同的情况下使用。,使用索引和约束条件,用法如下：selecta,bfromtable_aunionselecta,bfromtable_b;orderbya完成以下操作：createtabletst1(namechar(4);createtabletst2(namechar(4);createtabletst3(namenumber(4);createtabletst4(name_tstchar(4);,使用索引和约束条件,insertintotst1values(aaaa);insertintotst1values(bbbb);insertintotst1values(cccc);insertintotst2values(mmmm);insertintotst2values(nnnn);insertintotst2values(cccc);insertintotst3values(111);insertintotst3values(222);insertintotst4values(xxxx);insertintotst4values(yyyy);insertintotst4values(cccc);,使用索引和约束条件,selectnamefromtst1unionselectnamefromtst2;selectnamefromtst1unionselectnamefromtst3;selectnamefromtst1unionselectnamefromtst4;分析上述三个select语句进行连接的情况，在实际应用中有什么需要注意的地方。2）UNIONALL基本上与UNION相同，但不去除重复和行；selectnamefromtst1unionallselectnamefromtst2;selectnamefromtst1unionallselectnamefromtst3;selectnamefromtst1unionallselectnamefromtst4;,使用索引和约束条件,3）INTERSECT用来返回前后两个查询相同的部分：语法结构如下：selecta,bfromtable_aintersectselecta,bfromtable_b;,使用索引和约束条件,完成以下操作：selectnamefromtst1intersectselectnamefromtst2;,使用索引和约束条件,4）MINUS用来返回前面查询减去后面查询的部分，下图示:,使用索引和约束条件,完成以下操作：selectnamefromtst1minusselectnamefromtst2;,使用索引和约束条件,编写子查询子查询完成以下操作：createtabletst5(namechar(4),agenumber(3);insertintotst5values(cccc,51);insertintotst5values(bbbb,46);insertintotst5values(pppp,66);insertintotst5values(qqqq,55);selectnamefromtst5whereage(selectavg(age)fromtst5);,使用索引和约束条件,selectnamefromtst1wherename=(selectnamefromtst5whereage=51);selectname,agefromtst5wherenamein(selectnamefromtst1);,