第04章 数据库对象
(6)修改列：
SQL>ALTER TABLE teacher MODIFY address VARCHAR2(60); (7)修改限制：
SQL>ALTER TABLE teacher MODIFY
CONSTRAINT teacher_sex_ck(sex IN (‘m’,’f’));
说明：如果有记录存在，修改可能引起NOT NULL冲突；数据类型的不兼容，违反主键或外键约束等。当设置主键有冲突时，可以检查是否有相同的记录，并删除。
SQL>SELECT no FROM telephone
GROUP BY no HAVING COUNT(*)>1; SQL>DELETE FROM telephone t1
WHERE EXISTS(
SELECT no FROM telephone t2 WHERE t2.no=t1.no GROUP BY no HAVING COUNT(*)>1) AND ROWID NOT IN (
SELECT MIN(ROWID) FROM telephone t3 WHERE t3.no=t1.no)
或
SQL>DELETE FROM tt t1
WHERE no IN(
SELECT no FROM tt GROUP BY no HAVING COUNT(*)>1)
- 16 -
第04章 数据库对象
AND ROWID NOT IN (
SELECT MIN(ROWID)
FROM tt t2 WHERE t2.no=t1.no)
4.1.6 删除表
删除表的语法为：
DROP TABLE [schema.]table [CASCADE CONSTRAINTS] 删除表，如果有表定义了外键，将限制一起删除。 当删除一个表时，随之删除的内容有： (1)表的索引 (2)指向本表的外部键 (3)表的触发器 (4)表的分区 (5)表的快照
(6)角色和用户的实体权限 (7)本表的所有限制
4.1.7 分析表
ANALYZE TABLE对表进行分析。 (1)验证表的存储结构
SQL>ANNLYZE TABLE teacher VALIDATE STRUCTURE;
可以发现表中是否有坏块，结果记录在invalid_rows中，如果有坏块，要重建表 (2)收集表的统计信息
统计信息包括表的记录数、块使用情况、记录长度等，结果记录在dba|all|user_tables。 SQL>ANNLYZE TABLE teacher COMPUTE STATISTICS; 精确统计表的记录数、块使用情况、记录长度等。 SQL>ANNLYZE TABLE teacher ESTIMATE STATISTICS;
- 17 -
第04章 数据库对象
通过对200条记录的统计，粗略估计统计信息。 (3)查询表的行链接和行迁移
SQL>ANNLYZE TABLE teacher LIST CHAIN_ROWS;
检查结果记录在chained_rows中。可以使用重建表方法消除行链接和行迁移。
4.1.8 表信息
常用表的信息：表名、拥有者、表空间、表的限制、列名、数据类型及宽度、列的限制。 (1)查询表结构 DESC[RIBE] tablename; (2)查询表的信息：
dba|user_tables：表名、存储参数等信息 dba|user_segments：表所在表空间和表的大小 dba_extents：查询表段的区信息 dba_constrants：表的限制信息 dba_tab_columns：表的列信息 dba_cons_columns：限制列的信息
- 18 -
第04章 数据库对象
4.2 索引
索引是一种与表相关的可选对象。在一个表上建立索引，不会对表的操作方法产生任何影响，但是能显著提高对表的查询速度。
4.2.1 索引概念
全表扫描：没有索引的表查询，从头到尾的查询全表所有行，称为全表扫描。全表扫描需要对所有行进行I/O操作，对于记录较多的大表，全表扫描的查询速度较慢，查询效率不高。
索引查找：索引类似于”目录”，目录中记录了大纲和页码；类似地，索引中记录了查询内容和对应行的rowid，有索引查询过程是：
①在索引中查找到符合条件所有行的rowid； ②根据rowid读取相应的行数据。
在目录中查询比在全书中查询快得多；类似地，在索引中查询比全表扫面块得多，所以索引能加快查询速度。
索引副作用：
①索引需要占用空间，是典型的用空间换时间。一般情况下，索引与表分别存放在不同的表空间，以便提高索引和表I/O并行度，减少I/O冲突。
②由于索引的结构依附于表，当表数据发生变化时(增加、删除、修改),系统要同步维护索引，占用系统资源。
索引不是越多越好，要根据具体实际使用表的情况建立有效的索引，删除不必要的索引。 索引的特点：
①数据小：索引只存储索引字段和行ROWID，相对于数据表而言，数据量较小。
②查询快：索引查询速度快，但数据量增大时，查询速度基本不变。但全表扫描随数据的增加，查询速度明显减慢。
③自维护：当数据表的数据改变时，系统自动根据表的数据变化维护索引。
④独立性：索引虽依附于表，但索引存在与否，不影响对表的操作方法，只是提高查询速度。 ⑤多索引：主键只能有一个，但可以根据不同的字段建立多个索引。
- 19 -
第04章 数据库对象
相关问题：
①何时需要索引：索引能提高查询速度，是不是只要有索引，都能提高查询速度呢？ ②需要哪种索引：索引的种类有哪些？哪种索引最有效？
③如何建立索引：不同种类的索引如何建立？如何维护？如何查询索引信息？ 使用索引：
假设建立了teacher表： CREATE TABLE teacher(
id name dno sex
NUMBER(20) VARCHAR2(10), VARCHAR2(10), CHAR(2) DATE
CHECK(sex IN (‘男’,’女’)), NOT NULL,
birthday
CONSTRAINT teacher_id_pk PRIMARY KEY(id)
USING INDEX TABLESPACE indx, CONSTRAINT teacher_name_uq UNIQUE(name)
USING INDEX TABLESPACE indx,
CONSTRAINT teacher_dno_fk FOREIN KEY(dept_id)
REFERENCES dept(dno) );
(1)定义主键后，自动建立了索引，索引字段是id，索引名字为teacher_id_pk。 (2)定义唯一键后，自动建立了索引，索引字段为name，索引名字为teacher_name_uq。 (3)在查询时，只有查询条件字段有索引时，系统才自动使用索引。如： SQL>SELECT * FROM teacher WHERE id=1234;
//使用索引teacher_id_pk
SQL>SELECT * FROM teacher WHERE name=’张大谦’; //使用索引teacher_name_uq SQL>SELECT * FROM teacher WHERE dno=’02’; SQL>SELECT * FROM teacher WHERE sex=’男’;
- 20 -
//不使用索引 //不使用索引