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

使用索引和约束条件,人民银行郑州培训学院 信息部 潘汉杰 2005年10月26-27日,开始新内容之前先完成以下建表操作：,表名：选课表: xkb 列名：课程号： kch char(4) 课程名称：kcmc varchar2(20) 学分： xf number(2) 建表及数据录入请自已使用create和insert命令进行。,使用索引和约束条件,索引的概念 数据库的索引 可以将索引概念应用到数据库表上。当一个表含有大量的记录时，Oracle查找该表中的特写记录要花很长的时间就像花很长时间翻看全书来查找某个主题一样。Oracle有一个易于使用的功能，即可以建立一个次隐藏表，该表包含主表中的一个或多个重要的列，以及在主表中的指相应行的指针。在这里，与书的页码相对应，该隐藏的次表（即索引表）中的指针就是行号。通过访问索引，Oracle可以准确地知道要,使用索引和约束条件,查找的特定数据在哪一行上。由于索引比引用它的表要小得多，因此用索引查找表中的数据比不用索引的表明赤地快。在一个拥有百万条记录的表中选择与某值相匹配的记录花了18.9秒，当建立该表的索引后，同样的查询只需要0.6秒就能完成。在一个大表中增加索引能使查询速度快几十倍。看图1中的一个索引表:,图1 带有一个索引表,使用索引和约束条件,一旦建立了表的索引，Oracle自动使该表与表保持同步。对该表的任何INSERT、UPDATE或DELETE操作也自动修改索引，并且，如果索引中含有SELECT语句所需的列，则表的任何SELECT操作都将自动经过索引。增加或删除索引不会影响表的操作以前使用该表的任何程序现在仍将可以运行，但是操作速度可能变慢。如果删除表，则相关的索引也将自动删除，因为没相关表的索引是没有意义的。 删除索引的语法如下： DROP INDEX index_name,使用索引和约束条件,索引何时有用 对于必须读取表中内容的命令，索引改善了它们的响应时间。这意味着SELECT、UPDATE和DELETE命令都能够更快地进行运行，如果该表有对应于这些命令的列的索引。 但是增加表的索引并不能提高INSERT命令的输入数据的速度，相反还要降低运行速度。因为索引本身实际上是一个表，因此当对表添加一个记录时，Oracle必须做两次插入。这样，组表增加一个索引将导致插入操作要两倍多的时间（两倍的时间用于两次插入，另外还需要一点时间处理二表之间的同步）。增加两个索引将使用插入操作花三倍时间，三个索引使插入操作花四倍的时间，等等。因此索引的使用要权衡利弊。它们会使数据输入花费更长的时间，但却使读取数据的速度提高。所以，需要快速进行数据输入的应用程序最好不,使用索引和约束条件,要增加表的索引。例如，超市中的销售系统，应使收很机尽可能快地围绕销售事务进行工作（即直接插入到数据库中）。这时，若给存储事务的表增加一个索引将是错误的，因为它会使插入变变慢。另一方面，在同一时刻可能会有经营管理人员想要执行查询来分析事务。 记住：表越大，从表的索引中获得的好处就越多。表2是在一个百万条记录的表上使用和不使用索引时，执行各种DML操作所花费的时间：,使用索引和约束条件,如何建立索引 建立索引很简单，命令的语法如下： CREATE INDEX index_name ON table_name(column_name); 如果想要索引包含一个以上的表列，其语法如下： CREATE INDEX index_name ON table_name( first_column_name, second_column_name );,使用索引和约束条件,用上面建立索引的命令建立索引： create index code_index on person(person_code); create index person_name_index on person(last_name,first_name); 请对建立的其它表建立索引进行测试。 说明：在标准Oracle索引中所能包含的最大的列数为32。,使用索引和约束条件,索引类型 1、B*树索引 Oracle组织记录的缺省索引类型称为B*树。图3示出B*树索引是如何组织记录的。,图3 B*-树索引如何组织记录,使用索引和约束条件,当建立一个B*-树索引时，Oracle分析被索引的列的值，确定如何将表分成记录数量相等的叶块，然后建立枝块层，以便使叶块层的记录用尽可能少的步骤确定。 上图的例子中，枝块是按字母顺序均匀分割的。在实际中，分枝点由记录的值确定。例如，如果一个表含有的以“A”开头的记录比其他字母的多得多，那么可能一个完整的枝块都是“A”的，下一个枝块则从“B”开始。 B*-树索引的优点是它可使Oracle快速地确定不需要读取的记录。通过使必须读取的数据数量最小化，Oracle将更快地返回结果。,使用索引和约束条件,由于B*-树索引通过根据内容将数据划分成集合和子集合来工作，因此这种类型的索引适合于被索引的列中包含大量不同值的情况，则位图索引更适合。 2、位图索引 B*-树索引结构最适合于检索含有大量不同值的列，那么，显而易见，另一种索引结构对于只包含很少的不同值的列更有效。 例如，性别的列将只含有三种可能值，即“M”（男）、“F”（女）或“U”（未知）。将数量很少的不同值放入B*-树索引结构中没有意义，因为B*-树的“逐步划分成子组”的方法对只有少量的不同值的情形几乎无用。在这种情况下，使用位图索引更有意义。 图4给出了对如何设计位图索引的一个简单描述。,使用索引和约束条件,图4 位图索引如何组织记录,使用索引和约束条件,在WHERE子句为一个低基数列的SELECT查询中，预先建立该列的位图索引可大大减少检索花费时间。该速度的提高是两方面的结果：1）位图索引相当地小；2）存储在位图索引中的“1”或“0”值可以被计算机很快地计算。 建立位图索引的语法如下： CREATE BITMAP INDEX index_name ON table_name(column_name); 保持数据的完整性：约束条件 什么是约束条件 约束条件（constraint)是定义一个或多个条件的一种方法，用户的输入在被Oracle接收进表之前必须满足这些条件。,使用索引和约束条件,怎样建立约束条件 我们将学习如何建立三种不同的约束条件。当一起使用时，这些约束条件对于确保表不的数据是“干净”的将大有帮助。 1）NOT NULL 非空，一旦使用该项约束条件，那么被约束的列不能为空值。 在建立表的时候，使用“非空”约束。例如： CREATE TABLE test_constraint( product_name varchar2(20) NOT NULL, product_price NUMBER(4,2) NOT NULL, purchase_date DATE );,使用索引和约束条件,如果表已经建立，则可以通过更改已有的表，使得当插入或更新记录时，列不再接收空值。将已有列修改为NOT NULL状态的语法如下： ALTER TABLE table_name MODIFY (column_name NOT NULL); 例如：ALTER TABLE person MODIFY (last_name NOT NULL); ALTER TABLE person MODIFY (last_name NOT NULL); 通过输入代码可以测试约束条件。,使用索引和约束条件,2）UNIQUE 唯一值约束条件。 添加唯一值约束条件的语法结构如下： ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column_name); 例如：ALTER TABLE person ADD CONSTRAINT unique_test UNIQUE( first_name , last_name ); 可通过使用插入记录的语句进行测行。,使用索引和约束条件,3）Check 检查（check）约束条件允许定义为了输入的数据被Oracle数据库接收，数据必须满足的条件。可以给表的每个列定义一个检查约束条件。 例如，可以要求价格列的数据必须为正值，日期列的值在某个范围内。检查约束条件是保证数据库为干净数据的最有力的工具之一。 在一个已有表中建立某列检查约束条件的语法如下： ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition_to_satisfy) ;,使用索引和约束条件,完成以下操作认真观察运行结果： 例1： create table test(age number(2); alter table test add( constraint test_check check( age is not null and age10) ); insert into test values(12); insert into test values(10); insert into test values(9); commit;,使用索引和约束条件,例2： alter table purchase add( constraint check_purchase_date check( purchase_date is not null and to_char(purchase_date,YYYYMMDD)20020630 ) ); insert into purchase valuse(Small Widget,10,28-5月-02,GA); insert into purchase valuse(Small Widget,9,28-6月-02,GA); insert into purchase valuse(Small Widget,10,28-7月-02,GA);,使用索引和约束条件,启用和禁止已有的约束条件 约束条件可以禁止使用也可以重新加载，而不用永久地删除它。语法如下： ALTER TABLE table_name DISABLE CONSTRAINT constraint_name; 重新启用约束条件的语法为： ALTER TABLE table_name ENABLE CONSTRAINT constraint_name; 例如：alter table test disable constraint test_check; insert into test values(7); insert into test values(9); alter table test enable constraint test_check;（为什么出错？如何插入下列数据？） insert into test values(12); insert into test values(13);,使用索引和约束条件,更改和删除已有的约束条件 生活不可预测，需求在不断地改变，某些时候需要修改或删除某个表的已有约束条件。 修改表的约束条件的语法如下： ALTER TABLE table_name MODIFY (column_name NULL); 例如：alter table test modify (test_check null); alter table test modify (test_check not null); 如果想要删除整个约束条件，可使用以下语法完成： ALTER TABLE table_name DROP CONSTRAINT constraint_name; 注意：删除约束条件是一个永久性的操作。如果你认为今后还会需要该约束条件可考虑禁用它来代替删除它。,使用索引和约束条件,例如：alter table test drop constraint test_check; 表之间的关系 考察下面三个表之间的联系,使用索引和约束条件,使用约束条件加强表之间的联系 为了使两个表之间存在联系，必须使两件事正确： 1）父表必须有一列（或一组列）唯一标识它所包含的每个记录。（称为主码或主键） 2）子表必须有一个相同的列（或一组列）包含唯一标识父记录的值。（你为外码或外键）,使用索引和约束条件,（1）建立主键 如果表已存在用如下语句： ALTER TABLE table_name ADD PRIMARY KEY(column_name 1,column_name 2,) ; 例如： create table st(st_no number(4),name char(10); alter table st add primary key(st_no); 当建立主键时，Oracle自动利用该主键在该列（或几列）上建立一个索引。,使用索引和约束条件,完成以下操作建立学员情况表(xyqkb) ： create table xyqkb(xh number(4),xm char(10),dh char(15); insert into xyqkb(2001,张三,037165648733); insert into xyqkb(2002,李四,037165648734); insert into xyqkb(2003,王五,037165648735); insert into xyqkb(2004,赵六,037165648736); insert into xyqkb(2005,吴七,037165648737); commit; alter table xyqkb add primary key(xh);,使用索引和约束条件,主键也可以在建表时直接建立：如 create table xyqkb( xh number(4) primary key, xm char(10), dh char(15) );,使用索引和约束条件,（2）建立一个外键约束条件 主键和外键是建立表之间联系的物理组成部分。但是，由它们自己并不能保证联系的完整性也就是说，即使主键和外键的列都有完全相同的名字和数据类型，但是，Oracle并不认为它们之间有关系，除非你加以确定。即必须在子表中定义一个约束条件，使得它在接收数据进入其外键之前先检查父表的主键。如果没有这样的约束条件，用户可能会将父表中实际不存在的值输入给子表的外键。,使用索引和约束条件,建立外键约束条件的语法如下： ALTER TABLE child_table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name(s)_in_child_table) REFERENCES parent_table_name ; 完成下面的操作建立一个学员成绩表(xycjb)： create table xycjb(xh number(4),kcbh number(4),cj number(3)； alter table xycjb add constraint xh_fk froeign key(xh) references xyqkb;,使用索引和约束条件,观察并分析出错情况： insert into xycjb values(2000,1001,90); insert into xycjb values(2001,1003,85); insert into xycjb values(2002,1002,88); insert into xycjb values(2001,1001,84); insert into xycjb values(2003,1002,80); insert into xycjb values(2002,1002,67); insert into xycjb values(2003,1003,87); insert into xycjb values(2004,1004,89); insert into xycjb values(2004,1001,83); insert into xycjb values(2005,1002,82); insert into xycjb values(2005,1004,88);,使用索引和约束条件,完成下面的操作建立一个选课表(xkb)： cteate table xkb ( kcbh number(4) primary key, kcmc varchar2(20), xf number(3) ); 试试能不能完成以下操作，为什么？ alter table xycjb add constraint kcbh_fk foreign key (kcbh) references xkb;,使用索引和约束条件,向选课表（XKB)中插入记录： insert into xkb values(1001,高等数学,5); insert into xkb values(1002,线性代数,4); insert into xkb values(1003,普通物理,5); insert into xkb values(1004,程序设计,6); commit; alter table xycjb add constraint kcbh_fk foreign key (kcbh) references xkb;,使用索引和约束条件,由系统自动维护，与用户有关的几个表： user_tables user_constraints user_indexes 查看一下这三个表的结构。,使用索引和约束条件,编写SELECT语句显示多个表中的数据 从多个表中选取数据的语法格式如下： SELECT table1.column_name,table2.column_name FROM table1,table2 WHERE 父表.主键 = 子表.外键 select xyqkb.xm,xycjb.kcbh,xycjb.cj from xyqkb,xycjb where xyqkb.xh = xycjb.xh;,使用索引和约束条件,select xyqkb.xm,xkb.kcmc,xycjb.cj from xyqkb,xycjb,xkb where xyqkb.xh = xycjb.xh and xkb.kcbh=xycjb.kcbh ; 使用别名，如下所示： select a.xm , b.kcmc , c.cj from xyqkb a , xkb b , xycjb c where a.xh = c.xh and b.kcbh=c.kcbh ;,使用索引和约束条件,外部连接 观察下面两个操作过程的结果有何区别： a）select a.xm , b.kcbh , b.cj from xyqkb a, xycjb b where a.xybh = b.xybh ; b） select a.xm , b.kcbh , b.cj from xyqkb a, xycjb b where a.xybh = b.xybh (+) ;,使用索引和约束条件,完成以下操作： create table test_m( m_no number(4) primary key, name varchar2(10), age number(2); insert into test_m values(1001,a001,34); insert into test_m values(1002,a002,32); insert into test_m values(1003,a003,43); insert into test_m values(1004,a004,36); insert into test_m values(1005,a005,39);,使用索引和约束条件,create table test_s( m_no number(4), sl number(4); alter table test_s add constraint test_fk foreign key(m_no) references test; insert into test_s values(1001,56); insert into test_s values(1001,256); insert into test_s values(1002,153); insert into test_s values(1003,1256);,使用索引和约束条件,观察下面两种操作结果的差异： a） select ,b.sl from test_m a, test_s b where a.m_no = b.m_no； b）select ,b.sl from test_m a, test_s b where a.m_no = b.m_no (+) ； 小结：从第二种情况的运行结果可以看出：即使在子表中没有与父表对应的记录，也要把父表中的记录列出来。但子表中绝不允许出现没有指向父表对应记录的记录。 操作（b) 即称为外部连接。,使用索引和约束条件,连接运算符 1）UNION 用来返回前后两个查询的结果，如果有重复则只返回其中一个。连接运算符通常是在两个表的结构类似或一致但表的内容不同的情况下使用。,使用索引和约束条件,用法如下： select a,b from table_a union select a,b from table_b; order by a 完成以下操作： create table tst1(name char(4); create table tst2(name char(4); create table tst3(name number(4); create table tst4(name_tst char(4);,使用索引和约束条件,insert into tst1 values (aaaa); insert into tst1 values(bbbb); insert into tst1 values(cccc); insert into tst2 values(mmmm); insert into tst2 values(nnnn); insert into tst2 values(cccc); insert into tst3 values(111); insert into tst3 values(222); insert into tst4 values(xxxx); insert into tst4 values(yyyy); insert into tst4 values(cccc);,使用索引和约束条件,select name from tst1 union select name from tst2; select