Oracle——Oracle索引与视图的创建与应用


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

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

创建测试表的具体过程:

1、以sys账号登录数据库

cmd>sqlplus sys/'your_sys_password'@orcl as sysdba

(注意sys登录需要注明sysdba身份)

2、以创建查询表的方式在scott模式下创建包括‘SYS’、‘PUBLIC’、‘SCOTT’三个用户的全部对象

CREATE TABLE s15436303.DemoTable
as
SELECT * FROM all_objects
WHERE owner IN ('SYS','PUBLIC','SCOTT');

注意用户名字母必须大写。
这里写图片描述

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

conn scott/tiger@orcl;

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

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

DESC demoTable;

这里写图片描述

1、查询各用户记录数

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

这里写图片描述

2、查询各列记录值的个数

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

这里写图片描述


三、分析表,开启追踪

analyze table demoTable compute statistics;
set autotrace trace explain  --开启追踪

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


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

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

SELECT * FROM demoTable where object_name = 'EMP';

这里写图片描述

SELECT * FROM demoTable where owner ='SCOTT';

这里写图片描述

2、创建object_name索引后执行

a、创建索引

CREATE INDEX idx_objectName ON demoTable(object_name);

这里写图片描述

b、执行查询

SELECT * FROM demoTable where object_name = 'EMP';

这里写图片描述

SELECT * FROM demoTable where owner ='SCOTT';

这里写图片描述

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

这里写图片描述

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

3、创建owner索引后执行

a、创建索引

CREATE INDEX idx_owner ON demoTable(owner);

b、执行查询

SELECT * FROM demoTable where object_name = 'EMP';

SELECT * FROM demoTable where owner ='SCOTT';

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

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

4、创建位图索引后执行

a、删除原有owner索引

DROP INDEX idx_owner;

这里写图片描述

b、创建位图索引

CREATE BITMAP INDEX idx_owner_bitmap ON demoTable(owner);

这里写图片描述

c、执行查询

SELECT * FROM demoTable where object_name = 'EMP';

SELECT * FROM demoTable where owner ='SCOTT';

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

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


五、索引的应用小结

索引类似于座次表,是对数据(记录中的特定字段的值)的位置进行排序优化后的树状存储结构,
一定是在大数据的环境下有效

索引的优点:提高数据查询指定字段 效率,先建立指定字段的索引,并且指定字段的值具有离散性高的特点

缺点:占用空间、降低数据更新的效率

适用于查询多,更新少的大数据环境


六、创建视图

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;

这里写图片描述


七、更新视图

UPDATE vw_test set sal= 1000;

这里写图片描述

UPDATE vw_SalSummary set avgSal=1000;

PS:
CMD命令提示:此视图的数据操纵操纵非法。
这里写图片描述

八、视图的应用小结

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

声明:May丶乘剑的部落小阁|版权所有,违者必究|如未注明,均为原创|本网站采用BY-NC-SA协议进行授权

转载:转载请注明原文链接 - Oracle——Oracle索引与视图的创建与应用


一个偶尔努力、偶尔懈怠的"搬砖"人