`

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

 

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

 

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

 

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

 

分享到:
评论

相关推荐

    Oracle_Index 索引

    Oracle_Index 索引

    Oracle_索引

    介绍数据库索引知识、索引分类及优缺点、索引原理、索引优化

    高效ORACLE_之_索引(完整).pdf

    在Oracle数据库中,索引是一种关键的数据结构,用于加快数据检索速度,提升数据库性能。本篇文档“高效ORACLE_之_索引(完整).pdf”主要探讨了如何有效地使用和理解Oracle数据库中的各种索引类型,以及何时何地应该...

    《Oracle_全文索引_详细介绍》_40页.pdf

    ### Oracle全文索引详解 #### 一、Oracle全文索引概述 Oracle全文索引是一种特殊类型的索引,它主要用于提高基于文本的查询性能。自从Oracle 7.3版本开始引入了这一特性,允许用户通过Oracle服务器提供的上下文...

    oracle_commond.rar_odbc api_oracle_oracle 客户端_oracle sql_sql

    在"oracle常用命令集.sql"中,可能包含了如DML(INSERT、UPDATE、DELETE)语句,DDL(CREATE、ALTER、DROP)语句,以及查询优化技巧,例如使用索引、子查询、联接操作等。 4. 数据库操作实例 描述中的"oracle常用...

    oracle_resource.rar_oracle_oracle_resource

    "oracle_resource.rar_oracle_oracle_resource"这个主题聚焦于找出Oracle数据库中最消耗系统资源的任务或SQL语句。通过对资源占用进行深入分析,我们可以识别性能瓶颈,进而采取针对性的优化措施。 资源占用查询....

    BBS.rar_ bbs oracle_bbs_bbs oracle_oracle_oracle BBS

    这个项目是针对"bbs oracle_bbs_bbs oracle_oracle_oracle BBS"的,可能是一个用Oracle数据库支持的BBS(Bulletin Board System,电子公告板系统)的实现。描述中提到了"复旦大学",暗示这可能是复旦大学学生或教师...

    Oracle_Xml.rar_Oracle XML _oracle_xml oracle_xml数据库_操作 xml

    3. **XMLIndex**: Oracle提供了XML索引,包括结构索引、路径索引和函数索引,以提高XML数据的查询性能。结构索引基于XML文档的结构,路径索引针对特定路径,函数索引则是基于自定义函数的结果。 4. **XMLQuery和...

    DATADIC.rar_DATADIC_cxGridLevel.d_delphi MySQL oracle_oracle_ora

    描述中提到的"数据字典"是一个重要的概念,它是数据库管理系统中的一个重要组成部分,记录了数据库中所有对象的详细信息,如表结构、字段、索引等。这个特定的"数据字典"能帮助用户在MySQL、Oracle和SQL Server之间...

    Oracle为sdo_geometry创建空间索引

    Oracle 中创建空间索引的实现步骤和注意事项 空间索引是 Oracle 中的一种特殊索引类型,用于加速空间数据的查询和检索。空间索引可以大大提高空间数据的查询效率,特别是在处理大量空间数据时。本文将详细介绍如何...

    odt.rar_odt_oracle_oracle O_oracle ODT

    3. **数据库对象设计**:可以直接在VS中创建和修改数据库表、视图、索引、存储过程等对象,可视化界面使得设计更加直观。 4. **数据访问对象(DAO)生成**:ODT可以自动生成.NET数据访问层代码,帮助快速构建与...

    oracle_note.rar_oracle_oracle Note 341456

    7. **性能优化**:通过索引、分区、查询优化器和统计信息收集,可以提升Oracle数据库的性能。监控和分析SQL语句执行计划也是优化的重要环节。 8. **PL/SQL**:Oracle的编程语言,结合了SQL的功能,用于编写存储过程...

    Oracle_test1.rar_oracle_oracle面试

    3. 索引:索引可以加速查询,Oracle支持B树索引、位图索引和函数索引等多种类型。理解何时使用不同类型的索引以及如何优化索引策略是面试中的常见问题。 4. 视图:视图是虚拟表,基于一个或多个表的查询结果。视图...

    ORACLE_SQL_ALL.rar_oracle_oracle sql_sql优化

    - **索引类型**:B树索引、位图索引、函数索引、唯一索引和非唯一索引等,以及它们在不同场景下的应用。 - **索引选择性**:理解索引的选择性对查询性能的影响,如何选择最佳索引字段。 3. **执行计划分析** - *...

    Analyze_Oracle_Table.rar_Table_analyze orac_analyze orac_oracle

    索引是数据库中用于快速查找记录的一种数据结构,如果Oracle没有正确的索引统计信息,它可能无法正确判断何时应使用索引,导致查询性能下降。因此,定期分析表和索引有助于确保Oracle知道哪些索引是有效的,并且可以...

    oracle_note.rar_ORACLE 入门_oracle_oracle 笔记

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统,尤其在企业级应用中占据重要地位。这份"Oracle_note.rar"压缩包包含的"数据库开发Oracle学习笔记"是为初学者准备的一份详细教程,旨在帮助读者快速掌握...

    oracle_cj.doc

    本文将深入探讨Oracle数据库的一些核心概念和技术,包括SQL查询、数据库对象创建、事务处理、索引和视图,以及一些实用的SQLPlus命令。 1. SQL查询: SQL(Structured Query Language)是用于管理和操作关系数据库...

Global site tag (gtag.js) - Google Analytics