索引,索引的建立、修改、删除
索引
索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理直接有关。下面给出建立索引的方法和要点。
§3.5.1 建立索引
1. CREATE INDEX命令语法:
CREATE INDEX
CREATE [unique] INDEX [user.]index
ON [user.]table (column [ASC | DESC] [,column
[ASC | DESC] ] ... )
[CLUSTER [scheam.]cluster]
[INITRANS n]
[MAXTRANS n]
[PCTFREE n]
[STORAGE storage]
[TABLESPACE tablespace]
[NO SORT]
Advanced
其中:
schema ORACLE模式,缺省即为当前帐户
index 索引名
table 创建索引的基表名
column 基表中的列名,一个索引最多有16列,long列、long raw
列不能建索引列
DESC、ASC 缺省为ASC即升序排序
CLUSTER 指定一个聚簇(Hash cluster不能建索引)
INITRANS、MAXTRANS 指定初始和最大事务入口数
Tablespace 表空间名
STORAGE 存储参数,同create table 中的storage.
PCTFREE 索引数据块空闲空间的百分比(不能指定pctused)
NOSORT 不(能)排序(存储时就已按升序,所以指出不再排序)
2.建立索引的目的:
建立索引的目的是:
l 提高对表的查询速度;
l 对表有关列的取值进行检查。
但是,对表进行insert,update,delete处理时,由于要表的存放位置记录到索引项中而会降低一些速度。
注意:一个基表不能建太多的索引;
空值不能被索引
只有唯一索引才真正提高速度,一般的索引只能提高30%左右。
Create index ename_in on emp (ename,sal);
例1:商场的商品库表结构如下,我们为该表的商品代码建立一唯一索引,使得在前台POS收款时提高查询速度。
Create table good(good_id number(8) not null,/* 商品条码 */
Good_desc varchar2(40), /* 商品描述 */
Unit_cost number(10,2) /* 单价 */
Good_unit varchar2(6), /* 单位 */
Unit_pric number(10,2) /* 零售价 */
);
注:提高查询速度的方法还有在表上建立主键,主键与唯一索引的差别
在于唯一索引可以空,主键为非空,比如:
Create table good(good_id number(8) primary key,
Good_desc Varchar2(40),
Unit_cost number(10,2),
Good_unit char(6),
Unit_pric number(10,2)
);
§3.5.2 修改索引
对于较早的Oracle版本,修改索引的主要任务是修改已存在索引的存储参数适应增长的需要或者重新建立索引。而Oracle8I及以后的版本,可以对无用的空间进行合并。这些的工作主要是由管理员来完成。
简要语法结构如下,更详细的语法图见电子文档《Oracle8i Reference 》 中的 Alter index.
ALTER [UNIQUE] INDEX [user.]index
[INITRANS n]
[MAXTRANS n]
REBUILD
[STORAGE n]
其中:
REBUILD 是 根据原来的索引结构重新建立索引,实际是删除原来的索引后再重新建立。
提示:DBA经常用 REBUILD 来重建索引可以减少硬盘碎片和提高应用系统的性能。
例:
alter index pk_detno rebuild storage(initial 1m next 512k);
ALTER INDEX emp_ix REBUILD REVERSE;
Oracle8i 的新功能可以对索引的无用空间进行合并,它由下面命令完成:
ALTER INDEX . . . COALESCE;
例如:
ALTER INDEX ename_idx COALESCE;
§3.5.3 删除索引
当不需要时可以将索引删除以释放出硬盘空间。命令如下:
DROP INDEX [schema.]indexname
例如:
sql> drop index pk_dept;
注:当表结构被删除时,有其相关的所有索引也随之被删除。
§3.6 新索引类型
Oracle8i为了性能优化而提供新的创建新类型的索引。这些新索引在下面介绍:
§3.6.1 基于函数的索引
基于函数的索引就是存储预先计算好的函数或表达式值的索引。这些表达式可以是算术运算表达式、SQL或 PL/SQL函数、C调用等。值得注意的是,一般用户要创建函数索引,必须具有GLOBAL QUERY REWRITE和CREATE ANY INDEX权限。否则不能创建函数索引,看下面例子:
例1:为EMP表的ename 列建立大写转换函数的索引idx :
CREATE INDEX idx ON emp ( UPPER(ename));
这样就可以在查询语句来使用:
SELECT * FROM EMP WHERE UPPER(ename) LIKE ‘JOH%’;
例2:为emp 的工资和奖金之和建立索引:
1) 查看emp 的表结构:
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
2)没有授权就创建函数索引的提示:
SQL> create index sal_comm on emp ( (sal+comm)*12, sal,comm)
2 tablespace users storage(initial 64k next 64k pctincrease 0);
create index sal_comm on emp ( (sal+comm)*12, sal,comm)
*
ERROR at line 1:
ORA-01031: insufficient privileges
3) 连接到DBA帐户并授权:
SQL> connect sys/sys@ora816
Connected.
SQL> grant GLOBAL QUERY REWRITE to scott;
Grant succeeded.
SQL> grant CREATE ANY INDEX to scott;
Grant succeeded.
4)在连接到scott帐户,创建基于函数的索引:
SQL> connect scott/tiger@ora816
Connected.
SQL> create index sal_comm on emp ( (sal+comm)*12, sal,comm)
2 tablespace users storage(initial 64k next 64k pctincrease 0);
Index created.
1)在查询中使用函数索引:
SQL> select ename,sal,comm from emp where (sal+comm)*12 >5000;
ENAME SAL COMM
---------------------- ---------------- ----------------
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
TURNER 1500 0
赵元杰 1234.5 54321
§3.6.2 反向键索引
反向键索引通过反向键保持索引的所有叶子键上的插入分布。有时,可用反向键索引来避免不平衡的索引。对于反向键索引可以进行下面操作:
l 通过在ALTER INDEX命令后加REBUILD NOREVERSE或REBUILD REVERSE子句来使索引边为反向键索引或普通索引;
l 采用范围扫描的查询不能使用反向键索引;
l 位图索引不能反向;
l 索引编排表不能反向。
例1:创建一个反向键索引:
CREATE INDEX i ON t (a,b,c) REVERSE;
例2:使一个索引变为反向键索引:
ALTER INDEX i REBUILD NOREVERSE;
§3.6.3 索引组织表
与普通的索引不一样,索引组织表(Index_Organized Table)是根据表来存储数据,即将索引和表存储在一起。这样的索引结构表(Index_organized table—IOT)的特点是:对表数据的改变,如插入一新行、删除某行都引起索引的更新。
索引组织表就象带一个或多个列所有的普通表一样,但索引组织表在B-树索引结构的叶节点上存储行数据。通过在索引结构中存储数据,索引组织表减少了总的存储量,此外,索引组织表也改善访问性能。
由于表中的行与B_树索引存放在一起,每个行都没有ROWID,而是用主键来标识。但是Oracle会“猜”这些行的位置并为每个行分配逻辑的ROWID。此外,你可以为这样的表建立第二个索引。
创建索引结构表也是用CREATE TABLE 命令加ORGANIZATION INDEX关键字来实现。但是,这样的表在创建完后,你还必须为该表建立一个主键。
例子:
CREATE TABLE IOT_EXPAMPLE
(
Pk_col1 number(4),
Pk_col2 varchar2(10),
Non_pk_col1 varchar2(40),
Non_pk_col2 date,
CONSTRAINT pk_iot PRIMARY KEY
( pk_col1, pk_col2)
)
ORGANIZATION INDEX
TABLESPACE INDEX
STORAGE( INITIAL 1M NEXT 512K PCTINCREASE 0 );
索引组织表有些限制:
l 不能使用唯一约束;
l 必须具有一个主键;
l 不能建立簇;
l 不能包含LONG类型列;
l 不支持分布和复制。
提示:如果建立了索引组织表,则会在DBA_TABLES中的IOT_TYPE和IOT_NAME列上记录有索引组织表的信息。
例1.修改索引结构表 docindex 的索引段的INITRANS参数:
ALTER TABLE docindex INITRANS 4;
例2.下面语句加一个的溢出数据段到索引组织表 docindex中:
ALTER TABLE docindex ADD OVERFLOW;
例3.下面语句为索引组织表 docindex的溢出数据段修改INITRANS参数:
ALTER TABLE docindex OVERFLOW INITRANS 4;
============================================================================================================
适当的使用索引可以提高数据检索速度,可以给经常需要进行查询的字段创建索引
oracle的索引分为5种:唯一索引,组合索引,反向键索引,位图索引,基于函数的索引
创建索引的标准语法:
CREATE INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空间名;
创建唯一索引:
CREATE unique INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空间名;
创建组合索引:
CREATE INDEX 索引名 ON 表名 (列名1,列名2)
TABLESPACE 表空间名;
创建反向键索引:
CREATE INDEX 索引名 ON 表名 (列名) reverse
TABLESPACE 表空间名;
查看文章
oracle 查看索引类别以及查看索引字段被引用的字段方法2008年01月04日 星期五 13:20查看索引个数和类别
select * from user_indexes where table='表名' ;
查看索引被索引的字段
SQL>select * from user_ind_columns where index_name=upper('&index_name');
PS:
查看某表的约束条件
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;
查看视图的名称
SQL>select view_name from user_views;
相关推荐
oracle10g增删改查 试图 索引 存储过程及游标的使用
MySQL 是一款广受欢迎的开源关系型数据库管理系统(RDBMS),由瑞典MySQL AB公司开发,现隶属于美国甲骨文公司(Oracle)。自1998年首次发布以来,MySQL以其卓越的性能、可靠性和可扩展性,成为全球范围内Web应用...
oracle语句整理 常用语句 建表 增删改查 建索引 游标的简单用法 函数调用
大概了解 Oracle 索引底层的数据结构,从而更好地理解 Oracle 索引对增、删、改、查的性能。B-树(B-tree) 非索引的结构能满足所有需要,但自平衡的 B-树索引结构更能优化在大数据集上检索的性能。每个 B-树节点...
内容包括:数据库系统基本概念 ,工具,SQL语言,数据查询(包括链接查询和分组统计查询)、数据操作(增删改查)、PL/SQL高级编程(视图,函数,存储过程,序列,索引,触发器,游标),最后介绍了数据库的备份和...
此笔记为个人通过自学所整理的oracle笔记,希望看到的人有什么建议告诉我,或者共同...里面内容主要包括:oracle增删改查、用户管理、索引、视图、PLSQL、存储过程、函数、游标、触发器等等!每个知识点都有案例讲解!
文档中展示了如何对海量数据进行增删改查,另外详细介绍了Oracle数据库的索引以及Oracle数据库的锁机制,附件包含.xmind文件,需要安装xmind思维导图软件
* 作增、删、改、查的操作,并将其封装到存储过程中 * 写一个 Java 程序来调用存储过程 2. FUNCTION、PROCEDURE 和 PACKAGE 的区别是什么? 答:FUNCTION 和 PROCEDURE 是 PL/SQL 代码的集合,通常用于完成一个...
增删改查使用hibernate实现。 一开始一直以为是hibernate的内部处理上有关联操作导致的删除和更新数据缓慢。所以将原先使用hibernate的saveOrupdate方法,改查jdbc的 sql语句来处理update和delete数据操作。但是依然...
设计环境 1.Windows7旗舰版32位 2.Microsoft Visual Studio 2005 3.Oracle 11g 第2章 概要设计 2.1系统需求分析 图书管理系统主要是用oracle数据库进行逻辑处理,实现对图书信息的增删改查,以 及出库入库的管理。...
2)我想把emp所有的(增,删,改,查/insert,delete,update,select这四个一起可以用all代替) 操作权限赋给leng grant all on emp to leng;(此时登陆用户为scott) 案例2:这时我想把权限收回来怎么办呢, 这时用...
①:增/删/改 ②:约束 ③:一些查询 五.锁,表分区 ①:锁的概念 ②:并发问题 ③:锁机制 ④:锁的类型 ②:常见分区 六.数据库对象管理 ①:同义词 ②:序列 ③:视图 ④:索引 七.PL/SQL基础 ①:什么是PL/SQL ...
数据库知识点整理,包括数据库和表的创建,增删改查,子查询,关联查询,连接,交并差集,索引,序列,视图,各种函数,DDL、DML、DQL的实例。 每个知识点都有基于ORACLE scott 账户下employee 表的实例代码。
数据库知识点整理,包括数据库和表的创建,增删改查,子查询,关联查询,连接,交并差集,索引,序列,视图,各种函数,DDL、DML、DQL的实例。 存储过程,触发器,PL/SQL的基本使用。 每个知识点都有基于ORACLE ...
重点知识点:基本的增删改查、SQL命令,索引、存储过程、JDBC。 主流的数据库有MySQL、Oracle、SQL Server等等,你只需要搞定一个就可以了,知识都是相通的,一通百通。目前公司里用到MySQL的比较多,所以建议大家...
2.其中包含增删改查 3.from+size分页和Scroll分页 4.高亮显示示例 5.动态创建索引,包含中文分词字段创建(ik中文分词需要提前安装) 6.注意其中使用了oracle库同步数据,可以把数据库相关代码注释掉
概述:存储数据的处所,用户可对数据进行增删改查等操作,用一定方式存储数据,可用户共享,即一个数据库多张表的结构。 数据库管理系统(Database Management System,DBMS) 概述:操作和管理数据库的软件,用于...
仅实现了基本增删改查功能。另一个是使用Tkinter实现的简单计算器 MySQL 是一款广受欢迎的开源关系型数据库管理系统(RDBMS),由瑞典MySQL AB公司开发,现隶属于美国甲骨文公司(Oracle)。自1998年首次发布以来,...
提供10版本的plsqldeveloper客户端, 内包含破解码及客户端安装包, plsql可用于oracle数据库并进行数据的增、删、改、查、建索引、建视图等操作,方便了开发人员使用oracle数据库。