oracle 索引，权限
(2011-02-17 16:09:32) 标签： 分类： Oracle
杂谈
一，索引 介绍ν
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o次数， 从而提高数据访问性能。索引有很多种我们主要介绍常用的几种： 为什么添加了索引后，会加快查询速度呢？
创建索引 单列索引ν
单列索引是基于单个列所建立的索引，比如： create index 索引名 on 表名(列名); 复合索引ν
复合索引是基于两列或是多列的索引。在同一张表上可以 多个索引，但是要求 列的组合必须不同，比如：
create index emp_idx1 on emp (ename, job); create index emp_idx1 on emp (job, ename);
使用原则 使用原则ν
1. 在大表上建立索引才有意义
2. 在 where 子句或是连接条件上经常引用的列上建立索引 3. 索引的层次不要超过 4 层
这里能不能给学生演示这个效果呢？ 如何构建一个大表呢？
索引的缺点
索引缺点分析ν
索引有一些先天不足：
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：表） 常用的 ：
alter 修改 delete 删除 select 查询 insert 添加
update 修改 index 索引 references 引用 execute 执行 ν 显示对象权限
通过数据字段视图可以显示用户或是角色所具有的对象权限。视图为 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 用户要操作 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 权限。
比如为了让 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
