在Oracle中合理创建数据库的索引

在Oracle数据库中 创建索引虽然比较简单 但是要合理的创建索引则比较困难了 笔者认为 在创建索引时要做到三个适当 即在适当的表上 适当的列上创建适当数量的索引 虽然这可以通过一句话来概括优化的索引的基本准则 但是要做到这一点的话 需要数据库管理员做出很大的努力 具体的来说 要做到这个三个适当有如下几个要求

一 根据表的大小来创建索引

虽然给表创建索引 可以提高查询的效率 但是数据库管理员需要注意的是 索引也需要一定的开销的 为此并不是说给所有的表都创建索引 那么就可以提高数据库的性能 这个认识是错误的 恰恰相反 如果不管三七二十一 给所有的表都创建了索引 那么其反而会给数据库的性能造成负面的影响 因为此时滥用索引的开销可能已经远远大于由此带来的性能方面的收益 所以笔者认为 数据库管理员首先需要做到 为合适的表来建立索引 而不是为所有的表建立索引

一般来说 不需要为比较小的表创建索引 如在一个ERP系统的数据库中 department表用来存储企业部门的信息 一般企业的部分也就十几个 最多不会超过一百个 这 条记录对于人来说 可能算是比较多了 但是对于计算机来说 这给他塞塞牙缝都还不够 所以 对类似的小表没有必要建立索引 因为即使建立了索引 其性能也不会得到很大的改善 相反索引建立的开销 如维护成本等等 要比这个要大 也就是说 付出的要比得到的多 显然违反常理

另外 就是对于超大的表 也不一定要建立索引 有些表虽然比较大 记录数量非常的多 但是此时为这个表建立索引并一定的合适 如系统中有一张表 其主要用来保存数据库中的一些变更信息 往往这些信息只给数据库管理员使用 此时为这张表建立索引的话 反而不合适 因为这张表很少用到 只有在出问题的时候才需要查看 其次其即使查看 需要查询的纪录也不会很多 可能就是最近一周的更新记录等等 对于对于一些超大的表 建立索引有时候往往不能够达到预计的效果 而且在打表上建立索引 其索引的开销要比普通的表大的多 那么到底是否给大表建立索引呢笔者认为 主要是看两个方面的内容 首先是需要关注一下 在这张大表中经常需要查询的记录数量 一般来说 如果经常需要查询的数据不超过 %到 %的话 那就没有必要为其建立索引的必要 因为此时建立索引的开销可能要比性能的改善大的多 这个比例只是一个经验的数据 如果数据库管理员需要得出一个比较精确的结论 那么就需要进行测试分析 即数据库管理员需要测试一下全表扫描的时间 看看其是否比建立索引后的查询时间要长或者短 如果是长的话 则说明有建立索引的必要 但是如果没有的话 则说明还是全表扫描速度来的快 此时也就没有必要建立索引了

总之 在考虑是否该为表建立索引时 一般来说小表没有建立索引的必要 而对于打表的话 则需要进行实际情况实际分析 简单一点的 可以根据大致的比率来确定 如果要精确一点的 则可以进行全表扫描性能分析 以判断建立索引后是否真的如预期那样改善了数据库性能

二 根据列的特征来创建索引

列的特点不同 索引创建的效果也不同 数据库管理员需要了解为哪些列创建索引可以起到事倍功半的效果 同时也需要了解为哪些列创建索引反而起到的是事倍功半的效果 这有利于他们了解到底给为怎么样的字段建立索引

根据笔者的经验 往往为如下特征的列创建索引能够起到比较明显的效果 如对于一些重复内容比较少的列 特别是对于那些定义了唯一约束的列 在这些列上建立索引 往往可以起到非常不错的效果 如对于一些null值的列与非Null值的列混合情况下 如果用户需要经常查询所有的非Null值记录的列 则最好为其设置索引 如果经常需要多表连接查询 在用与连接的列上设置索引可以达到事半功倍的效果

可见 索引设置的是否恰当 不仅跟数据库设计架构有关 而且还跟企业的经济业务相关 为此 对于一些套装软件 虽然一开始数据库管理员已经做了索引的优化工作 但是随着后来经济数据的增加 这个索引的效果会越来越打折扣 这主要是因为记录的表化影响到了索引优化的效果 所以笔者建议各位数据库管理员 即使采用的是大牌软件公司的套装软件 也需要隔一段时间 如一年 对数据库的索引进行优化 该去掉的去掉 该调整的调整 以提高数据库的性能

如在数据库中有一张表是用来保存用户信息的 其中有个字段身份z号码 这是一个唯一的字段 在数据库设计时 给这个字段创建了索引 但是当这个数据库投入使用之后 用户不怎么输入用户的身份z号码 而且平时也基本不按这个号码来进行查询 当记录月来月多时 这个身份z号码上的索引字段不但不能够改善数据库的查询性能 反而成了鸡肋 对于这些有很多NULL值的列 而且不会经常查询所有的非NULL值记录的列 数据库管理员要下决心 即使清除这些列上的索引

所以说索引的优化与调整是一个动态的过程 并不是说数据库设计好之后就不需要经过调整 数据库管理员往往需要根据记录的变化情况 来进行适当的变更 以提高索引的效果

三 在一个表上创建多少索引合适

虽然说 在表上创建索引的数量没有限制 但是决不是越多越好 也就是说 在创建索引这项事情上 + 〉 往往不成立 有时候 创建索引越多 其可能会得到适得其反的效果 那么在一个表上 到底给创建多少索引合适呢这个没有一个明确的标准 而是需要数据库管理员根据实际的用途以及数据库中记录的情况 来进行判断

通常来说 表的索引越多 其查询的速度也就越快 但是 表的更新速度则会降低 这主要是因为表的更新(如往表中插入一条记录)速度 反而随着索引的增加而增加 这主要是因为 在更新记录的同时需要更新相关的索引信息 为此 到底在表中创建多少索引合适 就需要在这个更新速度与查询速度之间取得一个均衡点 如对于一些数据仓库或者决策型数据库系统 其主要用来进行查询 相关的记录往往是在数据库初始化的时候倒入 此时 设置的索引多一点 可以提高数据库的查询性能 同时因为记录不怎么更新 所以索引比较多的情况下 也不会影响到更新的速度 即使在起初的时候需要导入大量的数据 此时也可以先将索引禁用掉 等到数据导入完毕后 再启用索引 可以通过这种方式来减少索引对数据更新的影响 相反 如果那些表中经常需要更新记录 如一些事务型的应用系统 数据更新 *** 作是家常便饭的事情 此时如果在一张表中建立过多的索引 则会影响到更新的速度 由于更新 *** 作比较频繁 所以对其的负面影响 要比查询效率提升要大的多 此时就需要限制索引的数量 只在一些必要的字段上建立索引

笔者在平时数据库优化时 往往会根据这些表的用途来为列设置索引 可以查询相关的动态视图 看看对于这张表的 *** 作 是更新 *** 作(包括更新 删除 插入等等)占的比例大 还是查询 *** 作占的比例大 当过多的索引已经影响到更新 *** 作的速度时 则数据库管理员就需要先禁用某些索引 以提高数据库的性能

lishixinzhi/Article/program/Oracle/201311/18407

你好！

如果一个sql 能走单个分区

在走分区内的索引就能快一些~

非分区索引的话，就是整个表的，查询的SQL 不走分区裁剪

那样走整个索引是快一些的~

祝你好运

望采纳~

1打开Navicat，如下图所示。

2右击oracle数据库，然后单击[打开连接]。

3单击[other]，然后单击[index]，显示oracle数据库中的所有索引。

4点击“新建索引”进入索引设计界面。

5设置类型、表类型、表名、列名等。

6在advanced选项卡中，设置表空间和记录等选项。

7点击“保存”，输入索引名，然后点击“确定”。

不使用Oracle text功能,也有很多方法可以在Oracle数据库中搜索文本可以使用标准的INSTR函数和LIKE *** 作符实现。

SELECT FROM mytext WHERE INSTR (thetext, 'Oracle') > 0;

SELECT FROM mytext WHERE thetext LIKE '%Oracle%';

有很多时候，使用instr和like是很理想的, 特别是搜索仅跨越很小的表的时候然而通过这些文本定位的方法将导致全表扫描,对资源来说消耗比较昂贵,而且实现的搜索功能也非常有限，因此对海量的文本数据进行搜索时，建议使用oralce提供的全文检索功能 建立全文检索的步骤步骤一 检查和设置数据库角色首先检查数据库中是否有CTXSYS用户和CTXAPP脚色。如果没有这个用户和角色，意味着你的数据库创建时未安装intermedia功能。你必须修改数据库以安装这项功能。 默认安装情况下，ctxsys用户是被锁定的，因此要先启用ctxsys的用户。 步骤二 赋权 在ctxsys用户下把ctx_ddl的执行权限赋于要使用全文索引的用户，例：

grant execute on ctx_ddl to pomoho;

步骤三 设置词法分析器(lexer)

Oracle实现全文检索，其机制其实很简单。即通过Oracle专利的词法分析器(lexer),将文章中所有的表意单元(Oracle 称为 term)找出来，记录在一组 以dr$开头的表中，同时记下该term出现的位置、次数、hash 值等信息。检索时，Oracle 从这组表中查找相应的term，并计算其出现频率，根据某个算法来计算每个文档的得分(score),即所谓的‘匹配率’。而lexer则是该机制的核心，它决定了全文检索的效率。Oracle 针对不同的语言提供了不同的 lexer, 而我们通常能用到其中的三个：

n basic_lexer: 针对英语。它能根据空格和标点来将英语单词从句子中分离，还能自动将一些出现频率过高已经失去检索意义的单词作为‘垃圾’处理，如if , is 等，具有较高的处理效率。但该lexer应用于汉语则有很多问题，由于它只认空格和标点，而汉语的一句话中通常不会有空格，因此，它会把整句话作为一个 term,事实上失去检索能力。以‘中国人民站起来了’这句话为例，basic_lexer 分析的结果只有一个term ,就是‘中国人民站起来了’。此时若检索‘中国’，将检索不到内容。

n chinese_vgram_lexer: 专门的汉语分析器，支持所有汉字字符集(ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8 )。该分析器按字为单元来分析汉语句子。‘中国人民站起来了’这句话，会被它分析成如下几个term: ‘中’，‘中国’，‘国人’，‘人民’，‘民站’，‘站起’，起来’，‘来了’，‘了’。可以看出，这种分析方法，实现算法很简单，并且能实现‘一网打尽’，但效率则是差强人意。

n chinese_lexer: 这是一个新的汉语分析器，只支持utf8字符集。上面已经看到，chinese vgram lexer这个分析器由于不认识常用的汉语词汇，因此分析的单元非常机械，像上面的‘民站’，‘站起’在汉语中根本不会单独出现，因此这种term是没有意义的，反而影响效率。chinese_lexer的最大改进就是该分析器 能认识大部分常用汉语词汇，因此能更有效率地分析句子，像以上两个愚蠢的单元将不会再出现，极大 提高了效率。但是它只支持 utf8, 如果你的数据库是zhs16gbk字符集，则只能使用笨笨的那个Chinese vgram lexer

如果不做任何设置，Oracle 缺省使用basic_lexer这个分析器。要指定使用哪一个lexer, 可以这样 *** 作：

第一． 当前用户下下建立一个preference(例：在pomoho用户下执行以下语句)

exec ctx_ddlcreate_preference ('my_lexer', 'chinese_vgram_lexer');

第二． 在建立全文索引索引时，指明所用的lexer:

CREATE INDEX myindex ON mytable(mycolumn) indextype is ctxsyscontext

parameters('lexer my_lexer');

这样建立的全文检索索引，就会使用chinese_vgram_lexer作为分析器。

步骤四 建立索引

通过以下语法建立全文索引

CREATE INDEX [schema]index on [schema]table(column) INDEXTYPE IS ctxsyscontext [ONLINE]

LOCAL [(PARTITION [partition] [PARAMETERS('paramstring')]

[, PARTITION [partition] [PARAMETERS('paramstring')]])]

[PARAMETERS(paramstring)] [PARALLEL n] [UNUSABLE];

例：

CREATE INDEX ctx_idx_menuname ON pubmenu(menuname)

indextype is ctxsyscontext parameters('lexer my_lexer')

步骤五 使用索引

使用全文索引很简单，可以通过：

select from pubmenu where contains(menuname,'上传')>0

全文索引的种类

建立的Oracle Text索引被称为域索引(domain index)，包括4种索引类型：

l CONTEXT

2 CTXCAT

3 CTXRULE

4 CTXXPATH

依据你的应用程序和文本数据类型你可以任意选择一种。

对多字段建立全文索引

很多时候需要从多个文本字段中查询满足条件的记录，这时就需要建立针对多个字段的全文索引，例如需要从pmhsubjects(专题表)的 subjectname(专题名称)和briefintro(简介)上进行全文检索，则需要按以下步骤进行 *** 作：

Ø 建议多字段索引的preference

以ctxsys登录，并执行：

EXEC ctx_ddlcreate_preference(' ctx_idx_subject_pref',

'MULTI_COLUMN_DATASTORE');

Ø 建立preference对应的字段值(以ctxsys登录)

EXEC ctx_ddlset_attribute(' ctx_idx_subject_pref ','columns','subjectname,briefintro');

Ø 建立全文索引

CREATE INDEX ctx_idx_subject ON pmhsubjects(subjectname)

INDEXTYPE ISctxsysCONTEXT PARAMETERS('DATASTORE ctxsysctx_idx_subject_pref lexer my_lexer')

Ø 使用索引

select from pmhsubjects where contains(subjectname,'李宇春')>0

全文索引的维护

对于CTXSYSCONTEXT索引，当应用程序对基表进行DML *** 作后，对基表的索引维护是必须的。索引维护包括索引同步和索引优化。

在索引建好后，我们可以在该用户下查到Oracle自动产生了以下几个表：(假设索引名为myindex)：

DR$myindex$I、DR$myindex$K、DR$myindex$R、DR$myindex$N其中以I表最重要，可以查询一下该表，看看有什么内容：

SELECT token_text, token_count FROM dr$i_rsk1$I WHERE ROWNUM <= 20;

这里就不列出查询接过了。可以看到，该表中保存的其实就是Oracle 分析你的文档后，生成的term记录在这里，包括term出现的位置、次数、hash值等。当文档的内容改变后，可以想见这个I表的内容也应该相应改变，才能保证Oracle在做全文检索时正确检索到内容(因为所谓全文检索，其实核心就是查询这个表)。这就用到sync(同步) 和 optimize(优化)了。

同步(sync): 将新的term 保存到I表；

优化(optimize): 清除I表的垃圾，主要是将已经被删除的term从I表删除。

当基表中的被索引文档发生insert、update、delete *** 作的时候，基表的改变并不能马上影响到索引上直到同步索引。可以查询视图 CTX_USER_PENDING查看相应的改动。例如：

SELECT pnd_index_name, pnd_rowid,

TO_CHAR (pnd_timestamp, 'dd-mon-yyyy hh24:mi:ss') timestamp

FROM ctx_user_pending;

该语句的输出类似如下：

PND_INDEX_NAME PND_ROWID TIMESTAMP

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

MYINDEX AAADXnAABAAAS3SAAC 06-oct-1999 15:56:50

同步和优化方法: 可以使用Oracle提供的ctx_ddl包同步和优化索引

一 对于CTXCAT类型的索引来说， 当对基表进行DML *** 作的时候，Oracle自动维护索引。对文档的改变马上反映到索引中。CTXCAT是事务形的索引。

索引的同步

在对基表插入，修改，删除之后同步索引。推荐使用sync同步索引。语法：

ctx_ddlsync_index(

idx_name IN VARCHAR2 DEFAULT NULL

memory IN VARCHAR2 DEFAULT NULL,

part_name IN VARCHAR2 DEFAULT NULL

parallel_degree IN NUMBER DEFAULT 1);

idx_name 索引名称

memory 指定同步索引需要的内存。默认是系统参数DEFAULT_INDEX_MEMORY 。

指定一个大的内存时候可以加快索引效率和查询速度，且索引有较少的碎片

part_name 同步哪个分区索引。

parallel_degree 并行同步索引。设置并行度。

例如：

同步索引myindex:Exec ctx_ddlsync_index ('myindex');

实施建议：建议通过oracle的job对索引进行同步

索引的优化

经常的索引同步将会导致你的CONTEXT索引产生碎片。索引碎片严重的影响了查询的反应速度。你可以定期优化索引来减少碎片，减少索引大小，提高查询效率。

当文本从表中删除的时候，Oracle Text标记删除的文档，但是并不马上修改索引。因此，就的文档信息占据了不必要的空间，导致了查询额外的开销。你必须以FULL模式优化索引，从索引中删除无效的旧的信息。这个过程叫做垃圾处理。当你经常的对表文本数据进行更新，删除 *** 作的时候，垃圾处理是很必要的。

exec ctx_ddloptimize_index ('myidx', 'full');

实施建议：每天在系统空闲的时候对全文索引进行相应的优化，以提高检索的效率

PS定时优化索引

3定时优化同步域索引

创建定时任务，定期优化和同步域索引

SQL> create or replace procedure hsp_sync_index as

2 begin

3 ctx_ddlsync_index('id_cont_msg');

4 end;

5 /

Procedure created

Elapsed: 00:00:0008

SQL> VARIABLE jobno number;

SQL> BEGIN

2 DBMS_JOBSUBMIT(:jobno,'hsp_sync_index();',

3 SYSDATE, 'SYSDATE + (1/24/4)');

4 commit;

5 END;

6 /

PL/SQL procedure successfully completed

Elapsed: 00:00:0027

SQL> create or replace procedure hsp_optimize_index as

2 begin

3 ctx_ddloptimize_index('id_cont_msg','FULL');

4 end;

5 /

SQL> VARIABLE jobno number;

SQL> BEGIN

2 DBMS_JOBSUBMIT(:jobno,'hsp_optimize_index();',

3 SYSDATE, 'SYSDATE + 1');

4 commit;

5 END;

6 /

Procedure created

Elapsed: 00:00:0003

PL/SQL procedure successfully completed

Elapsed: 00:00:0002

SQL>

按类型分为：字符串类型、数字类型、日期类型、LOB类型、LONG RAW& RAW类型、ROWID & UROWID类型。

在讲叙字符串类型前，先要讲一下编码。字符串类型的数据可依编码方式分成数据库字符集（CHAR/VARCHAR2/CLOB/LONG)和国际字符集(NCHAR/NVARCHAR2/NCLOB)两种。数据库中的字符串数据都通过字符集将字符转换为数字后(二进制)，才存储到数据块中。通过不同的编码集转换，即便是相同的字符，也可能会转换成不同的二进制编码。这也是产生乱码的原因。数据库的编码格式一般是在创建数据库时指定的。当然也可以修改数据库的编码。

一 字符串类型

11：CHAR类型 CHAR(size [BYTE | CHAR])

CHAR类型，定长字符串，会用空格填充来达到其最大长度。非NULL的CHAR（12）总是包含12字节信息。CHAR字段最多可以存储2,000字节的信息。如果创建表时，不指定CHAR长度，则默认为1。另外你可以指定它存储字节或字符，例如 CHAR(12 BYTYE) CHAR(12 CHAR)一般来说默认是存储字节

注意：数据库的NLS_CHARACTERSET 为AL32UTF8，即一个汉字占用三到四个字节。如果NLS_CHARACTERSET为ZHS16GBK，则一个字符占用两个字节。

12： NCHAR类型

这是一个包含UNICODE格式数据的定长字符串。NCHAR字段最多可以存储2,000字节的信息。它的最大长度取决于国家字符集。

13 VARCHAR类型

不要使用VARCHAR数据类型。使用VARCHAR2数据类型。

14： VARCHAR2类型

变长字符串，与CHAR类型不同，它不会使用空格填充至最大长度。VARCHAR2最多可以存储4,000字节的信息。

15： NVARCHAR2类型

这是一个包含UNICODE格式数据的变长字符串。 NVARCHAR2最多可以存储4,000字节的信息。

二 数字类型

21 NUMBER类型

NUMBER(P,S)是最常见的数字类型，可以存放数据范围为10130~10126（不包含此值)，需要1~22字节(BYTE)不等的存储空间。

P 是Precison的英文缩写，即精度缩写，表示有效数字的位数，最多不能超过38个有效数字

S是Scale的英文缩写，可以使用的范围为-84~127。Scale为正数时，表示从小数点到最低有效数字的位数，它为负数时，表示从最大有效数字到小数点的位数

下面是官方文档的示例

Actual Data Specified As Stored As

12389 NUMBER 12389

12389 NUMBER(3) 124

12389 NUMBER(6,2) 12389

12389 NUMBER(6,1) 1239

12389 NUMBER(3) 124

12389 NUMBER(4,2) exceeds precision

12389 NUMBER(6,-2) 100

01234 NUMBER(4,5)01234

00012 NUMBER(4,5) 00012

000127 NUMBER(4,5) 00013

0000012 NUMBER(2,7) 0000012

00000123 NUMBER(2,7) 0000012

12e-4 NUMBER(2,5) 000012

12e-5 NUMBER(2,5) 000001

22 INTEGER类型

INTEGER是NUMBER的子类型，它等同于NUMBER（38,0），用来存储整数。若插入、更新的数值有小数，则会被四舍五入。

23 浮点数

Oracle 数据库提供了专为浮点数的两种数值数据类型：

BINARY_FLOAT

BINARY_FLOAT 是 32 位、 单精度浮点数字数据类型。可以支持至少6位精度,每个 BINARY_FLOAT 的值需要 5 个字节，包括长度字节。

BINARY_DOUBLE

BINARY_DOUBLE 是为 64 位，双精度浮点数字数据类型。每个 BINARY_DOUBLE 的值需要 9 个字节，包括长度字节。

在数字的列中，浮点数有小数精度。在 BINARY_FLOAT 或 BINARY_DOUBLE 的列中，浮点数有二进制的精度。二进制浮点数支持的特殊值无穷大和 NaN （不是数字）。

25 FLOAT类型

FLOAT类型也是NUMBER的子类型。

Float(n),数 n 指示位的精度，可以存储的值的数目。N 值的范围可以从 1 到 126。若要从二进制转换为十进制的精度，请将 n 乘以 030103。要从十进制转换为二进制的精度，请用 332193 乘小数精度。126 位二进制精度的最大值是大约相当于 38 位小数精度。

三 日期类型

日期类型用于存储日期数据，但是并不是使用一般的格式（2012-08-08）直接存储到数据库的。

31 DATE类型

DATE是最常用的数据类型，日期数据类型存储日期和时间信息。虽然可以用字符或数字类型表示日期和时间信息，但是日期数据类型具有特殊关联的属性。为每个日期值，Oracle 存储以下信息： 世纪、 年、 月、 日期、 小时、 分钟和秒。一般占用7个字节的存储空间。

32 TIMESTAMP类型

这是一个7字节或12字节的定宽日期/时间数据类型。它与DATE数据类型不同，因为TIMESTAMP可以包含小数秒，带小数秒的TIMESTAMP在小数点右边最多可以保留9位

33 TIMESTAMP WITH TIME ZONE类型

这是TIMESTAMP类型的变种，它包含了时区偏移量的值

34 TIMESTAMP WITH LOCAL TIME ZONE类型

35 INTERVAL YEAR TO MOTH

36 INTERVAL DAY TO SECOND

四 LOB类型

内置的LOB数据类型包括BLOB、CLOB、NCLOB、BFILE（外部存储）的大型化和非结构化数据，如文本、图像、视屏、空间数据存储。BLOB、CLOB、NCLOB类型

41 CLOB 数据类型

它存储单字节和多字节字符数据。支持固定宽度和可变宽度的字符集。CLOB对象可以存储最多 (4 gigabytes-1) (database block size) 大小的字符

42 NCLOB 数据类型

它存储UNICODE类型的数据，支持固定宽度和可变宽度的字符集，NCLOB对象可以存储最多(4 gigabytes-1) (database block size)大小的文本数据。

43 BLOB 数据类型

它存储非结构化的二进制数据大对象，它可以被认为是没有字符集语义的比特流，一般是图像、声音、视频等文件。BLOB对象最多存储(4 gigabytes-1) (database block size)的二进制数据。

44 BFILE 数据类型

二进制文件，存储在数据库外的系统文件，只读的，数据库会将该文件当二进制文件处理

五 RAW & LONG RAW类型

51 LONG类型

它存储变长字符串，最多达2G的字符数据（2GB是指2千兆字节， 而不是2千兆字符），与VARCHAR2 或CHAR 类型一样，存储在LONG 类型中的文本要进行字符集转换。ORACLE建议开发中使用CLOB替代LONG类型。支持LONG 列只是为了保证向后兼容性。CLOB类型比LONG类型的限制要少得多。 LONG类型的限制如下：

1一个表中只有一列可以为LONG型。(Why有些不明白)

2LONG列不能定义为主键或唯一约束，

3不能建立索引

4LONG数据不能指定正则表达式。

5函数或存储过程不能接受LONG数据类型的参数。

6LONG列不能出现在WHERE子句或完整性约束（除了可能会出现NULL和NOT NULL约束）

52 LONG RAW 类型，能存储2GB 的原始二进制数据（不用进行字符集转换的数据）

53 RAW类型

用于存储二进制或字符类型数据，变长二进制数据类型，这说明采用这种数据类型存储的数据不会发生字符集转换。这种类型最多可以存储2,000字节的信息

六 ROWID & UROWID类型

在数据库中的每一行都有一个地址。然而，一些表行的地址不是物理或永久的，或者不是ORACLE数据库生成的。

例如，索引组织表行地址存储在索引的叶子，可以移动。

例如,外部表的ROWID（如通过网关访问DB2表）不是​​标准的ORACLE的rowid。

ORACLE使用通用的ROWID（UROWIDs）的存储地址的索引组织表和外表。索引组织表有逻辑urowids的，和国外表的外urowids。UROWID这两种类型的存储在ROWID伪（堆组织的表的物理行id）。

创建基于逻辑的rowid在表中的主键。逻辑的rowid不会改变，只要主键不改变。索引组织表的ROWID伪UROWID数据类型。你可以访问这个伪列，你会堆组织表的ROWID伪（即使用一个SELECT …ROWID语句）。如果你想存储的rowid索引组织表，那么你就可以定义一列的表型UROWID到列检索值的ROWID伪。

数据库索引的种类：

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

非唯一索引：B树索引

create index 索引名 on 表名（列名） tablespace 表空间名;

唯一索引：建立主键或者唯一约束时会自动在对应的列上建立唯一索引

2、索引列的个数：单列索引和复合索引

3、按照索引列的物理组织方式

B树索引

create index 索引名 on 表名（列名） tablespace 表空间名;

位图索引

create bitmap index 索引名 on 表名（列名） tablespace 表空间名;

反向键索引

create index 索引名 on 表名（列名） reverse tablespace 表空间名;

函数索引

create index 索引名 on 表名（函数名(列名)） tablespace 表空间名;

删除索引

drop index 索引名

重建索引

alter index 索引名 rebuild

索引的创建格式： 

CREATE UNIUQE | BITMAP INDEX <schema><index_name> 

    ON <schema><table_name> 

    (<column_name> | <expression> ASC | DESC, 

     <column_name> | <expression> ASC | DESC,) 

    TABLESPACE <tablespace_name> 

    STORAGE <storage_settings> 

    LOGGING | NOLOGGING 

    COMPUTE STATISTICS 

    NOCOMPRESS | COMPRESS<nn> 

    NOSORT | REVERSE 

    PARTITION | GLOBAL PARTITION<partition_setting>

UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。 

    <column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引” 

    TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高) 

    STORAGE：可进一步设置表空间的存储参数 

    LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率) 

    COMPUTE STATISTICS：创建新索引时收集统计信息 

    NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值) 

    NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值 

    PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区

使用USER_IND_COLUMNS查询某个TABLE中的相应字段索引建立情况

使用DBA_INDEXES/USER_INDEXES查询所有索引的具体设置情况。

在Oracle中的索引可以分为：B树索引、位图索引、反向键索引、基于函数的索引、簇索引、全局索引、局部索引等，下面逐一讲解：

一、B树索引：

最常用的索引，各叶子节点中包括的数据有索引列的值和数据表中对应行的ROWID，简单的说，在B树索引中，是通过在索引中保存排过续的索引列值与相对应记录的ROWID来实现快速查询的目的。其逻辑结构如图：

  可以保证无论用户要搜索哪个分支的叶子结点，都需要经过相同的索引层次，即都需要相同的I/O次数。

B树索引的创建示例：

create index ind_t on t1(id) ;

注1：索引的针对字段创建的，相同字段不能创建一个以上的索引；

注2：默认的索引是不唯一的，但是也可以加上unique，表示该索引的字段上没有重复值(定义unique约束时会自动创建)；

注3：创建主键时，默认在主键上创建了B树索引，因此不能再在主键上创建索引。

二、位图索引：

有些字段中使用B树索引的效率仍然不高，例如性别的字段中，只有“男、女”两个值，则即便使用了B树索引，在进行检索时也将返回接近一半的记录。

所以当字段的基数很低时，需要使用位图索引。(“低”的标准是取值数量 < 行数1%)

位图索引的逻辑结构如上图所示：索引中不再记录rowid和键值，而是将每个值作为一列，用0和1表示该行是否等于该键值(0表示否;1表示是)。其中位图索引的行顺序与原表的行顺序一致，可以在查询数据的过程中对应计算出行的原始物理位置。

位图索引的创建示例：

create bitmap index ind_t on t1(type);

注：位图索引不可能是唯一索引，也不能进行键值压缩。

三、反向键索引：

考虑这个情况：某一字段的值是1-1000顺序排列，建立B树索引后依旧递增，到后来该B数索引不断在后面增加分支，会形成如下如的不对称树：

  反向键索引是一种特殊的B树索引，在存储构造中与B树索引完全相同，但是针对数值时，反向键索引会先反向每个键值的字节，然后对反向后的新数据进行索引。例如输入2008则转换为8002，这样当数值一次增加时，其反向键在大小中的分布仍然是比较平均的。

反向键索引的创建示例：

create index ind_t on t1(id) reverse;

注：键的反转由系统自行完成。对于用户是透明的。

四、基于函数的索引：

有的时候，需要进行如下查询：select from t1 where to_char(date,'yyyy')>'2007';

但是即便在date字段上建立了索引，还是不得不进行全表扫描。在这种情况下，可以使用基于函数的索引。其创建语法如下：

create index ind_t on t1(to_char(date,'yyyy'));

注：简单来说，基于函数的索引，就是将查询要用到的表达式作为索引项。

五、全局索引和局部索引：

这个索引貌似很复杂，其实很简单。总得来说一句话，就是无论怎么分区，都是为了方便管理。

具体索引和表的关系有三种：

1、局部分区索引：分区索引和分区表1对1

2、全局分区索引：分区索引和分区表N对N

3、全局非分区索引：非分区索引和分区表1对N

创建示例：

首先创建一个分区表

create table student

(

stuno number(5),

sname vrvhar2(10),

deptno number(5)

)

partition by hash (deptno)

(

partition part_01 tablespace A1,

partition part_02 tablespace A2

);

创建局部分区索引(1v1)：

create index ind_t on student(stuno)

local(

partition part_01 tablespace A2,

partition part_02 tablespace A1

); --local后面可以不加

创建全局分区索引(NvN)：

create index ind_t on student(stuno)

global partition by range(stuno)

(

partition p1 values less than(1000) tablespace A1,

partition p2 values less than(maxvalue) tablespace A2

); --只可以进行range分区

创建全局非分区索引(1vN)

create index ind_t on student(stuno) GLOBAL;

Oracle中的数据类型有：字符型、数字型、日期型等。具体介绍如下：

1、字符型：char(n):用于标识固定长度的字符串。

当实际数据不足定义长度时，使用空格补全右边不足位。varchar(n):可变字符串类型。

为SQL标准规定的，数据库必须实现的数据据类型。可以存储空字符串。

varchar2(n):可变字符串类型，是oracle在varchar的基础上自行定义的可变长度字符串

类型。当做为列类型使用时，最大长度可被定义为4000;当做为变量类型使用时，长度

可被定义为32767。不可以存储空字符串。

2、数值型：可用于存储整数、浮点数。

number(m,n):m表示有效数字的总位数(最大为38位)，n表示小 数位数。

3、日期时间型：

date:包含Year(年)、Month(月) 、Day(天)、Hour(时)、Minutes(分)、Second(秒)

说明:(yyyy表示4位年分;mm表示月份;dd表示天;hh表示时;mi表示分;ss表示秒)

4、大对象类型：lob：用于存储大对象类型。例如：文本信息长度超过4000、二进制文件等。最大容量为4GB。

lob分类：clob：用于存储大型文本数据。(例如：备注信息)

blob：用于存储二进制数据。(例如：文件)

bfile：作为独立文件存在的二进制数据。

5、特殊数据：

null与空字符串：null与空字符串，都要用is null或is not null进行比较。

单引号：想将单引号做为字符中使用，请通过单引号进行转义。

6、注意：oracle中，没有布尔类型，可利用字符串或数值(1/0)表示

当索引的碎片过多时 会影响执行查询的速度 从而影响到我们的工作效率 这时候采取的最有利的措施莫过于重建索引了 本文主要介绍了Oracle数据库中检查索引碎片并重建索引的过程 接下来我们就开始介绍这一过程

重建索引的步骤如下

确认基本信息

登入数据库 找到专门存放index 的tablespace 并且这个tablespace下所有index的owner都是tax 将index专门存放在一个独立的tablespace 与数据表的tablespace分离 是常用的数据库设计方法

查找哪些index需要重建

通过anlyze index validate structure命令可以分析单个指定的index 并且将单个index 分析的结果存放到 index_stats试图下 一般判断的依据是

height > pct_used < % del_lf_rows / lf_rows + > g )

google上下载了遍历所有index脚本

发现anlyze index validate structure只能填充单个index分析信息 于是google了下 从网上下了个Loop 脚本 遍历索引空间下所有的索引名字 并且可以把所有index的分析信息存放到自己建立的一个用户表中

anlyze index 锁定index

发现下载的脚本不好用 应为anlyze index在分析索引前要争取独占锁 锁住index 很明显有些index正在被应用系统的使用 所以运行anlyze失败 这里吸取的教训是 尽量晚上做这种事 但是本人比较喜欢准时回家 所以在语句中添加Exception Handler 抛出anlyze index执行失败的那些index 名称 使脚本正常运行完毕 并且根据打印到前台的index name手动执行那些index分析

总结

虽然发现 个index中有 个符合上面的判断的依据 但是发现索引都不大 而那些拥有百万leaf的索引又没有符合上面的判断条件 所以结论是无需index rebuild online 没有啥碎片

什么时候可以rebuild index呢

rebuild index online 对那些有大量DML *** 作的大索引是有益的 可以每个月季度做一次针对较大索引的rebuild 通常哪怕rebuild index online也会造成I/O争用 所以有无online意义不大 可以放到 个晚上 分批执行rebuild index 锁定index 不让用户用(没有用户等入的时候) 并且加上paralle 关键字 应为发现数据库服务器有 个cpu processors

lishixinzhi/Article/program/Oracle/201311/19014

以上就是关于在Oracle中合理创建数据库的索引全部的内容，包括:在Oracle中合理创建数据库的索引、Oracle数据库中分区索引和非分区索引的特点，什么时候该用什么类型的索引求大神给详细讲解下、Oracle中表建立联合索引后，其中的一个字段是否可以用来进行索引。等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！