格式设置:
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_objectsWHERE 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
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 count(*) FROM DemoTable2 where owner ='SCOTT';
3)创建owner索引后执行--创建索引
CREATE INDEX idx_owner ON DemoTable2(owner);
SELECT * FROM DemoTable2 where object_name = 'EMP';
SELECT * FROM DemoTable2 where owner ='SCOTT';
小结:创建索引在出现列值出现大量重复时无效,但对聚合操作有效。
--删除原有owner索引
4)创建位图索引后执行
DROP INDEX idx_owner;
CREATE BITMAP INDEX idx_owner_bitmap2 ON DemoTable2(owner);
SELECT * FROM DemoTable2 where object_name = 'EMP';
-- 关闭索引
set autotrace off;
小结:位图索引对聚合操作会产生影响
5、索引的应用小结
索引类似于座次表,是对数据(记录中的特定字段的值)的位置进行排序优化后的树状存储结构,一定是在大数据的环境下有效。索引的优点:提高数据查询指定字段 效率,先建立指定字段的索引,并且指定字段的值具有离散性高的特点。
索引的缺点:占用空间、降低数据更新的效率。
适用于查询多,更新少的大数据环境。
6、创建视图
CREATE VIEW vw_test2 ASselect *
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、视图的应用小结
视图优点:可以简化应用,方便权限管理,整合多来源数据、面向应用重组数据视图中只能在简单视图中进行更新,但有可能更新数据后导致视图记录数发生变化。