T6_索引、视图、序列

第六课索引、视图与序列作者：吴进学习目标索引的类型和作用1建立和删除索引2视图的建立、更新和删除3序列41索引概述索引的目的是提高存储数据页面的速度服务器扫描索引以获得数据存储单元的地址索引的优点为：提高查询的执行速度、实施数据的唯一性、加速了表之间的连接索引的缺点为：创建索引需要花费较多的时间、每个创建的索引连同原先的数据源都需要空间来存储数据、每次修改数据时索引都要更新2索引类型按照索引数据的存储方式可以分为：B*树索引位图索引按照索引个数分为：单列索引复合索引索引列值唯一性可分为：唯一索引非唯一索引（默认）2索引的例子--建立一个测试表createtabletab_idx_testasselect*fromall_objects;或者可以写为createtabletab_idx_testasselect*fromall_objectswhere1=2;insertintotab_idx_testselect*fromall_objects;--连续执行多次,达到20万条左右insertintotab_idx_testselect*fromall_objects;--检查条数selectcount(1)fromtab_idx_test;--测试时间selectowner,object_namefromall_objectswhereobject_name='DBA_INDEXES';--增加索引createindexidx_tab_idx_testontab_idx_test(object_name);--再次测试时间selectowner,object_namefromtab_idx_testwhereobject_name='DBA_INDEXES';2.1B*树索引（默认）以B*树结构（算法）来组织并存放索引数据B*树是一颗二叉树默认以升序排列由根块、分支块、叶块（ROWID)组成其中，索引数据存储在叶块2.1B*树索引例子createindexidx_emp_enameonemp(ename);selectrowid,empno,enamefromemp;2.1B*树索引语法Create[unique]indexindex_nameOntable_name(column_name,column_name…)[tablespacetablespace_name];其中：1、unique：此关键字表示创建唯一性索引，即索引的值不允许重复。2、已有索引不能更新，只能先删除，再新建。3、Tablespace_name:位于的表空间的名称，如果没有指定表空间，就是当前用户默认的表空间。2.1B*树索引创建规则创建规则及特点：应建立在重复值很少的列上应建立经常在where子句中引用的某列或某几列上适用于相等查询和范围查询例：我们经常会执行语句：select*fromempwhereename=‘SCOTT’那么我们可以在ename列上创建B树索引，语句如下：createindexind_enameonemp(ename)tablespaceusers2.2位图索引以位置来表示索引数据相比B树索引，位图索引可以节省大量磁盘空间基于每个不同值建立一个位图语法：CREATEBITMAPINDEXidx_nameONtab_name(col_name)TABLESPACEspace_name2.2位图索引规则和特点建立在重复值很多的列建立在执行数据统计、数据汇总的条件查询列上例：经常执行语句：Selectcount(1)fromtab_namewheresex=‘F’那么可以在性别列上建立位图索引：Createbitmapindexidx_sexOntab_name(sex);2.2位图索引例子CnameMarital_statusRegionSexRowidCarterSignleEast1AAAMZJAAEAAAAIVAAAGuibaultMarriedCentral0AAAMZJAAEAAAAIVAABHuberMarriedWest0AAAMZJAAEAAAAIVAACJohnsonDivorcedWest1AAAMZJAAEAAAAIVAADSchwenSignleCentral0AAAMZJAAEAAAAIVAAEVitetoMarriedCentral0AAAMZJAAEAAAAIVAAF2.2位图索引例子SingleMarriedDivorcedrowid100AAAMZJAAEAAAAIVAAA010AAAMZJAAEAAAAIVAAB010AAAMZJAAEAAAAIVAAC001AAAMZJAAEAAAAIVAAD100AAAMZJAAEAAAAIVAAE010AAAMZJAAEAAAAIVAAFMarital_status列，分布的数据SignleMarriedDivorced2.2位图索引例子EastCentralWestrowid100AAAMZJAAEAAAAIVAAA010AAAMZJAAEAAAAIVAAB001AAAMZJAAEAAAAIVAAC001AAAMZJAAEAAAAIVAAD010AAAMZJAAEAAAAIVAAE010AAAMZJAAEAAAAIVAAFRegion列，分布的数据EastCentralWest2.2位图索引例子011111011001married011001查询条件Selectcount(1)fromcustomerwheremarital_status=‘Married’andregionin(‘central’,’west’);结果位图011001West001100Central010011andor=and=2.3重建索引•如果在索引列上频繁执行UPDATE或INSERT操作，则应定期重建索引，提高空间利用率•语法：•ALTERINDEXidx_nameREBUILD2.4删除索引•索引不再需要时•移动表数据后，索引会无效，需删除或重建•DROPINDEXidx_name2.5查看索引信息•显示表的所有索引：•显示EMP表的索引信息•SELECTindex_name,index_type,uniquenessfromuser_indexeswheretable_name='EMP';3视图视图是一个虚拟的表视图提供一种安全机制，保证只能检索和修改他们看得到的数据复杂查询的使用也可以通过视图来简化3视图的优点为用户提供相关数据，提供一种安全机制隐藏数据复杂性从异构源组织数据，比如远程服务器和不是同一个数据库的表减少对象的大小3.1创建视图createviewv_empasselectemp.*,dept.dnamefromemp,deptwhereemp.deptno=dept.deptno;Select*fromv_emp;3.2更新视图记录Createorreplaceviewview_nameassubquery;Renameold_view_nametonew_view_name3.3删除视图Dropviewview_name;3.4只读视图增加withreadonly，则视图只能查询，不能修改。例如：createviewaaasselect*fromempwithreadonly;insertintoaa(empno,ename)values(8888,'abc');4序列如何生成一个自动增长的列?答案：Oracle不提供一种类似Sqlserver的identity的列，而是提供了一个对象，序列。4.1序列语法完整的语法如下：Createsequenceseq_name[incrementbyx][startwithx][maxvaluex|nomaxvalue][minvaluex|nominvalue][cycle|nocycle][cache|nocache]4.2序列语法例子CREATESEQUENCESYS.AANOCYCLENOORDERCACHE20NOMAXVALUEMINVALUE1INCREMENTBY1STARTWITH1INCREMENTBY1表示按1递增STARTWITH1表示从1开始NOMAXVALUE表示没有上限值MINVALUE1表示最小值为1NOCYCLE表示序列达到最大值后，不按从头开始循环的方式再产生序列号。NOORDER表示不排序CACHE20表示缓存为204.3使用序列selects_a.nextvalfromdual;selects_a.currvalfromdual;每用一次nextval，则序列值增加序列归1declarennumber(10);tsqlvarchar2(100);beginselectseq_test.nextvalintonfromdual;n:=-(n-1);tsql:='altersequenceseq_testincrementby'||n;executeimmediatetsql;selectseq_test.nextvalintonfromdual;tsql:='altersequenceseq_testincrementby1';executeimmediatetsql;end;4.4OEM创建序列课后习题填空题（1）模式中的数据库对象的逻辑组织纯粹是为组织打算的，并且绝对与数据库对象的（）无关。（2）索引的主要目标是提高（）的速度。（3）位图索引适合用于那些（）比较小的字段。课后习题选择题（1）UNIQUE关键字表示创建哪一种索引？（）A.唯一性B.B树C.位图D.非唯一性（2）CREATEINDEX语句是用来创建（）A.序列B.同义词C.索引D.视图（3）撤销视图的语句是（）A.DROPVIEWB.DROPINDEXC.DROPVIEWD.DROPSEQUENCE课后习题判断题视图提供一种安全机制，它保证用户只能检索和修改他们看得到的数据，基表中其余数据既不能看到，也不能被存储。（）简答题（1）简述各类索引的作用。（2）简述视图的作用及其使用方式。课后习题操作题（1）通过OEM在表名为DEPT的表上创建索引名为index_dept的索引，然后通过PL/SQLDEVELOPER来删除该索引。（2）通过OEM在表名为DEPT的表上创建视图名为view_deptDE只读视图，然后通过PL/SQLDEVELOPER来删除该视图。答案填空题(1)物理存储(2)存储数据页面(3)基数选择题(1)A(2)C(3)A判断题(1)√