oracle学习笔记——视图、索引
1.视图（VIEW）
1.1 概念
视图-----是由SELECT查询语句(可以是单表或者多表查询)定义的一个"逻辑表",只有定义而无数据,是一个"虚表". 在创建视图时,只是将视图的定义信息保存在数据字典中, 而并不将实际的数据复制到任何地方, 即不需要在表空间中为视图分配存储空间. 视图是查看和操纵基表数据的一种方法, 可以像使用表一样使用视图.
tips: 查询视图没有什么限制, 插入/更新/删除视图的操作会受到一定的限制; 所有针对视图的操作都会影响到视图的基表; 为了防止用户通过视图间接修改基表的数据, 可以将视图创建为只读视图(带上with read only选项).
视图中的数据会随基表的更新而自动更新.
视图犹如基表的一个"窗口", 通过这个"窗口", 可以实施许多管理. 在一个视图中可以定义的最大列数为1000, 与表的限制相同.
视图约束: 允许在视图上生成约束, 如"主键约束、唯一键约束、外键约束、检查约束"等. 但视图上的约束不是强制性的, 而是声明性的. 视图约束的语法与表相同. 在创建视图时, 可以使用with check option选项,给视图定义check约束,使其只能查询、操作满足check 约束的记录行.
1.2 作用
1)提供各种数据表现形式, 可以使用各种不同的方式将基表的数据展现在用户面前, 以便符合用户的使用习惯(主要手段: 使用别名).
2)隐藏数据的逻辑复杂性并简化查询语句, 多表查询语句一般是比较复杂的, 而且用户需要了解表之间的关系, 否则容易写错; 如果基于这样的查询语句创建一个视图, 用户就可以直接对这个视图进行"简单查询"而获得结果. 这样就隐藏了数据的复杂性并简化了查询语句. 这也是oracle提供各种"数据字典视图"的原因之一,all_constraints就是一个含有2个子查询并连接了9个表的视图(在catalog.sql中定义).
3)执行某些必须使用视图的查询. 某些查询必须借助视图的帮助才能完成. 比如, 有些查询需要连接一个分组统计后的表和另一表, 这时就可以先基于分组统计的结果创建一个视图, 然后在查询中连接这个视图和另一个表就可以了.
4)提供某些安全性保证. 视图提供了一种可以控制的方式, 即可以让不同的用户看见不同的列, 而不允许访问那些敏感的列, 这样就可以保证敏感数据不被用户看见.
5)简化用户权限的管理. 可以将视图的权限授予用户, 而不必将基表中某些列的权限授予用户, 这样就简化了用户权限的定义.
1.3 创建视图
权限: 要在当前方案中创建视图, 用户必须具有create view系统权限; 要在其他方案中创建视图, 用户必须具有create any view系统权限. 视图的功能取决于视图拥有者的权限.
语法: create [ or replace ] [ force ] view [schema.]view_name
[ (column1,column2,...) ]
as
select ...
[ with check option ] [ constraint constraint_name ]
[ with read only ];
tips:
or replace: 如果存在同名的视图, 则使用新视图"替代"已有的视图
force: "强制"创建视图,不考虑基表是否存在,也不考虑是否具有使用基表的权限
column1,column2,...：视图的列名, 列名的个数必须与select查询中列的个数相同; 如果select查询包含函数或表达式, 则必须为其定义列名. 此时, 既可以用column1, column2指定列名, 也可以在select查询中指定列名.
with check option: 指定对视图执行的dml操作必须满足“视图子查询”的条件即,对通过视图进行的增删改操作进行"检查",要求增删改操作的数据, 必须是select查询所能查询到的数据, 否则不允许操作并返回错误提示. 默认情况下, 在增删改之前"并不会检查"这些行是否能被select查询检索到.
with read only：创建的视图只能用于查询数据, 而不能用于更改数据.
1.3.1 创建简单视图
是指基于单个表建立的，不包含任何函数、表达式和分组数据的视图。
示例1：基于emp表创建一个vw_emp视图
create view vw_emp
as
select empno,ename,job,hiredate,deptno from emp; --------创建简单视图
desc vw_emp; --------象表一样使用
select * from vw_emp where deptno=10;--------查询
insert into vw_emp values(1234,'JACK','CLERK','29-4月-1963',10);--------增加
update vw_emp set ename='刘德华' where ename='JACK';--------更新
delete vw_emp where ename='刘德华'; --------删除
create view vw_emp_readonly
as
select empno,ename,job,hiredate,deptno from emp
with read only ; --------创建只读视图，只能用于执行select语句
delete from vw_emp_readonly where empno=1234;--------删除失败
create view vw_emp_check
as
select empno,ename,job,hiredate,deptno
from emp where deptno=10
with check option constraint vw_emp_chk ;
--------创建检查视图：对通过视图进行的增删改操作进行检查，
要求增删改操作的数据必须是select查询所能查询到的数据。
insert into vw_emp_check
values(1235,'JACK','CLERK','29-4月-1963',20);
--------20号部门不在查询范围内，违反检查约束
delete from vw_emp_check where empno=1234;
--------所删除的数据在查询范围内，不违反检查约束
1.3.2 创建连接视图
是指基于多个表所创建的视图，即，定义视图的查询是一个连接查询。主要目的是为了简化连接查询
示例1：查询部门编号为10和30的部门及雇员信息
create view vw_dept_emp
as
select a.deptno,a.dname,a.loc,b.empno,b.ename,b.sal
from dept a , emp b
where a.deptno=b.deptno
and a.deptno in (10,30);
select * from vw_dept_emp;
1.3.3 创建复杂视图
是指包含函数、表达式、或分组数据的视图。主要目的是为了简化查询。主要用于执行查询操作，并不用于执行DML操作。
注意：当视图的select查询中包含函数或表达式时，必须为其定义列别名。
示例1：查询目前每个岗位的平均工资、工资总和、最高工资和最低工资。
create view vw_emp_job_sal
(job,avgsal,sumsal,maxsal,minsal)
as
select job,avg(sal),sum(sal),max(sal),min(sal)
from emp
group by job;
select * from vw_emp_job_sal;
1.3.4 强制创建视图
正常情况下，如果基表不存在，创建视图就会失败。但是可以使用force选项强制创建视图(前提：创建视图的语句没有语法错误！)，此时该视图处于失效状态。
示例1：
create force view vw_test_tab
as
select c1,c2 from test_tab;--------会出现“警告: 创建的视图带有编译错误。”
select object_name,status from user_objects
where object_name='VW_TEST_TAB';--------视图状态：INVALID
select * from vw_test_tab;--------报错
create table test_tab--------先建表
(c1 number(9) primary key, c2 varchar2(20),c3 varchar2(30));
select * from vw_test_tab;--------自动编译失效的视图
select object_name,status from user_objects
where object_name='VW_TEST_TAB';--------视图状态：VALID
1.4 更改视图
在对视图进行更改(或重定义)之前，需要考虑如下几个问题：
之一——由于视图只是一个虚表，其中没有数据，所以更改视图只是改变数据字典中对该视图的
定义信息，视图的所有基础对象都不会受到任何影响
之二——更改视图之后，依赖于该视图的所有视图和PL/SQL程序都将变为INVALID(失效)状态
之三——如果以前的视图中具有with check option选项，但是重定义时没有使用该
选项，
则以前的此选项将自动删除。
1.4.1 更改视图的定义
方法——执行create or replace view语句。这种方法代替了先删除(“权限也将随之删除”)
后创建的方法，会保留视图上的权限，但与该视图相关的存储过程和视图会失效。
示例1：create or replace view v_test_tab
as
select c1,c2||' + '||c3 c23 from test_tab;
1.4.2 视图的重新编译
语法：alter view 视图名compile;
作用：当视图依赖的基表改变后，视图会“失效”。为了确保这种改变“不影响”视图和依赖于该视图的
其他对象，应该使用alter view 语句“明确的重新编译”该视图，从而在运行视图前发现重新
编译的错误。视图被重新编译后，若发现错误，则依赖该视图的对象也会失效；若没有错误，
视图会变为“有效”。
权限：为了重新编译其他模式中的视图，必须拥有alter any table系统权限。
注意：当访问基表改变后的视图时，oracle会“自动重新编译”这些视图。
示例1：select last_ddl_time,object_name,status
from user_objects
where object_name='V_TEST_TAB';——视图的状态:有效
alter table test_tab modify (c2 varchar2(30));——修改c2列的长度
select last_ddl_time,object_name,status
from user_objects
where object_name='V_TEST_TAB';——视图的状态:失效
alter view v_test_tab compile;——明确的重新编译
select last_ddl_time,object_name,status
from user_objects
where object_name='V_TEST_TAB';——视图的状态:有效
思考：若上述代码修改的不是列长，而是表名，结果又会如何？
<警告：更改的视图带有编译错误；视图状态：失效>
1.5 删除视图
可以删除当前模式中的任何视图；
如果要删除其他模式中的视图，必须拥有DROP ANY VIEW系统权限；
视图被删除后，该视图的定义会从词典中被删除，并且在该视图上授予的“权限”也将被删除。
视图被删除后，其他引用该视图的视图及存储过程等都会失效。
示例1：drop view vw_test_tab;
1.6 查看视图
使用数据字典视图
dba_views——DBA视图描述数据库中的所有视图
all_views——ALL视图描述用户“可访问的”视图
user_views——USER视图描述“用户拥有的”视图
dba_tab_columns——DBA视图描述数据库中的所有视图的列(或表的列)
all_tab_columns——ALL视图描述用户“可访问的”视图的列(或表的列)
user_tab_columns——USER视图描述“用户拥有的”视图的列(或表的列)
示例1：查询当前方案中所有视图的信息
desc user_views;
set long 400;
select view_name,text from user_views;
示例1：查询当前方案中指定视图(或表)的列名信息
select * from user_tab_columns where table_name='VW_DEPT';
1.7 在连接视图上执行DML操作
在视图上进行的所有DML操作，最终都会在基表上完成；
select 视图没有什么限制，但insert/delete/update有一些限制
1.7.1 在视图上执行DML操作的步骤和原理
第一步：将针对视图的SQL语句与视图的定义语句(保存在数据字典中)“合并”成一条SQL语句
第二步：在内存结构的共享SQL区中“解析”(并优化)合并后的SQL语句
第三步：“执行”SQL语句
示例：
假设视图v_emp的定义语句如下：
create view v_emp
as
select empno,ename,loc
from employees emp,departments dept
where emp.deptno=dept.deptno and dept.deptno=10;
当用户执行如下查询语句时：
select ename from v_emp
where empno=9876;
oracle将把这条SQL语句与视图定义语句“合并”成如下查询语句：
select ename
from employees emp,departments dept
where emp.deptno=dept.deptno and dept.deptno=10
and empno=9876;
然后，解析(并优化)合并后的查询语句，并执行查询语句
1.7.2 查询视图“可更新”(包括“增删改”)的列
使用数据字典视图
dba_updatable_columns——显示数据库所有视图中的所有列的可更新状态
all_updatable_columns——显示用户可访问的视图中的所有列的可更新状态
user_updatable_columns——显示用户拥有的视图中的所有列的可更新状态
示例1：查询v_stu_dept中的哪些列是可更新的
select table_name,column_name,insertable,updatable,deletable
from user_updatable_columns
where table_name='V_STU_DEPT';
1.7.3 可更新连接视图
如果创建连接视图的select查询“不包含”如下结构，
并且遵守连接视图的“更新准则”，
则这样的连接视图是“可更新”的：
之一：集合运算符(union,intersect,minus)
之二：DISTINCT关键字
之三：GROUP BY，ORDER BY，CONNECT BY或START WITH子句
之四：子查询
之五：分组函数
之六：需要更新的列不是由“列表达式”定义的
之七：基表中所有NOT NULL列均属于该视图
1.7.4 键值保存表
如果连接视图中的一个“基表的键”(主键、唯一键)在它的视图中仍然存在，
并且“基表的键”仍然是“连接视图中的键”(主键、唯一键)；
即，某列在基表中是主键|唯一键，在视图中仍然是主键|唯一键
则称这个基表为“键值保存表”。
一般地，由主外键关系的2个表组成的连接视图，外键表就是键值保存表，而主键表不是。
1.7.5 连接视图的更新准则
之一：一般准则——(讲)
任何DML操作，只能对视图中的键值保存表进行更新,
即，“不能通过连接视图修改多个基表”;
在DML操作中，“只能使用连接视图定义过的列”;
“自连接视图”的所有列都是可更新(增删改)的
之二：insert准则
在insert语句中不能使用“非键值保存表”中的列(包括“连接列”)；
执行insert操作的视图，至少应该“包含”键值保存表中所有设置了约束的列；
如果在定义连接视图时使用了WITH CHECK OPTION 选项，
则“不能”针对连接视图执行insert操作
之三：update准则
键值保存表中的列是可以更新的；
如果在定义连接视图时使用了WITH CHECK OPTION 选项，
则连接视图中的连接列(一般就是“共有列”)和基表中的“其他共有列”是“不可”更新的，
连接列和共有列之外的其他列是“可以”更新的
之四：delete准则
如果在定义连接视图时使用了WITH CHECK OPTION 选项，
依然“可以”针对连接视图执行delete操作
2 索引
目标是为了提高查询的速度，当用户对查询速度不满意而需要对数据库的性能进行调
校时，优先考虑建立索引。
数据库中索引的概念与书索引的概念非常类似，
不同之处在于数据库索引用来在表中查找特定的行。
索引缺点：
向表中“添加/删除”行时，必须花费额外的时间来更新该行的索引。
创建索引的时机：
当需要从大表中检索少数几行时，都应该对列创建索引。
基本准则：
当任何单个查询要检索的行<=整个表行数的10%时，索引就非常有用。
索引的候选列：
应该是用来存储很大范围的值的列
自动创建索引：
表的主键和唯一键将自动创建索引
2.1 语法
create [unique] index 索引名-------unique指定索引列中的值是唯一的，索引名建议以idx打头
on 表名(列1, 列2... ...) --------可以对多列创建索引，这种索引称为“复合索引”
[tablespace 表空间名]; --------省去后，索引将被存储到用户的默认表空间中
提示：
出于性能方面的原因，通常应该将索引与表存储到不同的表空间中
示例：
create index idx_customers_lastname on customers(last_name);
2.2 查询索引和索引列的信息
select * from user_indexes where table_name in('CUSTOMERS', 'EMPLOYEES');
select * from user_ind_columns where table_name in('CUSTOMERS', 'EMPLOYEES');
2.3 修改索引
alter index 索引名rename to 新索引名
2.4 删除索引
drop index 索引名
1.视图的概述 视图其实就是一条查询sql语句，用于显示一个或多个表或其他视图中的相关数据。视图将一个查询的结果作为一个表来使用，因此视图可以被看作是存储的查询或一个虚拟表。视图来源于表，所有对视图数据的修改最终都会被反映到视图的基表中，这些修改必须服从基表的完整性约束，并同样会触发定义在基表上的触发器。(Oracle支持在视图上显式的定义触发器和定义一些逻辑约束) 2.视图的存储 与表不同，视图不会要求分配存储空间，视图中也不会包含实际的数据。视图只是定义了一个查询，视图中的数据是从基表中获取，这些数据在视图被引用时动态的生成。由于视图基于数据库中的其他对象，因此一个视图只需要占用数据字典中保存其定义的空间，而无需额外的存储空间。 3.视图的作用 用户可以通过视图以不同形式来显示基表中的数据，视图的强大之处在于它能够根据不同用户的需要来对基表中的数据进行整理。视图常见的用途如下： 通过视图可以设定允许用户访问的列和数据行，从而为表提供了额外的安全控制 隐藏数据复杂性 视图中可以使用连接(join)，用多个表中相关的列构成一个新的数据集。此视图就对用户隐藏了数据来源于多个表的事实。 简化用户的SQL 语句 用户使用视图就可从多个表中查询信息，而无需了解这些表是如何连接的。 以不同的角度来显示基表中的数据 视图的列名可以被任意改变，而不会影响此视图的基表 使应用程序不会受基表定义改变的影响 在一个视图的定义中查询了一个包含4 个数据列的基表中的3 列。当基表中添加了新的列后，由于视图的定义并没有被影响，因此使用此视图的应用程序也不会被影响。 保存复杂查询 一个查询可能会对表数据进行复杂的计算。用户将这个查询保存为视图之后，每次进行类似计算只需查询此视图即可。
龙源期刊网 https://www.doczj.com/doc/cd13629944.html, Oracle索引分析与查询优化 作者：崔黎明志远李婧 来源：《数字技术与应用》2016年第07期 摘要：Oracle是目前国内在大型数据存储中用得比较多的一种关系型数据库，磁盘阵列技术（RAID）和集群技术（RAC）的运用，使Oracle在处理数据效率和数据安全上有非常大的提高，在国内交通、电力，通信和金融等重要领域都有广泛的用途。本文基于Oracle 11g版本，对Rowid和索引的原理机制做出分析，并论述利用这些原理对大型数据表查询的优化。 关键词：Oracle Rowid 索引查询 中图分类号：TP311.13 文献标识码：A 文章编号：1007-9416（2016）07-0234-02 在逻辑上，Oracle是由多个表空间构成的，在新建一个表空间的时候，必须指定存储的文件，可以指向多个存储在不同磁盘上的数据。表空间下面分为段、区、块。新建一张表的时候，Oracle数据库就会为它创建一个段。所谓区就是指连续的块（block）构成的空间，一般区包含8个空块，block是Oracle数据库最小的数据空间，一般为8k或16k，当开始往这个表中插入数据的时候，Oralce数据库会自动为这个表分配一个区，并把数据不断往此区进行填充，当数据填满此区后，Oralce数据库会重新为该表分配一个区而不是一个段。实际上，对于我们不同的查询过程中，就是通过一些谓词过滤条件，从对应的数据块中获取正确的一行数据或多行数据。如何快速定位到该数据行，是一个数据库学习者不断探索的方向。 1 Rowid 1.1 简介 从字面上理解是行标识的意思，它是Oracle数据库中数据表的一个伪列，用于存放该表中每一行数据的地址，在8i版本之前，Oracle采用受限制的rowid，它是由数据文件编号，块编号和数据在该块内的偏移量这三个部分构成，长度为6个字节，因此在8i之前的版本中，每 个数据库最多可以包含1022个文件，每个文件最多能有4m个数据块，而每个数据库最多能 存储64k条记录。为了突破长度的限制和解决其他一些缺陷，Oracle数据库引入了这样一个概念：相对文件号。它的主要特点是改变之前rowid中数据文件编号是整个数据库范围组成的表空间，即文件编号为5的文件不再是数据库中编号为5的数据文件，而是表空间中对应编号的数据文件。如下图1所示为Oracle11g中一个普通表的rowid。 从该图1中可以看到rowid共有18位，分为四部分，格式为：AAAAAABBBCCCCCCDDD，其中AAAAAA六位表示dataobjectid，根据这个id可以确定该行数据在哪个段中；BBB三位表示相对文件号，通过这个字段号可以用来确定该行数据的绝 对文件号；CCCCCC六位表示datablocknumber，它是相对于datafile的编号；最后三位DDD
oracle视图总结(转) 视图简介: 视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表。视图是存储在数据字典里的一条select语句。通过创建视图可以提取数据的逻辑上的集合或组合。 视图的优点： 1.对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。 2.用户通过简单的查询可以从复杂查询中得到结果。 3.维护数据的独立性，试图可从多个表检索数据。 4.对于相同的数据可产生不同的视图。 视图的分类： 视图分为简单视图和复杂视图。 两者区别如下： 1.简单视图只从单表里获取数据，复杂视图从多表获取数据； 2.简单视图不包含函数和数据组，复杂视图包含； 3.简单视图可以实现DML操作，复杂视图不可以。 视图的创建： CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY] 其中： OR REPLACE：若所创建的试图已经存在，ORACLE自动重建该视图； FORCE：不管基表是否存在ORACLE都会自动创建该视图； NOFORCE：只有基表都存在ORACLE才会创建该视图： alias：为视图产生的列定义的别名； subquery：一条完整的SELECT语句，可以在该语句中定义别名； WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束； WITH READ ONLY ：该视图上不能进行任何DML操作。 例如： Sql代码 1.CREATE OR REPLACE VIEW dept_sum_vw 2.(name,minsal,maxsal,avgsal)
总结ORACLE系统视图及表大全： dba_开头..... dba_users 数据库用户信息 dba_segments 表段信息 dba_extents 数据区信息 dba_objects 数据库对象信息 dba_tablespaces 数据库表空间信息 dba_data_files 数据文件设置信息 dba_temp_files 临时数据文件信息 dba_rollback_segs 回滚段信息 dba_ts_quotas 用户表空间配额信息 dba_free_space数据库空闲空间信息 dba_profiles 数据库用户资源限制信息 dba_sys_privs 用户的系统权限信息 dba_tab_privs用户具有的对象权限信息dba_col_privs用户具有的列对象权限信息dba_role_privs用户具有的角色信息 dba_audit_trail审计跟踪记录信息 dba_stmt_audit_opts审计设置信息 dba_audit_object 对象审计结果信息 dba_audit_session会话审计结果信息 dba_indexes用户模式的索引信息 user_开头 user_objects 用户对象信息 user_source 数据库用户的所有资源对象信息user_segments 用户的表段信息 user_tables 用户的表对象信息 user_tab_columns 用户的表列信息 user_constraints 用户的对象约束信息 user_sys_privs 当前用户的系统权限信息
user_tab_privs 当前用户的对象权限信息 user_col_privs 当前用户的表列权限信息 user_role_privs 当前用户的角色权限信息 user_indexes 用户的索引信息 user_ind_columns用户的索引对应的表列信息 user_cons_columns 用户的约束对应的表列信息 user_clusters 用户的所有簇信息 user_clu_columns 用户的簇所包含的内容信息 user_cluster_hash_expressions 散列簇的信息 v$开头 v$database 数据库信息 v$datafile 数据文件信息 v$controlfile控制文件信息 v$logfile 重做日志信息 v$instance 数据库实例信息 v$log 日志组信息 v$loghist 日志历史信息 v$sga 数据库SGA信息 v$parameter 初始化参数信息 v$process 数据库服务器进程信息 v$bgprocess 数据库后台进程信息 v$controlfile_record_section 控制文件记载的各部分信息v$thread 线程信息 v$datafile_header 数据文件头所记载的信息 v$archived_log归档日志信息 v$archive_dest 归档日志的设置信息 v$logmnr_contents 归档日志分析的DML DDL结果信息v$logmnr_dictionary 日志分析的字典文件信息 v$logmnr_logs 日志分析的日志列表信息 v$tablespace 表空间信息
ORACLE数据字典与视图 当ORACLE数据库系统启动后，数据字典总是可用，它驻留在SYSTEM表空间中，所有权属于sys(DBA)用户。 数据字典包含数据库中所有模式对象（包括表、视图、索引、聚簇、同义词、序列、过程、函数、包、触发器等）的定义、列的默认值、完整性约束的定义、用户的权限和角色信息、存储空间分配情况、审计信息、字符集信息等数据库信息。 为了方便用户查询，在数据字典表上建立了数据字典视图集。视图集分为三种，这些视图包含有类似信息，彼此以前缀相区别，前缀为USER、ALL和DBA。 ▽前缀为USER_的视图，为用户视图，是在用户的模式内，包含当前用户所拥有的全部对象信息。如：USER_OBJECTS视图包含当前用户所建立的对象信息。 ▽前缀为ALL_的视图，为扩展的用户视图，除包含当前用户所拥有的全部对象信息以外，还包含公共帐号和显式授权用户所拥有的全部模式对象信息。如：ALL_USERS。 ▽前缀为DBA_的视图，为DBA的视图，包含整个数据库的所有用户所拥有的所有对象信息，而不局限于部分用户。如：DBA_USERS视图包含数据库中所有用户信息。只有DBA用户或被授予select_any_dictionary系统权限的用户才能够访问DBA视图。 在数据库ORACLE还维护了一组虚表（virtual table），记录当前数据库的活动情况和性能参数，这些表称为动态性能表。动态性能表的拥有者为SYS用户，名字均以V_$或GV_$为前缀。动态性能表不是真正的表，许多用户不能直接存取。DBA可通过查询这些表，了解系统运行状况、诊断和解决系统运行中出现的问题。DBA可以建立视图，给其它用户授予存取视图权。为了便于访问，Oracle在动态性能表的基础上建立了公用同义词，这些同义词的名字以V_$开头。如V_$BGPROCESS视图记录Oracle后台进程信息。 Sys帐号进去在视图下可见全部数据字典。其它帐号只能通过SQL语句查询 ORACLE数据字典与视图（部分，不全） 视图名说明
Oracle视图中建立索引的注意事项 在视图上创建索引需要三个条件： 一、视图必须绑定到架构。 要做到这点，在 CREATE VIEW 语句中，必须加上 WITH SCHEMABINDING，如果是使用企业管理器，则在设计界面的空白处点击右键，属性，选中“绑定到架构”。 二、索引必须是唯一索引。 要做到这点，在 CREATE INDEX 中必须指定 UNIQUE。 三、索引必须是聚集索引。 要做到这点，在 CREATE INDEX 中必须指定 CLUSTERED。 例： CREATE VIEW viewFoo WITH SCHEMABINDING AS SELECT id... CREATE UNIQUE CLUSTERED INDEX index_viewFoo ON viewFoo(id) 在视图上创建聚集索引之前，该视图必须满足下列要求： 当执行 CREATE VIEW 语句时，ANSI_NULLS 和 QUOTED_IDENTIFIER 选项必须设置为 ON。OBJECTPROPERTY 函数通 过 ExecIsAnsiNullsOn 或 ExecIsQuotedIdentOn 属性为视图报告此信息。 为执行所有 CREATE TABLE 语句以创建视图引用的表，ANSI_NULLS 选项必须设置为 ON。 视图不能引用任何其它视图，只能引用基表。 视图引用的所有基表必须与视图位于同一个数据库中，并且所有者也与视图相同。 必须使用 SCHEMABINDING 选项创建视图。SCHEMABINDING 将视图绑定到基础基表的架构。 必须已使用 SCHEMABINDING 选项创建了视图中引用的用户定义的函数。 表和用户定义的函数必须由 2 部分的名称引用。不允许使用 1 部分、3 部分和 4 部分的名称。
Oracle 显示索引信息 为了显示Oracle索引的信息，Oracle提供了一系列的数据字典视图。通过查询这些数据字典视图，用户可以了解索引的各方面信息。 1．显示表的所有索引 索引是用于加速数据存储的数据库对象。通过查询数据字典视图DBA_INDEXES，可以显示数据库的所有索引；通过查询数据字典视图ALL_INDEXES，可以显示当前用户可访问的所有索引；查询数据字典视图USER_INDEXES，可以显示当前用户的索引信息。下面以显示SCOTT用户EMP表的所有索引为例，说明使用数据字典视图DBA_INDEXES的方法： SQL> connect system/password 已连接。 SQL> select index_name,index_type,uniqueness 2 from dba_indexes 3 where owner='SCOTT' and table_name='EMP'; INDEX_NAME INDEX_TYPE UNIQUENES ------------------------------ --------------------------- --------- EMP_ENAME_INDEX NORMAL NONUNIQUE EMP_JOB_BMP BITMAP NONUNIQUE IDX_ENAME FUNCTION-BASED NORMAL NONUNIQUE PK_EMP NORMAL/REV UNIQUE 如上所示，INDEX_NAME用于标识索引名。INDEX_TYPE用于标识索引类型：NORMAL表示普通B树索引；REV表示反向键索引；BITMAP表示位图索引；FUNCTION 表示基于函数的索引。UNIQUENESS用于标识索引的惟一性；OWNER用于标识对象的所有者；TABLE_NAME用于标识表名。 2．显示索引列 创建索引时，需要提供相应的表列。通过查询数据字典视图DBA_IND_COLUMNS，可以显示所有索引的表列信息；通过查询数据字典视图ALL_IND_COLUMNS，可以显示当前用户可访问所有索引的表列信息；通过查询数据字典视图USER_IND_COLUMNS，可以显示当前用户索引的表列信息。 例如，下面的语句将显示SCOTT用户的PK_EMP索引列信息： SQL> col column_name format a20 SQL> select column_name,column_position,column_length 2 from user_ind_columns 3 where index_name='PK_EMP'; COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH -------------------- ---------------------------- ------------- EMPNO 1 22 如上所示，COLUMN_NAME用于标识索引列的名称；COLUMN_POSITION用于标识列在索引中的位置；COLUMN_LENGTH用于标识索引列的长度。
实验项目名称：视图、索引和完整性等实验学时： 3 同组学生姓名：实验地点： 实验日期：实验成绩： 批改教师：批改时间：
实验4 视图、索引和完整性等 一、实验目的和要求 1、了解视图的基本概念、种类及各自的特点与作用。 2、理解索引的基本概念及其优缺点。 3、理解修改数据时索引的开销。 4、理解数据完整性的概念及分类。 5、理解同义词和序列的基本概念。 6、掌握在OEM中创建视图、索引、实体完整性、域完整性和参照完整性以及同义词、序列的方法。 7、掌握用PL/SQL语言创建视图、索引、各种约束、同义词和序列的方法。 二、实验设备、环境 设备：奔腾Ⅳ或奔腾Ⅳ以上计算机 环境：WINDOWS 2000 SERVER或WINDOWS 2003 SERVER、ORACLE 10g中文版 三、实验步骤 1、分析题意，重点分析题目要求并给出解决方法。 2、根据题目要求启动SQL*Plus、iSQL*Plus、OEM等管理工具。 3、按题目要求完成实际操作任务，并将相关文档资料保存在以自己学号命名的文件夹中。 4、提交完成的实验结果。 四、实验内容 1、视图的创建与使用。 （1）创建一个名为cx_employees的视图，只允许查看雇员的编号、姓名、生日、性别和部门的编号。 （2）创建一个名为cx_salary的视图，要求只显示财务部雇员的姓名和薪水情况。 使用视图，查询财务部雇员中姓名为王林的信息。 （3）通过视图向Employees表插入一条信息：雇员编号：510888；姓名：张无忌；出生年月1982年8月23日；性别：男；部门编号：3。 （4）将张无忌从经理办公室转到市场部。 （5）将张无忌从Employees表中删除。 2、创建索引。 （1）对ORCL数据库中的Employees表中的DepartmentID属性上建立一个名为DeptID_index 索引，并指定索引的存储特征值，数据库中的行以升序保存，将索引建立在用户默认的表空间里。 （2）在Employees表的sex列上建一个位图索引。 （3）删除这两个索引。
Oracle 数据库视图与基表的关系 一：首先解释什么是视图：视图其实就是一条查询sql语句，用于显示一个或多个表或其他视图中的相关数据。视图将一个查询的结果作为一个表来使用，因此视图可以被看作是存储的查询或一个虚拟表，与真实表不同，视图不会要求分配存储空间，视图中也不会包含实际的数据。视图只是定义了一个查询，视图中的数据是从基表中获取，这些数据在视图被引用时动态的生成。由于视图基于数据库中的其他对象，因此一个视图只需要占用数据字典中保存其定义的空间，而无需额外的存储空间，并且基表的变化会导致视图相应的改变。 二：视图的创建： CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY] 其中： OR REPLACE：若所创建的试图已经存在，Oracle自动重建该视图； FORCE：不管基表是否存在ORACLE都会自动创建该视图； NOFORCE：只有基表都存在ORACLE才会创建该视图： alias：为视图产生的列定义的别名； subquery：一条完整的SELECT语句，可以在该语句中定义别名可以挑选某个表中你需要的属性； WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束； WITH READ ONLY ：该视图上不能进行任何DML操作。 三：视图的修改：直接利用前边创建时的or replaece 重建即可。 四：视图上的DML 操作： 1.一般简单视图，也就是基表只有一个的视图，是可以通过修改视图来修改基表的，Oracle是可以通过视图来修改Base table的。所谓base table就是用来构建视图的表，也就是视图的数据来源表。但是这种修改是有条件的。比如： create view v_emp as select empno,ename,job,deptno from emp where deptno=10 with check option constraint emp_cnst; 如果有这个限制，那么通过视图v_emp 插入数据的deptno字段的值必须是10，否则就会报“ORA-01402: 视图WITH CHECK OPTIDN 违反where 子句”的异常。 2.针对复杂视图，也就是基表有多个表，通过内连接查询建立的视图，只能通过视图来修改key_preserved表， 什么是Key-Preserved Table呢.Oracle给出的定义是: A table is key preserved if every key of the table can also be a key of the result of the join. It is not necessary that the key or keys of a table be selected for it to be key preserved. It is sufficient that if the key or keys were selected, then they would also be key(s) of the result of the join. 如果某一个表的主键可以作为这个join结果(view通常是几个表的join结果)的主键,那么这个表就是key preserved table。 这个表的主键并非一定要出现在select出来的结果集中(select list里面),但是如果其出现在结果集中,那么它必须可以满足作为这个结果集的主键的要求。 通过下面的例子来解释： create view liuwenhe as select e.ename, e.empno, e.job, d.deptno, d.dname from emp e,dept d where e.empno in (10,30) and e.deptno = d.deptno；emp表的主键是empno,dept表的主键
第13讲使用视图、索引、序列和同义词对象 1、使用视图（view） 视图是基于其他表或其他视图的逻辑表。 视图的作用： （1）限制数据访问，访问视图时只能访问select语句所涉及到的列。 （2）简化复杂查询，如果经常需要在多个表之间执行复杂查询操作，可以基于该复杂查询语句建立视图。 视图分类： （1）简单视图，基于单个表所建视图，不包含任何函数、表达式及分组数据的视图。（2）复杂视图，包含函数、表达式或者分组数据的视图。 （3）连接视图，基于多表所建立的视图。 （4）只读视图，至允许执行查询操作。 在视图上执行DML操作的原则： （1）DELETE操作原则：如果视图包含有group by子句、分组函数、distinct关键字和rownum 伪列，那么不能在该视图上执行delete操作。 （2）UPDATE操作原则：如果视图包含有group by子句、分组函数、distinct关键字和rownum伪列，以及使用表达式所定义的列，那么不能在该视图上执行update操作。（3）INSERT操作原则：如果视图包含有group by子句、分组函数、distinct关键字和rownum 伪列，以及使用表达式所定义的列，或者在视图上没有包含视图基表的not null列，那么不能在该视图上执行insert操作。 使用system帐号授予scott账户create view的权限。 SQL> grant create view to scott; 1.1 建立视图 （1）建立简单的视图 例：建立视图vu_emp，包含emp表的empno,ename,sal列 create view vu_emp as select empno,ename,sal from emp; 查看视图列 SQL> desc vu_emp; Name Type Nullable Default Comments ----- ------------ -------- ------- -------- EMPNO NUMBER(4) ENAME VARCHAR2(10) Y SAL NUMBER(7,2) Y 使用数据字典user_views查看用户视图
oracle学习笔记——视图、索引 1.视图（VIEW） 1.1 概念 视图-----是由SELECT查询语句(可以是单表或者多表查询)定义的一个"逻辑表",只有定义而无数据,是一个"虚表". 在创建视图时,只是将视图的定义信息保存在数据字典中, 而并不将实际的数据复制到任何地方, 即不需要在表空间中为视图分配存储空间. 视图是查看和操纵基表数据的一种方法, 可以像使用表一样使用视图. tips: 查询视图没有什么限制, 插入/更新/删除视图的操作会受到一定的限制; 所有针对视图的操作都会影响到视图的基表; 为了防止用户通过视图间接修改基表的数据, 可以将视图创建为只读视图(带上with read only选项). 视图中的数据会随基表的更新而自动更新. 视图犹如基表的一个"窗口", 通过这个"窗口", 可以实施许多管理. 在一个视图中可以定义的最大列数为1000, 与表的限制相同. 视图约束: 允许在视图上生成约束, 如"主键约束、唯一键约束、外键约束、检查约束"等. 但视图上的约束不是强制性的, 而是声明性的. 视图约束的语法与表相同. 在创建视图时, 可以使用with check option选项,给视图定义check约束,使其只能查询、操作满足check 约束的记录行. 1.2 作用 1)提供各种数据表现形式, 可以使用各种不同的方式将基表的数据展现在用户面前, 以便符合用户的使用习惯(主要手段: 使用别名). 2)隐藏数据的逻辑复杂性并简化查询语句, 多表查询语句一般是比较复杂的, 而且用户需要了解表之间的关系, 否则容易写错; 如果基于这样的查询语句创建一个视图, 用户就可以直接对这个视图进行"简单查询"而获得结果. 这样就隐藏了数据的复杂性并简化了查询语句. 这也是oracle提供各种"数据字典视图"的原因之一,all_constraints就是一个含有2个子查询并连接了9个表的视图(在catalog.sql中定义). 3)执行某些必须使用视图的查询. 某些查询必须借助视图的帮助才能完成. 比如, 有些查询需要连接一个分组统计后的表和另一表, 这时就可以先基于分组统计的结果创建一个视图, 然后在查询中连接这个视图和另一个表就可以了. 4)提供某些安全性保证. 视图提供了一种可以控制的方式, 即可以让不同的用户看见不同的列, 而不允许访问那些敏感的列, 这样就可以保证敏感数据不被用户看见. 5)简化用户权限的管理. 可以将视图的权限授予用户, 而不必将基表中某些列的权限授予用户, 这样就简化了用户权限的定义. 1.3 创建视图 权限: 要在当前方案中创建视图, 用户必须具有create view系统权限; 要在其他方案中创建视图, 用户必须具有create any view系统权限. 视图的功能取决于视图拥有者的权限. 语法: create [ or replace ] [ force ] view [schema.]view_name [ (column1,column2,...) ] as select ... [ with check option ] [ constraint constraint_name ] [ with read only ];
1、oracle中查询某个表在那个存储过程中用到了 例如查询t_lea_waybill在那个存储过程中用到了： select https://www.doczj.com/doc/cd13629944.html, 过程名称, min(a.line) 首次出现行数 from user_source a where a.TEXT like'%t_lea_waybill%' group by https://www.doczj.com/doc/cd13629944.html,; 这样当我们不知道某个表在那个存储过程里面用到的时候，我们就不必要把每个存储过程打开在里面搜，直接用这个语句就可以查到，可以提高效率。 2、oracle中查询某个存储过程用到了那些表 select de.referenced_name from user_dependencies de where https://www.doczj.com/doc/cd13629944.html,='BI_OPER_REPORT'and de.referenced_type='TABLE';
3、oracle中查询某个存储过程用到了那些序列 select de.referenced_name from user_dependencies de where https://www.doczj.com/doc/cd13629944.html,='BI_OPER_REPORT'and de.referenced_type='SEQUENCE'; 4、oracle中查询某个字段属于哪个表 select table_name, owner from dba_tab_columns t where t.COLUMN_NAME = upper('MENUNAME');
5、oracle中查询某个表的列数 select count(*) from user_tab_columns a where table_name = upper('ac_menu'); 6、oracle中查询某个表字段的类型 select a.COLUMN_NAME, a.DATA_TYPE from user_tab_columns a where table_name = upper('ac_menu');