索引与视图

– 实验目的:
– 1、理解索引和位图索引的基本概念
– 2、掌握索引和位图索引的创建方法，并使用索引对查询过程产生的影响进行分析
– 3、理解视图的基本概念
– 4、掌握视图的创建方法，并对视图中数据查询与更新进行操作，明确其使用的注意事项。

– 实验步骤：

– 1、创建有特点的大数据表。
– 为了保证索引产生前后，查询效果的正确比对，应建立一个存在大量数据的测试表。这个测试表的数据来源于SYS模式下的all_objects视图，其中包括本数据库实例中的全部对象的基本描述，具体包括对象的所有者、对象名称、创建日期等信息。
– 创建测试表的具体过程：
– 1）以sys账号登录数据库
cmd>sqlplus sys/oracle321@orcl as sysdba (注意sys登录需要注明sysdba身份）
– 2）以创建查询表的方式在scott模式下创建包括‘SYS’、‘PUBLIC’、‘SCOTT’三个用户的全部对象
CREATE TABLE scott.DemoTable as
SELECT * FROM all_objects
WHERE owner IN (‘SYS’,’PUBLIC’,’SCOTT’);

 -- 注意用户名字母必须大写。

– 3) 切换到scott账号进行下一步操作。
conn scott/tiger@orcl;

– 2、查询统计数据表的各数据量大小，了解大数据表中的基本情况。
– 1）了解测试表的字段构成
DESC demoTable;

CREATE TABLE s15436325.stestTable as
SELECT * FROM Demotable;
conn s15436325/s15436325@orcl;
DESC stestTable;

– 1）查询各用户记录数
SELECT owner, count(*)
FROM stestTable
GROUP BY owner;

– 2）查询各列记录值的个数
select count(owner)
,count(object_type)
,count(object_name)
from stestTable;

– 3、分析表，开启追踪
analyze table stestTable compute statistics;
set autotrace trace explain –开启追踪
– 注意：关闭追踪的方法为 set autotrace off;

– 4、对比创建索引前后的查询操作的差异
– 1）无索引时执行查询(注意：名称中字母都要大写)
SELECT * FROM stestTable where object_name = ‘EMP’;
SELECT * FROM stestTable where owner =’SCOTT’;

– 2)创建object_name索引后执行
– –创建索引
CREATE INDEX idx_objectName ON stestTable(object_name);
– –执行查询
SELECT * FROM idx_objectName where object_name = ‘EMP’;
SELECT * FROM stestTable where owner =’SCOTT’;
SELECT count(*) FROM stestTable where owner =’SCOTT’;

– 小结：创建索引只对索引字段出现在where语句中有效。

– 3）创建owner索引后执行
– –创建索引
CREATE INDEX idx_owner ON stestTable(owner);
– –执行查询
SELECT * FROM stestTable where object_name = ‘EMP’;
SELECT * FROM idx_owner where owner =’SCOTT’;
SELECT count(*) FROM idx_owner where owner =’SCOTT’;
– 小结：创建索引在出现列值出现大量重复时无效,但对聚合操作有效。

– 4）创建位图索引后执行
– –删除原有owner索引
DROP INDEX idx_owner;
– –创建位图索引
CREATE BITMAP INDEX idx_owner_bitmap ON stestTable(owner);
– –执行查询
SELECT * FROM stestTable where object_name = ‘EMP’;
SELECT * FROM idx_owner_bitmap where owner =’SCOTT’;
SELECT count(*) FROM idx_owner_bitmap where owner =’SCOTT’;
– 小结：位图索引对聚合操作会产生影响

– 5、索引的应用小结
– 索引类似于座次表，是对数据（记录中的特定字段的值）的位置进行排序优化后的树状存储结构，
– 一定是在大数据的环境下有效
– 索引的优点：提高数据查询指定字段 效率,先建立指定字段的索引,并且指定字段的值具有离散性高的特点
– 缺点：占用空间、降低数据更新的效率
– 适用于查询多，更新少的大数据环境

– 6、创建视图
CREATE VIEW vw_test AS
select *
from emp
where sal+nvl(comm,0) > 2500;

CREATE VIEW vw_SalSummary as
select dname,avg(sal) avgSal,min(sal) minSal,max(sal) maxSal
from dept,emp
where dept.deptno = emp.deptno
group by dname;

– 7、更新视图
UPDATE vw_test set sal= 1000;
UPDATE vw_salSummary set avgSal=1000;

– 8、视图的应用小结
– 视图优点：可以简化应用，方便权限管理，整合多来源数据、面向应用重组数据
– 视图中只能在简单视图中进行更新，但有可能更新数据后导致视图记录数发生变化。