Oracle 索引、权限

索引介绍.索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数，从

而提高数据访问性能。索引有很多种我们主要介绍常用的几种：

为什么添加了索引后，会加快查询速度呢？

创建索引

单列索引是基于单个列所建立的索引，比如： create index 索引名 on 表名(列名);

复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列

的组合必须不同，比如： create index emp_idx1 on emp(ename, job);

create index emp_idx1 on emp(job, ename);

使用原则.

1. 在大表上建立索引才有意义

2. 索引的层次不要超过 4 层

3. 在 where 子句或是连接条件上经常引用的列上建立索引

索引有一些先天不足：

1. 建立索引，系统要占用大约为表 1.2 倍的硬盘和内存空间来保存索引。

2. 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据

和索引的一致性。

实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在

进行插入、修改和删除操作时比没有索引花费更多的系统时间。

比如在如下字段建立索引应该是不恰当的：

1. 很少或从不引用的字段； 2. 逻辑型的字段，如男或女（是或否）等。

综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，

这是考验一个 DBA 是否优秀的很重要的指标。

其它索引

介绍.按照数据存储方式，可以分为 B*树、反向索引、位图索引；

按照索引列的个数分类，可以分为单列索引、复合索引；

按照索引列值的唯一性，可以分为唯一索引和非唯一索引。

此外还有函数索引，全局索引，分区索引...对于索引我还要说：

在不同的情况，我们会在不同的列上建立索引，甚至建立不同种类的索引，请记住，

技术是死的，人是活的。比如：

B* 树索引建立在重复值很少的列上，而位图索引则建立在重复值很多、不同值相对

固定的列上。

显示索引信息

.显示表的所有索引

在同一张表上可以有多个索引，通过查询数据字典视图 dba_indexs 和 user_indexs，

可以显示索引信息。其中 dba_indexs 用于显示数据库所有的索引信息，而user_indexs

用于显示当前用户的索引信息： select index_name, index_type from user_indexes

where table_name = '表名';

显示索引列.通过查询数据字典视图 user_ind_columns, 可以显示索引对应的列的信息

select table_name, column_name from user_ind_columns where index_name =

'IND_ENAME';

. 你也可以通过 pl/sql developer 工具查看索引信息

 

管理权限和角色
这一部分我们主要看看 oracle 中如何管理权限和角色，权限和角色的区别在那里。
当刚刚建立用户时，用户没有任何权限，也不能执行任何操作。如果要执行某种特
定的数据库操作，则必须为其授予系统的权限；如果用户要访问其它方案的对象，
则必须为其授予对象的权限。为了简化权限的管理，可以使用角色。

权限是指执行特定类型 sql 命令或是访问其它方案对象的权利，包括系统权限和对
象权限两种。
系统权限是指执行特定类型 sql 命令的权利。它用于控制用户可以执行的一个或是
一组数据库操作。比如当用户具有 create table 权限时，可以在其方案中建表，当用
户具有 create any table 权限时，可以在任何方案中建表。oracle 提供了 100 多种系
统权限。
常用的有：
create session 连接数据库
create table 建表
create view 建视图
create public synonym 建同义词
create procedure 建过程、函数、包
create trigger 建触发器
create cluster 建簇
显示系统权限.oracle 提供了 100 多种系统权限，而且 oracle 的版本越高，提供的系统权限就越多，
我们可以询数据字典视图 system_privilege_map，可以显示所有系统权限。
select* from system_privilege_map order by name;
授予系统权限.一般情况，授予系统权限是由 dba 完成的，如果用其他用户来授予系统权限，则
要求该用户必须具有 grant any privilege 的系统权限。在授予系统权限时，可以带有
with admin option 选项，这样，被授予权限的用户或是角色还可以将该系统权限授
予其它的用户或是角色。为了让大家快速理解，我们举例说明：
1.创建两个用户 ken，tom。初始阶段他们没有任何权限，如果登录就会给出错误的
信息。
create user ken identfied by ken;
2 给用户 ken 授权
1). grant create session, create table to ken with admin option;
2). grant create view to ken;
3 给用户 tom 授权
我们可以通过 ken 给 tom 授权，因为 with admin option 是加上的。当然也可以通过
dba 给 tom 授权，我们就用 ken 给 tom 授权：
1. grant create session, create table to tom;
2. grant create view to ken; --ok 吗？不 ok
回收系统权限.一般情况下，回收系统权限是 dba 来完成的，如果其它的用户来回收系统权限，
要求该用户必须具有相应系统权限及转授系统权限的选项（with admin option）。
回收系统权限使用 revoke 来完成。
当回收了系统权限后，用户就不能执行相应的操作了，但是请注意，系统权限级联
收回的问题?[不是级联回收！]
system --------->ken ---------->tom
(create session)(create session) (create session)

用 system 执行如下操作：
revoke create session from ken; --请思考 tom 还能登录吗？
答案：能，可以登录
对象权限
指访问其它方案对象的权利，用户可以直接访问自己方案的对象，但是如果要访问
别的方案的对象，则必须具有对象的权限。
比如 smith 用户要访问 scott.emp 表（scott：方案，emp：表）
常用的有： reference 引用
alter 修改
delete 删除
select 查询
insert 添加
update 修改
index 索引
execut 执行
.显示对象权限
通过数据字段视图可以显示用户或是角色所具有的对象权限。视图为 dba_tab_privs
SQL> conn system/manager;
SQL> select distinct privilege from dba_tab_privs;
SQL> select grantor, owner, table_name, privilege from dba_tab_privs where grantee =
'BLAKE';
1.授予对象权限
在 oracle9i 前，授予对象权限是由对象的所有者来完成的，如果用其它的用户来操
作，则需要用户具有相应的（with grant option）权限，从 oracle9i 开始，dba 用户
（sys，system）可以将任何对象上的对象权限授予其它用户。授予对象权限是用 grant
命令来完成的。
对象权限可以授予用户，角色，和 public。在授予权限时，如果带有 with grant option
选项，则可以将该权限转授给其它用户。但是要注意 with grant option 选项不能被
授予角色。
1.monkey 1.monkey 1.monkey 1.monkey 用户要操作 scott.emp scott.emp scott.emp scott.emp 表，则必须授予相应的对象权限
1). 希望 monkey 可以查询 scott.emp 表的数据，怎样操作？
grant select on emp to monkey;
2). 希望 monkey 可以修改 scott.emp 的表数据，怎样操作？
grant update on emp to monkey;
3). 希望 monkey 可以删除 scott.emp 的表数据，怎样操作？
grant delete on emp to monkey;
4). 有没有更加简单的方法，一次把所有权限赋给 monkey？
grant all on emp to monkey;
2.能否对 monkey 访问权限更加精细控制。（授予列权限）
1). 希望 monkey 只可以修改 scott.emp 的表的 sal 字段，怎样操作？
grant update on emp(sal) to monkey
2).希望 monkey 只可以查询 scott.emp 的表的 ename，sal 数据，怎样操作？
grant select on emp(ename, sal) to monkey

3.授予 alter 权限
如果 black 用户要修改 scott.emp 表的结构，则必须授予 alter 对象权限
SQL> conn scott/tiger
SQL> grant alter on emp to blake;
当然也可以用 system，sys 来完成这件事。
4.授予 execute execute execute execute 权限
如果用户想要执行其它方案的包/过程/函数，则须有 execute 权限。
比如为了让 ken 可以执行包 dbms_transaction，可以授予 execute 权限。
SQL> conn system/manager
SQL> grant execute on dbms_transaction to ken;
5.授予 index 权限
如果想在别的方案的表上建立索引，则必须具有 index 对象权限。
如果为了让 black 可以在 scott.emp 表上建立索引，就给其 index 的对象权限
SQL> conn scott/tiger
SQL> grant index on scott.emp to blake;
6.使用 with grant option 选项
该选项用于转授对象权限。但是该选项只能被授予用户，而不能授予角色
SQL> conn scott/tiger;
SQL> grant select on emp to blake with grant option;
SQL> conn black/shunping
SQL> grant select on scott.emp to jones;
回收对象权限.在 oracle9i 中，收回对象的权限可以由对象的所有者来完成，也可以用 dba 用户（sys，
system）来完成。
这里要说明的是：收回对象权限后，用户就不能执行相应的 sql 命令，但是要注意
的是对象的权限是否会被级联收回？【级联回收】
如：scott------------->blake-------------->jones
select on emp select on emp select on emp
SQL> conn scott/tiger @accp
SQL> revoke select on emp from blake
请大家思考，jones 能否查询 scott.emp 表数据。
答案：查不了了（和系统权限不一样，刚好相反）