oracle_索引

1.查看Oracle当前用户下的信息（用户，表视图，索引，表空间，同义词，存储过程函数，约束条件

0、表空间

　　SQL>select username,default_tablespace from user_users;

　　查看当前用户的角色

　　SQL>select * from user_role_privs;

　　查看当前用户的系统权限和表级权限

　　SQL>select * from user_sys_privs;

　　SQL>select * from user_tab_privs;

　　查看用户下所有的表

　　SQL>select * from user_tables;

　　1、用户

　　查看当前用户的缺省表空间

　　SQL>select username,default_tablespace from user_users;

　　查看当前用户的角色

　　SQL>select * from user_role_privs;

　　查看当前用户的系统权限和表级权限

　　SQL>select * from user_sys_privs;

　　SQL>select * from user_tab_privs;

　　显示当前会话所具有的权限

　　SQL>select * from session_privs;

　　显示指定用户所具有的系统权限

　　SQL>select * from dba_sys_privs where grantee='GAME';

　　2、表

　　查看用户下所有的表

　　SQL>select * from user_tables;

SELECT * FROM ALL_TABLES;

　　查看名称包含log字符的表

　　SQL>select object_name,object_id from user_objects

　　where instr(object_name,'LOG')>0;

　　查看某表的创建时间

　　SQL>select object_name,created from user_objects where object_name=upper('&table_name');

　　查看某表的大小

　　SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments

　　where segment_name=upper('&table_name');

　　查看放在ORACLE的内存区里的表

　　SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;

　　3、索引

　　查看索引个数和类别

　　SQL>select index_name,index_type,table_name from user_indexes order by table_name;

　　查看索引被索引的字段

　　SQL>select * from user_ind_columns where index_name=upper('&index_name');

　　查看索引的大小

　　SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments

　　where segment_name=upper('&index_name');

　　4、序列号

　　查看序列号，last_number是当前值

　　SQL>select * from user_sequences;

　　5、视图

　　查看视图的名称

　　SQL>select view_name from user_views;

　　查看创建视图的select语句

　　SQL>set view_name,text_length from user_views;

　　SQL>set long 2000; 说明：可以根据视图的text_length值设定set long 的大小

　　SQL>select text from user_views where view_name=upper('&view_name');

　　6、同义词

　　查看同义词的名称

　　SQL>select * from user_synonyms;

SELECT * FROM ALL_SYSNONYMS;

　　7、约束条件

　　查看某表的约束条件

　　SQL>select constraint_name, constraint_type,search_condition, r_constraint_name

　　from user_constraints where table_name = upper('&table_name');

　　SQL>select c.constraint_name,c.constraint_type,cc.column_name

　　from user_constraints c,user_cons_columns cc

　　where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')

　　and c.owner = cc.owner and c.constraint_name = cc.constraint_name

　　order by cc.position;

　　8、存储函数和过程

　　查看函数和过程的状态

　　SQL>select object_name,status from user_objects where object_type='FUNCTION';

　　SQL>select object_name,status from user_objects where object_type='PROCEDURE';

　　查看函数和过程的源代码

　　SQL>select text from all_source where owner=user and name=upper('&plsql_name');

 

2.创建索引 CREATE INDEX

CREATE INDEX -- 定义一个新索引
Synopsis
CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [, ...] )
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]
描述
CREATE INDEX 在指定的表上构造一个名为 index_name 的索引。索引主要用来提高数据库性能。但是如果不恰当的使用将导致性能的下降。

索引的键字字段是以字段名的方式声明的，或者是可选的写在一个圆括弧里面的表达式。 如果索引方式支持多个字段索引，那么我们也可以声明多个字段。

一个索引字段可以是一个使用表的行的一个或多个字段的数值进行计算的表达式。 整个特性可用于获取对基本数据某种变形的快速访问。 比如，一个在 upper(col) 上的函数索引将允许子句 WHERE upper(col) = 'JIM' 使用索引。

PostgreSQL 为从索引提供 B-tree，R-tree，hash（散列） 和 GiST 索引方法。 B-tree 索引方法是一个 Lehman-Yao 高并发 B-trees 的实 现。R-tree 索引方法用 Guttman 的二次分裂算法实现了标准的 R-trees。 hash（散列）索引方法是 Litwin 的线性散列的一个实现。 用户也可以定义它们自己的索引方法，但这个工作相当复杂。

如果出现了 WHERE 子句，则创建一个部分索引。 部分索引是一个只包含表的一部分记录的索引，通常是该表中比其它部分数据更有用的部分。 比如，如果你有一个表，里面包含已上账和未上账的定单， 未上账的定单只占表的一小部分而且这部分是最常用的部分， 那么你就可以通过只在这个部分创建一个索引来改善性能。 另外一个可能的用途是用 WHERE 和 UNIQUE 强制一个表的某个子集的唯一性。 参阅 Section 11.7 探讨更多信息。

在 WHERE 子句里用的表达式只能引用下层表的字段，但是它可以使用所有字段，而不仅仅是被索引的字段。 目前，子查询和聚集表达式也不能出现在WHERE里。

索引定义里的所有函数和操作符都必须是immutable，（不变的）也就是说， 它们的结果必须只能依赖于它们的输入参数，而决不能依赖任何外部的影响（比如另外一个表的内容或者当前时间）。 这个约束确保该索引的行为是定义完整的。要在一个索引上使用用户定义函数，请记住在你创建它的时候把它标记为immutable的函数。
参数

UNIQUE 
令系统检测当索引创建时（如果数据已经存在）和每次添加数据时表中是否有重复值。 如果插入或更新的值会导致重复的记录时将生成一个错误。

name 
要创建的索引名。这里不能包含模式名； 索引总是在同一个模式中作为其父表创建的。

table 
要索引的表名（可能有模式修饰）。

method 
用于索引的方法的名字。可选的名字是 btree， hash，rtree，和 gist。缺省方法是 btree。

column 
表的列/字段名。

expression 
一个基于该表的一个或多个字段的表达式。 这个表达式通常必须带着圆括弧包围写出，如语法中显示那样。 不过，如果表达式有函数调用的形式，那么圆括弧可以省略。

opclass 
一个关联的操作符表。参阅下文获取细节。

tablespace 
创建索引所在的表空间。如果没有声明，则使用 default_tablespace 的表空间， 如果 default_tablespace 是空字串，则使用数据库的缺省表空间。

predicate 
为一个部分索引定义约束表达式。
注意
参阅 Chapter 11 获取有关何时使用索引，何时不使用索引， 以及它们哪种情况下是有用的信息。

目前，只有 B-tree 和 gist 索引方法支持多字段索引。 缺省时最多可以声明 32 个键字（这个限制可以在制作 PostgreSQL 时修改）。 目前只有 B-tree 支持唯一索引。

可以为索引的每个列/字段声明一个 操作符表。 操作符表标识将要被该索引用于该列/字段的操作符。 例如， 一个四字节整数的 B-tree 索引将使用 int4_ops 表； 这个操作符表包括四字节整数的比较函数。 实际上，该域的数据类型的缺省操作符表一般就足够了。 某些数据类型有操作符表的原因是，它们可能有多于一个的有意义的顺序。 例如，我们对复数类型排序时有可能以绝对值或者以实部。 我们可以通过为该数据类型定义两个操作符表，然后在建立索引的时候选择合适的表来实现。 有关操作符表更多的信息在 Section 11.8 和 Section 32.14 里。

使用 DROP INDEX 删除一个索引。

缺省时索引不会用于 IS NULL 子句。这种场合下使用索引的最好方法是用 IS NULL 谓词创建一个部分索引。
例子
在表films上的 title字段创建一个 B-tree 索引：

CREATE UNIQUE INDEX title_idx ON films (title);

在表 films 的字段 code 上创建一个索引， 并且让索引存在于表空间 indexspace上：

CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;

兼容性
CREATE INDEX 是 PostgreSQL 语言扩展。 在 SQL92 中没有 CREATE INDEX 命令。
 

 

3.Oracle初学者入门指南-索引的作用

关于索引是什么的最简单的比喻是，索引之于表数据如同目录之于一本书。

 

通过目录的页码我们可以快速的定位一个内容，同样通过索引记录的rowid我们可以快速的定位一条数据。

 

如同目录很难针对书中每个字词一样，索引也很难针对所有字段。

 

我们通常索引最能代表章节，记录属性的内容。

 

索引并非总能带来性能提升，但是通常情况下，索引能加快访问，所以建表的时候，你一定要知道还有索引这样一类对象。

 

下面这个案例是我们绝对不应该和不想看到的。

 

今天一个部门报数据库巨慢无比，上去看了一下，抓到如下的SQL:

SQL> select sql_text

  2  from v$sqltext a

  3  where a.hash_value = (

  4  select sql_hash_value from v$session b

  5  where b.sid='&sid'

  6  )

  7  order by piece asc

  8  /

 

SQL_TEXT

----------------------------------------------------------------

select * from i_cm_power t WHERE T.SJH='13911xxxxx6'

 

检查了一下该查询访问的数据表,居然一个索引都没有:

SQL> select index_name from dba_indexes where table_name=upper('i_cm_power');

 

INDEX_NAME

------------------------------

 

没有索引意味着，即使为了获取这一条记录，Oracle也必须对5.28G的一个表进行全表扫描，如果不慢那就怪了:

SQL> col segment_name for a20

SQL> select segment_name,bytes/1024/1024/1024

     from dba_segments where segment_name=upper('i_cm_power');

 

SEGMENT_NAME         BYTES/1024/1024/1024

-------------------- --------------------

I_CM_POWER                  5.28173828125

 

创建一个索引再说:

SQL> create index idx_i_cm_power_sjh on i_cm_power(sjh);

 

Index created.

 

Elapsed: 00:20:50.73

 

SQL> col segment_name for a20

SQL> select segment_name,bytes/1024/1024 MB

  2  from dba_segments where segment_name=upper('idx_i_cm_power_sjh');

 

SEGMENT_NAME                 MB

-------------------- ----------

IDX_I_CM_POWER_SJH         1360

 

SQL>

 

无疑这个索引对于这样的简单查询是大有益处的:

 

SQL> select * from i_cm_power t WHERE T.SJH='13911xxxxx6';

 

Elapsed: 00:00:00.07

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'I_CM_POWER'

   2    1     INDEX (RANGE SCAN) OF 'IDX_I_CM_POWER_SJH' (NON-UNIQUE)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          6  consistent gets

          0  physical reads

          0  redo size

       1022  bytes sent via SQL*Net to client

        503  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          2  rows processed

 

然而在实际中，你需要考虑更多的因素。

 

增加索引会占用更多的存储空间；索引的维护会增加数据库的负担，如果有海量的数据加载，可能会极大影响性能...

 

所以事实可能总是比你想象的更复杂，你只有知道的更多...