Oracle第7章_管理其他对象(索引和视图)汇总

1、第7章oracle索引和视图的创建和管理（D理解索引的概念、作用及其类型；（2）掌握各种索引的适用场合及其创建、修改过程;（3）掌握视图的常用操作；（4）了解外部表的概念及操作；（5）理解临时表的概念及其特点；（6）了解序列的常用操作。7. 1索引7. 2视图7. 3外部表 7. 4临时表 7. 5序列（D索引的作用（2）各类型索引的特点及其适用场合（2）视图的创建及管理（3） Oracle中临时表与其他关系数据库的不同点索引是一种可选的数据结构，在一个表上是否建立索引，这不 会对表的使用方法产生任何影响。但是如果在表中的某些字段上 建立了索引，这能够显著地提高对该表的查询速度。并且可以在 很

2、大程度上减少杳询时的硬盘I/O操作。如果一个表中包含有很多条记录，当对表执行查询时，必须将所 有的记录一一取出，以便将每一条记录与查询条件进行比较，然后 再返回满足条件的记录。这样进行操作的时间开销和I/O开销都是 十分巨大的。这时就可以考虑通过建立索引来减少开销。索引与表一样，不仅需要在数据字典中保存索引的定义，还需要 在表空间中为它分配实际的存储空间。创建索引时，Oracle会自动 在用户的默认表空间或指定的表空间中创建一个索引段，为索引数 据提供存储空间。为某个表创建的索引不必和该表保存在同一个表空间中。将索引 和对应的表分别存放在不同硬盘的不同的表空间中，反而能够提高 查询的速度（并行

3、访问）。在创建索引时，Oracle首先对将要建立索引的字段（索引字段） 进行排序，然后将排序后的字段值和对应记录的ROWID存储在索引 段中（索引条目）。索引与键两个术语在许多地方川以互换，但两者在本质上有着明 显的区别：索引时存储在数据库中的一种实体结构，可以通过SQL语句來操作；而键只是一个逻辑上的概念，在Oracle中是与完整性 约束相对应的。按照所有数据的存储方式可以将索引分为B树索引、方向键索引 、位图索引和基于函数的索引。按照索引列的个数又可以分为单列 索引和复合索引。按照索引列的唯一性，又可以分为唯一索引和非 唯一索引。SQL create index enp_jiane-_jo

4、b_index on emp;素引己创建。SQL create index enp_Job_nane_index on emp；I索引己创建。7.1.2 创建索引建立索引是使用CREATE INDEX语句完成的。一般情况下，建立索 引是由表的所有者完成的， 如果要以其他用户身份建立索引， 则要 求用户必须具有CREATE ANYINDEX系统权限或者在相应表的INDEX对象权限。(1)B树索引B树索引是Oracle中默认的、最常用的索引类型。B树索引是以B树结构组织并存放索引数据的。默认情况下，B树索引中的数据是 以升序方式排列的 C如果表包含的数据非常多，并且经常在WHERE子句中引用某列或

5、 某儿个列，则应该基于该列或该儿个列建立B树索引。B树索引由根块、分支块和叶块3部分组成，其中主要数据都集中在叶了结点。SQL create index emp_enamei nd ex nn emp2pct*ee 303tablespace u&ei*&；索引己创建。1(X)1 1012(2)位图索引索引的作用简单地说就是能够通过给定的索引列值，快速地找到 对应的记录。在B树索引中，通过在索引中保存排过序的索引列的 值，以及记录的物理地址ROW ID來丈现快速査找但是对干一些特 殊的表，B树索引的效率可能会很低。B树索引适用于重复值很少的列上，而位图索引适用于重复值很 多、不

6、同值相对固定的列上。位图索引以位图值标识索引行数据。创建位图素引时，Oracle基于每个不同值建立一个位图。当行数据匹配位图值时，相应位置 为1,否则为0。SQL create bitmap index enp_job_enp on enp 2 tablespace users；cmpno65322 6534565352位图男位图女T1T00索引己创建。(3)反向键索引在Oracle中，系统会自动为表的主键列建立索引，这个默认的索 引是普通的B树索引。通常，用户会希望表的主键是一个自动增长 的序列编号，这样的列就是所谓的单调递增序列编号列。当在这种 顺序递增的列上建立普通的B树索引时，如果表的

7、数据量非常庞大 ，将导致索引数据分布不均。反向键索引是一种特殊类型的B树索引，在顺序递增列上建立索 引时非常有用。反向键索引在存储结构上与常规的B树索引相同。然而，如果用 户使用序列在标准输入新记录，则反向键索引首先反向每个列键值 的字节，然后在反向后的新数据上进行索引。SQL create index pk_ewp on eRpCenpno3 tublespacc uucrs；create Index pk_enp on enp h i行出现错务ORA 00955=名称己由现有对讓使托SQL alter -index pk_Rnp rebuild mu。,/。；素引己更改。（4）基于函数的索

8、引用户在使用Oracle数据库时，最常遇到问题Z就是它对字符大 小写敏感。SQLselect empnoenamejobsal from emp 2 wheie enane=JsnithJ；未选定行KQL selectjnhFsal rom nnp2 where upper =upper；存在问题：索引不起作用，需要全表搜索。EMPNO ENAMEJOB7J6VSMITHCLEKKSAL8kM基于函数的索引是常规的B树索引，只是它存放的数据是由表中 的数据应用函数后所得到的，而不是表中数据本身。SQLcreate index idx_enane 2 on enploweF ；索引己创建。BQL

9、 select al from enp 2 wliere luwer -fmart in *；DENA MEJOBSAL匚_ _ _LMARTIN SALESMAN12507.1.3 修改索引修改索引是使用ALTER INDEX命令完成的。一般情况下，修改索 引是由索引的所有者完的，如果要以其他用户身份修改索引，则要 求该用户必须具有ALTER ANYINDEX系统权限或在相应表上的INDEX对象权限。在为表建立索引后，随着对表不断进行更新、插入和删除操作， 索引中将会产生越来越多的存储碎片，这对索引的工作效率会产生 负面影响。这时用户可以采取两种方式来清除碎片一一合并索引

10、或 重建索引。(1)合并索引合并索引只是将B树中叶子节点的存储碎片合并在一起，并不会 改变索引的物理组织结构。SQL alter index erap_enane_index 2 coalesce deallocate unused；素引己更改。(2)重建索引重建索引使用ALTER INDEX-REBUILD语句。 重建操作不仅可以消 除存储碎片，还可以改变索引的存储参数没置及存储表空间。创建 索引实际上是在指定的表空间屮重新建立一个索引，然后删除原來 的索引。SQL alter index enp_ename_index rehui丄d；索引己更改。7.1.4 显示索引信息通过查询相应的数据

11、字典视图，用户可以了解索引的各种信息。(1)显示表的所有索引DBA_INDEXES：数据库的所有索引ALL_INDEXES：当然用户可访问的所有索引USERJNDEXES：当前用户的索引信息SQL connect s5mtera/12346己连接SQL select ndex_nane , index_type .uniquenessUNIQUENES2from dba_indexes3where owner=,SCOTTJandINDEX.HAMEtable jnane=JEMP；INDEX_T?PEFMP-NAMF_JOR_IHDEXNORMALNONIINTQIIFEHP_JOB_NnM

12、E_IHDEXNORnHLNONUNIQUEEMP_ENAME_INDEXNORMALNONUNIQUEEMP一JOB EMPBITMAPHONUNIQUEIDX.ENAMEFUNCTION-BASED NORMALNONIIHIQIIEPKFMPHORHnrBEUUHTQIIF己选择6行。(2)显示索引列DBA_IND_COLUMNS：数据库所有索引的表列信息ALL_IND_COLUMNS：当然用户可访问的所有索引的表列信息USER_IND_COLUMNS：当前用户索引的表列信息SQL coliuin column_name format a20SQL se lect column_name

13、,colunn_po3ition，co lumn_lens( (th2fron user_ind_colunns3index_iianiePK_EMP:COLUMNNAMECOLUMN.POSITION COUJMN_LENGTHEMPNO122(3)显示索引段位置及其大小DBA_SEGMENTS：数据库所有段的详细信息USER_SEGMENTS：当前用户段的详细信息SQL select tablcspaccunamc.2from user_segnents3whe*e segnent_nane= ,PKTABLESPACE_NAMEsegment type.bytc3EMP:SEGMENT.

14、TYPEBYTESUSERSINDEX65536（4）显示函数索引DBA_IND_EXPRESSIONS：数据库所有函数索引所对应的函数 或表达式USER_ I ND_EXPRESS IONS；当前用户函数索引所对应的函数 或表达式SQL select colunnexpresslon2ron usei*_ind_exprescions3where index name=*IDX ENAMEJ:COLUMNJEXPRESSI ON|LOWFR7.1.5 删除索引删除索引是使用DROP INDEX语句完成的。一般情况下，删除索引 是由索引所有者完成的，如果以其他用户身份删除索引，则要求该 用户必须具有DROP ANYINDEX系统权限或在相应表上的INDEX对象 权限。通常在如下情况下需要删除某个索引:该索引不再需要时，应该删除该索引，以释放其所占用的空间。如果移动了表中的数据，导致索引中包含过多的存储碎片，此时 需要删除并重建索引。通过一段