Oracle数据库_7.Oracle索引和视图创建(1)
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
使用索引应该遵循以下一些基本的原则。 1．小表不需要建立索引。 2．对于大表而言，如果经常查询的记录数目少于表中总 记录数目的15%时，可以创建索引。这个比例并不绝对， 它与全表扫描速度成反比。 3．对于大部分列值不重复的列可建立索引。 4．对于基数大的列，适合建立B树索引，而对于基数小的 列适合建立位图索引。 5．对于列中有许多空值，但经常查询所有的非空值记录 的列，应该建立索引。
3 修改索引
当需要修改已创建的索引时，可以使用ALTER INDEX语 句。 用户想要修改自己方案中的索引，需要具有ALTER INDEX系统权限，如果想要修改其他用户方案中的索引， 则需要具有ALTER ANY INDEX系统权限。
一、重命名索引
重命名索引可以使用ALTER INDEX语句。例如，将上节 中为SALES表创建的索引“标题索引”改名为“标题B树索 引”，代码如下。 ALTER INDEX "SCOTT"."标题索引" RENAME TO "标题B 树索引"; 在“SQL Plus Worksheet”中执行以上SQL代码，如图所 示。
第7章Oracle索引和视图创建与管理
(1)索引创建与管理
本章内容
1 2 索引概述 创建索引
3
4
修改索引
查看索引
1 Oracle索引概述
一、索引的概念
索引是建立在表上的可选对象，设计索引的目的是为 了提高查询的速度。但同时索引也会增加系统的负担，进 行影响系统的性能。 目录可以帮助读者快速查找所需要的内容，数据库中的 索引就类似于书的目录。有了索引，DML操作就能快速找 到表中的数据，而不需要扫描整张表。因此，对于包含大 量数据的表来说，设计索引，可以大大提高操作效率。在 书中，目录是内容和页码的清单，而在数据库中，索引是 数据和存储位置的列表。
一、索引的概念
索引一旦建立后，当在表上进行DML操作时，Oracle 会自动维护索引，并决定何时使用索引。 索引的使用对用户是透明的，用户不需要在执行SQL语 句时指定使用哪个索引及如何使用索引，也就是说，无论 表上是否创建有索引，SQL语句的用法不变。用户在进行 操作时，不需要考虑索引的存在，索引只与系统性能相关。
四、管理索引的原则
6．LONG和LONG RAW列不能创建索引。 7．经常进行连接查询的列上应该创建索引。 8．在使用CREATE INDEX语句创建查询时，将最常查询 的列放在其他列前面。 9．维护索引需要开销，特别时对表进行插入和删除操作 时，因此要限制表中索引的数量。对于主要用于读的表， 则索引多就有好处，但是，一个表如果经常被更改，则索 引应少点。 10．在表中插入数据后创建索引。如果在装载数据之前创 建了索引，那么当插入每行时，Oracle都必须更改每个索 引。
二、合并索引
使用如下代码对SALES表的“标题索引”进行合并。 ALTER INDEX "SCOTT"."标题索引" COALESCE; 在“SQL Plus Worksheet”中执行以上SQL代码，结果如 图所示。
三、重建索引
当表中一个已编制索引的值被更新后，旧值会从索引中 删除，新值将被插入索引的另一个部分。旧值释放的空间 将不能被再次使用。随着更新或删除索引值的增多，索引 中不可用空间的量也在增加，这种情况称为索引滞留。由 于滞留索引中的数据和空闲区混在一起，查看索引的效率 便会降低。因此，如果在索引列上频繁进行UPDATE和 DELETE操作，为了提高空间的利用率，应该定期重建索引。 重建索引相当于删除原来的索引，然后再创建一个新的 索引，因此，CREAT INDEX语句中的选项同样适用于重建 索引。
一、索引的概念
索引是建立在表上的可选对象。 索引的关键在于通过一组排序后的索引键来取代默认的 全表扫描检索方式，从而提高检索效率。 索引在逻辑上和物理上都与相关的表的数据无关，当创 建或删除一个索引时，不会影响基本的表、数据库应用或 其他索引，当插入、更改和删除相关的表记录时，Oracle 会自动管理索引，如果删除索引，所有的应用仍然可以继 续工作。因此，在表上创建索引不会对表的使用产生任何 影响，但是，在表中的一列或多列上创建索引可以为数据 的检索提供快捷的存取路径，提高检索速度。
2 创建索引
CREATE INDEX语句的语法如下： CREATE [UNIQUE] | [BITMAP] INDEX index_name ON table_name([column1 [ASC|DESC],column2 [ASC|DESC],…] | [express]) [TABLESPACE tablespace_name] [PCTFREE n1] [STORAGE (INITIAL n2)] [NOLOGGING] [NOLINE] [NOSORT];
二、索引的原理
•在TOPIC列上建立索引，Oracle对全表进行一次搜索，将 每条记录的TOPIC值按升序排列，然后构建索引条目，即 （TOPIC值，ROWID值），存储到索引段中。
•当查询PEN的记录时，如何查找？
三、索引的类型
Oracle支持多种类型的索引，可以按列的多少、索引值 是否唯一和索引数据的组织形式对索引进行分类，以满足 各种表和查询条件的要求。 1．单列索引和复合索引 2．B树索引 3．位图索引 4．函数索引
删除索引使用DROP INDEX语句。要删除用户自己方案 中的索引，需要具有DROP INDEX系统权限，而要删除其 他用户方案中的索引，则需要具有DROP ANY INDEX系 统权限。
4 查看索引信息 查看索引信息有多种方式，最常用的包括使用 OEM查看、使用数据字典视图查看和使用对象报告 查看。
四、管理索引的原则
使用索引的目的是为了提高系统的效率，但同时它也会 增加系统的负担，进行影响系统的性能，因为系统必须在 进行DML操作后维护索引数据。 在新的SQL标准中并不推荐使用索引，而是建议在创建 表的时候用主键替代。因此，为了防止使用索引后反而降 低系统的性能，应该遵循一些基本的原则。
四、管理索引的原则
三、创建函数索引
使用函数索引可以提高在查询条件中使用函数和表达式 的查询语句的执行速度。 Oracle在创建函数索引时，首先对包含索引列的函数值 或表达式进行求值，然后将排序后的结果存储到索引中。 函数索引可以根据基数的大小，选择使用B树索引或位图 索引。
三、创建函数索引
在SALES表中，TOPIC列的值可能是大写形式、小写形 式或首字母大写的方式存储。因此在按TOPIC值查询时可 以采取忽略大小写的方式，其代码如下： SELECT * FROM SALES WHERE UPPER(TOPIC)=’TEE’; 但是，由于不是直接查询TOPIC列，所以，即使在 TOPIC列上创建了索引也无法使用。这样就需要使用函数 索引。
四、删除索引
当以下情况发生时，需要删除索引：
– 不需要该索引时。 – 当索引中包含损坏的数据块，或碎片过多时，应删除该索引， 然后再重建。 – 如果移动了表的数据，将导致索引无效，此时应删除该索引， 然后再重建。 – 当向表中装载大量数据时，Oracle也会向索引增加数据，为 了加快装载速度，可以在装载之前删除索引，在装载完毕后 重新创建索引。
2 创建索引
创建索引使用CREATE INDEX语句。 在用户自己的方案中创建索引，需要CREATE INDEX系 统权限，在其他用户的方案中创建索引则需要CREATE ANY INDEX系统权限。另外，索引需要存储空间，因此， 还必须在保存索引的表空间中有配额，或者具有 UNLIMITED TABLESPACE系统权限。
2 创建索引
可以在一个表上创建多个索引，但这些索引的列的组合必 须不同。如下列的索引是合法的。 CREATE INDEX idx1 ON SALES(ID,TOPIC) CREATE INDEX idx2 ON SALES(TOPIC,ID) 其中，idx1和idx2索引都使用了ID和TOPIC列，但由于顺 序不同，因此是合法的。
2 创建索引
其中： UNIQUE：表示唯一索引，默认情况下，不使用该选项。 BITMAP：表示创建位图索引，默认情况下，不使用该选 项。 PCTFREE：指定索引在数据块中的空闲空间。对于经常插 入数据的表，应该为表中索引指定一个较大的空闲空间。 NOLOGGING：表示在创建索引的过程中不产生任何重做 日志信息。默认情况下，不使用该选项。 ONLINE：表示在创建或重建索引时，允许对表进行DML 操作。默认情况下，不使用该选项。 NOSORT：默认情况下，不使用该选项。则Oracle在创建 索引时对表中记录进行排序。如果表中数据已经是按该索 引顺序排列的，则可以使用该选项。
二、索引的原理
当在一个没有创建索引的表中查询符合某个条件的记录 时，DBMS会顺序地逐条读取每个记录与查询条件进行匹 配，这种方式称为全表扫描(顺序访问)。 全表扫描方式需要遍历整个表，效率很低。
二、索引的原理
•假设SALES表的数据如表所示。
•ROWID伪列表示记录的物理存储位置。SALES表的 TOPIC列没有特定的顺序。 •现在查询TOPIC为PEN 的记录。由于在TOPIC列上没有索 引，该语句会搜索所有的记录。因为即使找到了PEN也不 能保证表中只有一个PEN，必须全部搜索一遍。
在创建索引时，Oracle会将索引的定义信息存放在数据字典 中，可以通过查询数据字典视图DBA_INDEXES、ALL_ INDEXES和USER_ INDEXES来查看。
命令查看： desc user_indexes desc all_indexes select index_name ,index_type,table_name,uniqueness From user_indexs;
二、合并索引
表在使用一段时间后，由于用户不断对其进行更新操作， 而每次对表的更新必然伴随着索引的改变，因此，在索引 中会产生大量的碎片，从而降低索引的使用效率。 有两种方法可以清理碎片：合并索引和重建索引。 合并索引就是将B树叶子节点中的存储碎片合并在一起， 从而提高存取效率，但这种合并并不会改变索引的物理组 织结构。
三、重建索引
重建索引使用ALTER INDEX语句的REBUILD选项。 例如，使用如下代码重建SALES表的“标题索引”。 ALTER INDEX "SCOTT"."标题索引" REBUILD; 在“SQL Plus Worksheet”中执行以上SQL代码，结果如 图所示。
三、重建索引
合并索引和重建索引都能消除索引碎片，但两者在使用 上有明显的区别。 合并索引不能将索引移动到其他表空间，但重建索引可 以； 合并索引代价较低，无需额外存储空间，但重建索引恰 恰相反； 合并索引只能在B树的同一子树中合并，不改变树的高度， 但重建索引重建整个B树，可能会降低树的高度。
查看表的全部索引 在“SQL Plus Worksheet”中执行以下代码可查看SALES 表的全部索引信息。 SELECT index_name,index_type,tablespace_name,uniqueness FROM all_indexes WHERE table_name='SALES';
Байду номын сангаас
一、创建B树索引
B树索引是Oracle默认的索引类型，当在WHERE子句中 经常要引用某些列时，应该在这些列上创建索引。 例如，经常需要在SALES表的TOPIC列上按标题查询， 就可以在TOPIC列上建立B树索引。
二、创建位图索引
位图索引适合于那些基数较少，且经常对该列进行查询、 统计的列。 下面，我们以SALES表的ISLOOK列（只有Y和N这两个 值）为例介绍如何创建位图索引。
3 修改索引
当需要修改已创建的索引时，可以使用ALTER INDEX语 句。 用户想要修改自己方案中的索引，需要具有ALTER INDEX系统权限，如果想要修改其他用户方案中的索引， 则需要具有ALTER ANY INDEX系统权限。
一、重命名索引
重命名索引可以使用ALTER INDEX语句。例如，将上节 中为SALES表创建的索引“标题索引”改名为“标题B树索 引”，代码如下。 ALTER INDEX "SCOTT"."标题索引" RENAME TO "标题B 树索引"; 在“SQL Plus Worksheet”中执行以上SQL代码，如图所 示。
第7章Oracle索引和视图创建与管理
(1)索引创建与管理
本章内容
1 2 索引概述 创建索引
3
4
修改索引
查看索引
1 Oracle索引概述
一、索引的概念
索引是建立在表上的可选对象，设计索引的目的是为 了提高查询的速度。但同时索引也会增加系统的负担，进 行影响系统的性能。 目录可以帮助读者快速查找所需要的内容，数据库中的 索引就类似于书的目录。有了索引，DML操作就能快速找 到表中的数据，而不需要扫描整张表。因此，对于包含大 量数据的表来说，设计索引，可以大大提高操作效率。在 书中，目录是内容和页码的清单，而在数据库中，索引是 数据和存储位置的列表。
一、索引的概念
索引一旦建立后，当在表上进行DML操作时，Oracle 会自动维护索引，并决定何时使用索引。 索引的使用对用户是透明的，用户不需要在执行SQL语 句时指定使用哪个索引及如何使用索引，也就是说，无论 表上是否创建有索引，SQL语句的用法不变。用户在进行 操作时，不需要考虑索引的存在，索引只与系统性能相关。
四、管理索引的原则
6．LONG和LONG RAW列不能创建索引。 7．经常进行连接查询的列上应该创建索引。 8．在使用CREATE INDEX语句创建查询时，将最常查询 的列放在其他列前面。 9．维护索引需要开销，特别时对表进行插入和删除操作 时，因此要限制表中索引的数量。对于主要用于读的表， 则索引多就有好处，但是，一个表如果经常被更改，则索 引应少点。 10．在表中插入数据后创建索引。如果在装载数据之前创 建了索引，那么当插入每行时，Oracle都必须更改每个索 引。
二、合并索引
使用如下代码对SALES表的“标题索引”进行合并。 ALTER INDEX "SCOTT"."标题索引" COALESCE; 在“SQL Plus Worksheet”中执行以上SQL代码，结果如 图所示。
三、重建索引
当表中一个已编制索引的值被更新后，旧值会从索引中 删除，新值将被插入索引的另一个部分。旧值释放的空间 将不能被再次使用。随着更新或删除索引值的增多，索引 中不可用空间的量也在增加，这种情况称为索引滞留。由 于滞留索引中的数据和空闲区混在一起，查看索引的效率 便会降低。因此，如果在索引列上频繁进行UPDATE和 DELETE操作，为了提高空间的利用率，应该定期重建索引。 重建索引相当于删除原来的索引，然后再创建一个新的 索引，因此，CREAT INDEX语句中的选项同样适用于重建 索引。
一、索引的概念
索引是建立在表上的可选对象。 索引的关键在于通过一组排序后的索引键来取代默认的 全表扫描检索方式，从而提高检索效率。 索引在逻辑上和物理上都与相关的表的数据无关，当创 建或删除一个索引时，不会影响基本的表、数据库应用或 其他索引，当插入、更改和删除相关的表记录时，Oracle 会自动管理索引，如果删除索引，所有的应用仍然可以继 续工作。因此，在表上创建索引不会对表的使用产生任何 影响，但是，在表中的一列或多列上创建索引可以为数据 的检索提供快捷的存取路径，提高检索速度。
2 创建索引
CREATE INDEX语句的语法如下： CREATE [UNIQUE] | [BITMAP] INDEX index_name ON table_name([column1 [ASC|DESC],column2 [ASC|DESC],…] | [express]) [TABLESPACE tablespace_name] [PCTFREE n1] [STORAGE (INITIAL n2)] [NOLOGGING] [NOLINE] [NOSORT];
二、索引的原理
•在TOPIC列上建立索引，Oracle对全表进行一次搜索，将 每条记录的TOPIC值按升序排列，然后构建索引条目，即 （TOPIC值，ROWID值），存储到索引段中。
•当查询PEN的记录时，如何查找？
三、索引的类型
Oracle支持多种类型的索引，可以按列的多少、索引值 是否唯一和索引数据的组织形式对索引进行分类，以满足 各种表和查询条件的要求。 1．单列索引和复合索引 2．B树索引 3．位图索引 4．函数索引
删除索引使用DROP INDEX语句。要删除用户自己方案 中的索引，需要具有DROP INDEX系统权限，而要删除其 他用户方案中的索引，则需要具有DROP ANY INDEX系 统权限。
4 查看索引信息 查看索引信息有多种方式，最常用的包括使用 OEM查看、使用数据字典视图查看和使用对象报告 查看。
四、管理索引的原则
使用索引的目的是为了提高系统的效率，但同时它也会 增加系统的负担，进行影响系统的性能，因为系统必须在 进行DML操作后维护索引数据。 在新的SQL标准中并不推荐使用索引，而是建议在创建 表的时候用主键替代。因此，为了防止使用索引后反而降 低系统的性能，应该遵循一些基本的原则。
四、管理索引的原则
三、创建函数索引
使用函数索引可以提高在查询条件中使用函数和表达式 的查询语句的执行速度。 Oracle在创建函数索引时，首先对包含索引列的函数值 或表达式进行求值，然后将排序后的结果存储到索引中。 函数索引可以根据基数的大小，选择使用B树索引或位图 索引。
三、创建函数索引
在SALES表中，TOPIC列的值可能是大写形式、小写形 式或首字母大写的方式存储。因此在按TOPIC值查询时可 以采取忽略大小写的方式，其代码如下： SELECT * FROM SALES WHERE UPPER(TOPIC)=’TEE’; 但是，由于不是直接查询TOPIC列，所以，即使在 TOPIC列上创建了索引也无法使用。这样就需要使用函数 索引。
四、删除索引
当以下情况发生时，需要删除索引：
– 不需要该索引时。 – 当索引中包含损坏的数据块，或碎片过多时，应删除该索引， 然后再重建。 – 如果移动了表的数据，将导致索引无效，此时应删除该索引， 然后再重建。 – 当向表中装载大量数据时，Oracle也会向索引增加数据，为 了加快装载速度，可以在装载之前删除索引，在装载完毕后 重新创建索引。
2 创建索引
创建索引使用CREATE INDEX语句。 在用户自己的方案中创建索引，需要CREATE INDEX系 统权限，在其他用户的方案中创建索引则需要CREATE ANY INDEX系统权限。另外，索引需要存储空间，因此， 还必须在保存索引的表空间中有配额，或者具有 UNLIMITED TABLESPACE系统权限。
2 创建索引
可以在一个表上创建多个索引，但这些索引的列的组合必 须不同。如下列的索引是合法的。 CREATE INDEX idx1 ON SALES(ID,TOPIC) CREATE INDEX idx2 ON SALES(TOPIC,ID) 其中，idx1和idx2索引都使用了ID和TOPIC列，但由于顺 序不同，因此是合法的。
2 创建索引
其中： UNIQUE：表示唯一索引，默认情况下，不使用该选项。 BITMAP：表示创建位图索引，默认情况下，不使用该选 项。 PCTFREE：指定索引在数据块中的空闲空间。对于经常插 入数据的表，应该为表中索引指定一个较大的空闲空间。 NOLOGGING：表示在创建索引的过程中不产生任何重做 日志信息。默认情况下，不使用该选项。 ONLINE：表示在创建或重建索引时，允许对表进行DML 操作。默认情况下，不使用该选项。 NOSORT：默认情况下，不使用该选项。则Oracle在创建 索引时对表中记录进行排序。如果表中数据已经是按该索 引顺序排列的，则可以使用该选项。
二、索引的原理
当在一个没有创建索引的表中查询符合某个条件的记录 时，DBMS会顺序地逐条读取每个记录与查询条件进行匹 配，这种方式称为全表扫描(顺序访问)。 全表扫描方式需要遍历整个表，效率很低。
二、索引的原理
•假设SALES表的数据如表所示。
•ROWID伪列表示记录的物理存储位置。SALES表的 TOPIC列没有特定的顺序。 •现在查询TOPIC为PEN 的记录。由于在TOPIC列上没有索 引，该语句会搜索所有的记录。因为即使找到了PEN也不 能保证表中只有一个PEN，必须全部搜索一遍。
在创建索引时，Oracle会将索引的定义信息存放在数据字典 中，可以通过查询数据字典视图DBA_INDEXES、ALL_ INDEXES和USER_ INDEXES来查看。
命令查看： desc user_indexes desc all_indexes select index_name ,index_type,table_name,uniqueness From user_indexs;
二、合并索引
表在使用一段时间后，由于用户不断对其进行更新操作， 而每次对表的更新必然伴随着索引的改变，因此，在索引 中会产生大量的碎片，从而降低索引的使用效率。 有两种方法可以清理碎片：合并索引和重建索引。 合并索引就是将B树叶子节点中的存储碎片合并在一起， 从而提高存取效率，但这种合并并不会改变索引的物理组 织结构。
三、重建索引
重建索引使用ALTER INDEX语句的REBUILD选项。 例如，使用如下代码重建SALES表的“标题索引”。 ALTER INDEX "SCOTT"."标题索引" REBUILD; 在“SQL Plus Worksheet”中执行以上SQL代码，结果如 图所示。
三、重建索引
合并索引和重建索引都能消除索引碎片，但两者在使用 上有明显的区别。 合并索引不能将索引移动到其他表空间，但重建索引可 以； 合并索引代价较低，无需额外存储空间，但重建索引恰 恰相反； 合并索引只能在B树的同一子树中合并，不改变树的高度， 但重建索引重建整个B树，可能会降低树的高度。
查看表的全部索引 在“SQL Plus Worksheet”中执行以下代码可查看SALES 表的全部索引信息。 SELECT index_name,index_type,tablespace_name,uniqueness FROM all_indexes WHERE table_name='SALES';
Байду номын сангаас
一、创建B树索引
B树索引是Oracle默认的索引类型，当在WHERE子句中 经常要引用某些列时，应该在这些列上创建索引。 例如，经常需要在SALES表的TOPIC列上按标题查询， 就可以在TOPIC列上建立B树索引。
二、创建位图索引
位图索引适合于那些基数较少，且经常对该列进行查询、 统计的列。 下面，我们以SALES表的ISLOOK列（只有Y和N这两个 值）为例介绍如何创建位图索引。