Oracle复合索引建立的问题

1、

理论上可以使用5列的复合索引，这点通过加hint可以验证；实际运行时oracle会比较走这个索引和全表扫的成本，选低的。

2、

看语句是否用到某个索引，是可以在执行计划里看到索引名的，你这种看filter和access的方法属于绕弯路了，没那么复杂。你看执行计划，如果有index scan就是用到索引了，如果只是table access full就是没用到。

3、是否有必要建2个索引要看你的应用侧重什么，得到一样东西是有代价的。如果只考虑查询性能，2个索引的效果会比1个索引好，因为这2个语句都可以选最合适的索引不必退而求其次，但是代价是多消耗了一个索引的空间和dml时需多维护一个索引。看你觉得这些代价值不值了。

如有帮助请采纳

1) Oracle 数据库对象又称模式对象；数据库对象是逻辑结构的集合，最基本的数据库对象是表。

其他数据库对象包括：同义词，索引，序列，视图。

11 同义词是现有对象的一个别名。

简化SQL语句；隐藏对象的名称和所有者；提供对对象的公共访问。

同义词共有两种类型：私有同义词和公有同义词。

私有同义词只能在其模式内访问，且不能与当前模式的对象同名。

私有同义词语法：

CREATE SYNONYM emp FOR SCOTTemp;--其中emp是SCOTTemp 的别名，SCOTT 是模式名，emp是表明

公有同义词语法：

CREATE PUBLIC SYNONYM emp_syn FOR SCOTTemp;--其中emp_syn 是 同义词名称

创建或替换现有的同义词：

CREATE OR REPLACE SYNONYM emp_syn FOR SCOTTemp;-- OR REPLACE 是替换现有的同义词

删除同义词：

SQL DROP SYNONYM emp;

SQL DROP PUBLIC SYNONYM emp_syn;

12 序列

121序列是用于生成唯一、连续序号的对象；序列可以是升序的，也可以是降序的。

使用CREATE SEQUENCE语句创建序列：

SQL CREATE SEQUENCE toys_seq

START WITH 10

INCREMENT BY 10

MAXVALUE 2000

MINVALUE 10

NOCYCLE

CACHE 10;

注意：指定内存中预先分配的序号数 。

122访问序列：

通过序列的伪列来访问序列的值

NEXTVAL 返回序列的下一个值

CURRVAL 返回序列的当前值

SQL INSERT INTO toys (toyid, toyname, toyprice)

VALUES ( toys_seqNEXTVAL, ‘TWENTY’, 25);

SQL INSERT INTO toys (toyid, toyname, toyprice)

VALUES ( toys_seqNEXTVAL, ’MAGIC PENCIL’, 75);

--其中toys_seqNEXTVAL指定序列的下一个值

SQL SELECT toys_seqCURRVAL FROM dual;-- 检索序列的当前值

123更改和删除序列

使用ALTER SEQUENCE语句修改序列，不能更改序列的START WITH参数

SQL ALTER SEQUENCE toys_seq MAXVALUE 5000 CYCLE;

使用DROP SEQUENCE语句删除序列

SQL DROP SEQUENCE toys_seq;

13视图

131视图以经过定制的方式显示来自一个或多个表的数据；

视图可以视为“虚拟表”或“存储的查询”；

创建视图所依据的表称为“基表。”

视图的优点有：

提供了另外一种级别的表安全性；

隐藏的数据的复杂性；

简化的用户的SQL命令；

隔离基表结构的改变；

通过重命名列，从另一个角度提供数据。

132创建视图的语法：

CREATE [OR REPLACE] [FORCE] VIEW

view_name [(alias[, alias])]

AS select_statement

[WITH CHECK OPTION]

[WITH READ ONLY];

CREATE VIEW stud_view（是图表）

AS SELECT studno, studname, subno（字段）

FROM Stud_details（原始表）;

使用 WITH CHECK OPTION 选项创建视图

CREATE OR REPLACE VIEW pause_view AS

SELECT FROM order_master WHERE ostatus = 'p'

WITH CHECK OPTION CONSTRAINT chk_pv;

使用 ORDER BY 子句创建视图

CREATE OR REPLACE VIEW ord_ven AS

SELECT FROM vendor_master ORDER BY venname;

创建带有错误的视图

CREATE FORCE VIEW ven AS

SELECT FROM venmaster;

创建外联接视图

CREATE VIEW ven_ord_outj_view AS

SELECT vmvencode, venname, orderno, odate, ostatus

FROM vendor_master vm, order_master om

WHERE vmvencode = omvencode(+);

或者

SELECT vmvencode, venname, orderno, odate, ostatus

FROM vendor_master vm LEFT OUTER JOIN order_master om

ON vmvencode = omvencode;

133视图上的DML语句

在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE

视图上的DML语句有如下限制：

只能修改一个底层的基表；

来源：( - 数据库对象_一土林夕_新浪博客如果修改违反了基表的约束条件，则无法更新视图；

如果视图包含连接 *** 作符、DISTINCT 关键字、集合 *** 作符、聚合函数或 GROUP BY 子句，则将无法更新视图；

如果视图包含伪列或表达式，则将无法更新视图。

视图中可以使用单行函数、分组函数和表达式：

CREATE VIEW item_view AS

SELECT itemcode, LOWER(itemdesc) item_desc

FROM itemfile;

使用DROP VIEW语句删除视图

SQL DROP VIEW toys_view;

14索引

索引有各种类型，除了标准索引外，还有一些特殊类型的索引：

索引的类型：唯一索引、位图索引、组合索引、基于函数的索引、反向键索引。

创建标准索引：

SQL CREATE INDEX item_index ON itemfile (itemcode)

TABLESPACE index_tbs;

重建索引：

SQL ALTER INDEX item_index REBUILD;

删除索引：

SQL DROP INDEX item_index;

141唯一索引

唯一索引确保在定义索引的列中没有重复值

Oracle 自动在表的主键列上创建唯一索引

使用CREATE UNIQUE INDEX语句创建唯一索引：

SQL CREATE UNIQUE INDEX item_index

ON itemfile (itemcode);

142组合索引

组合索引是在表的多个列上创建的索引；

索引中列的顺序是任意的；

如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度。

SQL CREATE INDEX comp_index

143反向键索引

反向键索引反转索引列键值的每个字节；

通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上；

创建索引时使用REVERSE关键字：

SQL CREATE INDEX rev_index

ON itemfile (itemcode) REVERSE;

SQL ALTER INDEX rev_index REBUID NOREVERSE;

144位图索引

位图索引适合创建在低基数列上；

位图索引不直接存储ROWID，而是存储字节位到ROWID的映射；

减少响应时间；

节省空间占用。

语法：

SQL CREATE BITMAP INDEX bit_index

ON order_master (orderno);

145索引组织表

索引组织表的数据存储在与其关联的索引中；

索引中存储的是行的实际数据，而不是ROWID；

基于主键访问数据；

CREATE TABLE 命令与 ORGANIZATION INDEX 子句一起用于创建索引组织表：

SQL CREATE TABLE ind_org_tab (

vencode NUMBER(4) PRIMARY KEY,

venname VARCHAR2(20)

)

ORGANIZATION INDEX;

普通表与索引组织表的比较：

普通表索引组织表

ROWID 唯一地标识行 主键唯一地标识行

隐式的 ROWID 列 没有隐式的 ROWID 列

基于 ROWID 的访问 基于主键的访问

顺序扫描返回所有行 完全索引扫描返回所有行，并按主键顺序排列

支持分区 不支持分区

146基于函数的索引

基于一个或多个列上的函数或表达式创建的索引

表达式中不能出现聚合函数

不能在LOB类型的列上创建

创建时必须具有 QUERY REWRITE 权限

SQL CREATE INDEX lowercase_idx

ON toys (LOWER(toyname));

SQL SELECT toyid FROM toys

WHERE LOWER(toyname)='doll';

147索引中的分区

可以将索引存储在不同的分区中

与分区有关的索引有三种类型：

局部分区索引 － 在分区表上创建的索引，在每个表分区上创建独立的索引，索引的分区范围与表一致；

全局分区索引 － 在分区表或非分区表上创建的索引，索引单独指定分区的范围，与表的分区范围或是否分区无关；

全局非分区索引 － 在分区表上创建的全局普通索引，索引没有被分。

148获取索引的信息

与索引有关的数据字典视图有：

USER_INDEXES － 用户创建的索引的信息；

USER_IND_PARTITIONS － 用户创建的分区索引的信息；

USER_IND_COLUMNS － 与索引相关的表列的信息。

SQL SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME

FROM USER_IND_COLUMNS

ORDER BY INDEX_NAME, COLUMN_POSITION;

来源：( - 数据库对象_一土林夕_新浪博客

create index index_name on table_name(column_name desc);

create bitmap index index_name on table_name(column[,column_name[,]]);

create index index_name on table_name(column_name[,column_name,[,]]);

create index index_name on table_name(pricenum);

分区索引和表索引区别：

分区表简单的可以理解为，物理独立，逻辑聚集的一些单位；为了更好的表达业务，把这些物理存储具有相似或者相同特性的存储逻辑化整为0，进行 *** 作；所以分区所以是根据每个分区建立的索引，又会根据分区表的表达形式，分区索引又可以建为组合索引（根据分区表中，分区字段的业务逻辑）；

表索引就是建立在一般表上的索引，并无其他含义；

总结：

无论是物理表还是索引，在数据库中，都会占用存储空间。

如何合理创建Oracle数据库索引的3个要求：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

总之，在适当的表、适当的列上建立适当的索引。这一句话包含的意思有很多，以上内容只是一部分内容。俗话说，师傅领进门，修行靠自身。笔者在这里指能够点到为止。一些具体的索引优化内容还是需要各位读者在日常工作中去体会与总结

以上就是关于Oracle复合索引建立的问题全部的内容，包括:Oracle复合索引建立的问题、Oracle同一表空间可以建立两个同名的索引名、oracle几个索引的创建方法等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！