索引与视图的创建与应用

格式设置：

set pagesize 66 
set linesize 160 
column ename format a20;

1、创建有特点的大数据表。

为了保证索引产生前后，查询效果的正确比对，应建立一个存在大量数据的测试表。这个测试表的数据来源于SYS模式下的all_objects视图，其中包括本数据库实例中的全部对象的基本描述，具体包括对象的所有者、对象名称、创建日期等信息。
创建测试表的具体过程：

 1）以sys账号登录数据库 (注意sys登录需要注明sysdba身份,以下xxxxxxxxxx是相应的账号密码）

cmd>sqlplus xxxxxxxxxx  as sysdba  

  2）以创建查询表的方式在scott模式下创建包括‘SYS’、‘PUBLIC’、‘SCOTT’三个用户的全部对象(注意用户名字母必须大写。)

CREATE TABLE scott.DemoTable2 
as
SELECT * FROM all_objects 

WHERE owner IN ('SYS','PUBLIC','SCOTT');

 3) 切换到scott账号进行下一步操作。

2、查询统计数据表的各数据量大小，了解大数据表中的基本情况。

 1）了解测试表的字段构成

DESC DemoTable2;

  2）查询各用户记录数

SELECT owner, count(*) 
-- 记录数
FROM DemoTable2
GROUP BY owner;

 3）查询各列记录值的个数

SELECT count(DISTINCT owner), 
  -- 所有者数量 
      count(DISTINCT OBJECT_TYPE), 
  -- 对象类型数量
      count(DISTINCT OBJECT_name) 
  -- 对象名称数量

FROM DemoTable2;

3、分析表，开启追踪

analyze table DemoTable2 compute statistics;

-- 开启追踪

set autotrace trace explain       

              

 注意：关闭追踪的方法为  set autotrace off;   

4、对比创建索引前后的查询操作的差异

  1）无索引时执行查询(注意：名称中字母都要大写)

SELECT * FROM DemoTable2 where object_name = 'EMP';

SELECT * FROM DemoTable2 where owner ='SCOTT';

 2)创建object_name索引后执行

--创建索引

CREATE INDEX idx_objectName2 ON DemoTable2(object_name);

--执行查询
SELECT * FROM DemoTable2 where object_name = 'EMP';

SELECT * FROM DemoTable2 where owner ='SCOTT';

SELECT count(*) FROM DemoTable2 where owner ='SCOTT';

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

  3）创建owner索引后执行

--创建索引

CREATE INDEX idx_owner ON DemoTable2(owner);

--执行查询
SELECT * FROM DemoTable2 where object_name = 'EMP';

SELECT * FROM DemoTable2 where owner ='SCOTT';

SELECT count(*) FROM DemoTable2 where owner ='SCOTT';

小结：创建索引在出现列值出现大量重复时无效,但对聚合操作有效。

 4）创建位图索引后执行

--删除原有owner索引
DROP INDEX idx_owner;

--创建位图索引
CREATE BITMAP INDEX idx_owner_bitmap2 ON DemoTable2(owner);

--执行查询
SELECT * FROM DemoTable2 where object_name = 'EMP';

SELECT * FROM DemoTable2 where owner ='SCOTT';

SELECT count(*) FROM DemoTable2 where owner ='SCOTT';

-- 关闭索引

set autotrace off; 

小结：位图索引对聚合操作会产生影响

5、索引的应用小结

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

6、创建视图

CREATE VIEW vw_test2 AS
select * 
from emp 
where sal+nvl(comm,0) > 2500;

CREATE VIEW vw_SalSummary2 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_test2 set sal= 1000;

UPDATE vw_salSummary2 set avgSal=1000;

注：这肯定是行不通。因为视图只允许操作简单的数据，而且avgSal还涉及其他数据，一改动，其他数据也会改动，导致数据出错

8、视图的应用小结

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