Oracle：单个多列索引或两个单列索引 [英] Oracle: Single multicolumn index or two single column indexes

问题描述

我有桌子

  create table1（
 column1 number（10，
 column2 number（10），
 column3 number（10）
）; 
  

column1 是主键
column2 和 column3 是外键

我在2列上创建了唯一约束

< pre class =lang-sql prettyprint-override> alter table table1
add constraint table1_contr1 unique（column1，column2）
using index tablespace tbs1;


当我在两个列上创建索引时

 在table1上创建索引table1_idx1（column1，coulmn2）; 
 
第1行的错误：
 ORA-01408：此列列表已编入索引
  

因此，当我创建唯一约束时，Oracle已经创建了索引。但是如果我单独创建索引，那么它是一个接受那些

 在table1（column1）上创建索引table1_idx1; 
在table2（column2）上创建索引table2_idx2; 
  

现在我的问题是，在对两个列都有唯一约束后，我仍然需要担心创建一个每列的索引？在访问对象时，每列上没有索引会对性能产生影响吗？

它在oracle 11R2上。

解决方案

这取决于...

仅仅 column1的索引 column1，column2 上有一个综合索引，那么$ c>将是有益的。由于 column1 是主要索引，因此针对只有 column1 作为谓词的表的查询将能够使用综合指数。如果您经常运行需要对索引进行全面扫描的查询并且 column2 的存在大大增加了索引的大小，则可能只是索引 column1 会更高效，因为完整的索引扫描需要做更少的I / O.但这是一个非常不寻常的情况。

如果您的某些查询针对某些 column2 的索引可能会有所帮助该表仅在 column2 上指定谓词。如果 column1 的相对较少的不同值，Oracle可能会使用复合索引执行索引跳过扫描，以满足仅指定的查询column2 作为谓词。但是跳过扫描的效率可能远远低于范围扫描，因此仅仅 column2 的索引很可能会使这些查询受益。如果 column1 存在大量不同的值，则跳过扫描的效率会更低，而且只需 column2的索引会更有益。当然，如果您从不使用 column2 查询表而不在 column1 上指定谓词，则不需要仅 column2 的索引。

I have table

create table1(
  column1 number(10,
  column2 number(10),
  column3 number(10)
);

column1 is Primary Key column2 and column3 is Foreign key

I have created unique constraint on 2 columns

alter table table1 
        add constraint table1_contr1 unique(column1,column2) 
      using index tablespace tbs1;

when I went to create index on both columns as

create index table1_idx1 on table1(column1,coulmn2);

ERROR at line 1:
ORA-01408: such column list already indexed

So Oracle already created index when I create unique constraint. But if I create index separately it is accepting those

create index table1_idx1 on table1(column1);
create index table2_idx2 on table2(column2);

Now my question is, after having unique constraint on both columns do I still need to worry about creating an index on each column? Does without index on each column will have impact on performance while accessing object?

It's on oracle 11R2.

解决方案

It depends...

It is quite unlikely that an index on just column1 will be beneficial if you already have a composite index on column1, column2. Since column1 is the leading index, queries against the table that have only column1 as a predicate will be able to use the composite index. If you are frequently running queries that need to do a full scan of the index and the presence of column2 substantially increases the size of the index, it is possible that an index on just column1 would be more efficient since the full index scan would need to do less I/O. But that is a pretty unusual situation.

An index on just column2 may be beneficial if some of your queries against the table specify predicates on just column2. If there are relatively few distinct values of column1, it is possible that Oracle could do an index skip scan using the composite index to satisfy queries that only specify column2 as a predicate. But a skip scan is likely to be much less efficient than a range scan so it is reasonably likely that an index on just column2 would benefit those queries. If there are a large number of distinct values for column1, the skip scan would be even less efficient and an index on just column2 would be more beneficial. Of course, if you never query the table using column2 without also specifying a predicate on column1, you wouldn't need an index on just column2.

这篇关于Oracle：单个多列索引或两个单列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！