如何创建合适的索引－主要是创建多列索引还是单列索引？

w_z_y

如我有一个表
UserInfo
{
id number(10,0) not null,
username varchar(100) not null,
realname varchar(100) not null,
birthday date not null
}

其中id列是PK，username 是唯一键(unique)
以上四列经常被当作条件放到where 字句里面。
我是分别在每一列上创建一个索引，还创建一个索引包含一列呢？
请多指教！！
如：
1）select * from userInfo where id = 10;
select * from userInfo where username = '1111';
select * from userInfo where name = '1111';
select * from userInfo where birthday= to_date('2003-10-10','yyyy-mm-dd');

这个肯定创建单列索引了
2）select * from userinfo where name = '2222' and birthday= to_date('2003-10-10','yyyy-mm-dd');
如何创建索引？？
3）select * from userinfo where id = 1111 and name = '2222' and birthday= to_date('2003-10-10','yyyy-mm-dd');

ytjutao

每一列创建索引

oracle-plus

下面的內容是使用composite indexes的好處和一些建議，你看一下看對你有沒有用：
Choosing Composite Indexes
A composite index contains more than one key column. Composite indexes can provide additional advantages over single-column indexes:

Improved selectivity
Sometimes two or more columns or expressions, each with poor selectivity, can be combined to form a composite index with more accurate selectivity.

Reduced I/O
If all columns selected by a query are in a composite index, then Oracle can return these values from the index without accessing the table.

A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:

CREATE INDEX comp_ind
ON tab1(x, y, z);

These combinations of columns are leading portions of the index: x, xy, and xyz. These combinations of columns are not leading portions of the index: yz, y, and z.

Follow these guidelines for choosing keys for composite indexes:
Consider creating a composite index on keys that are frequently used together in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either key individually.

If several queries select the same set of keys based on one or more key values, then consider creating a composite index containing all of these keys.

Of course, consider the guidelines associated with the general performance advantages and trade-offs of indexes described in the previous sections. Follow these guidelines for ordering keys in composite indexes:

Create the index so the keys used in WHERE clauses make up a leading portion.

If some keys are used in WHERE clauses more frequently, then be sure to create the index so that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index.

If all keys are used in WHERE clauses equally often, then ordering these keys from most selective to least selective in the CREATE INDEX statement best improves query performance.

If all keys are used in the WHERE clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index.

有一點也注意的是：如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。
所以，你要根據你的查詢需求來決定是否使用Composite Indexes

grassbell · 发表于 2004-12-26 17:27

最初由 oracle-plus 发布
[B]
有一點也注意的是：如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。
所以，你要根據你的查詢需求來決定是否使用Composite Indexes [/B]

这个是8i的限制，9i不是这样了

wlidflower · 发表于 2004-12-26 18:03

创建索引不光要看查询
还要注意对表进行delete insert是否很频繁
综合考虑
索引并不是越多越好
索引多了自然系统对维护索引的代价也多了

xzh2000 · 发表于 2004-12-26 18:05

三个索引足了
1 id上的主键
2 username上的unique index
3 name+birthday的组合索引

hanjs · 发表于 2007-11-15 18:15

最初由 grassbell 发布
[B]

这个是8i的限制，9i不是这样了 [/B]

这个要看sql怎么写的。

SQL> desc user_info
名称                                     是否为空? 类型
----------------------------------------- -------- ----------------------------

ID                                              NUMBER
CODE                                              VARCHAR2(10)
NAME                                              VARCHAR2(20)
FDATE                                           DATE
REMARK                                           VARCHAR2(50)

在name,fdate上建立复合索引。

select name,fdate from user_info where fdate=trunc(sysdate)

这样可以利用到索引，如果查询中还有别的列，那么就不行了！

一般我们认为like '%xx%'是不会走索引的，可是如果只查询了索引列或count等，还是可以利用索引的！

只看该作者 · 发表于 2007-11-15 20:08

将这四列作一个复合索引

seamanczg1984 · 发表于 2007-11-15 21:52

最初由 hanjs 发布
[B]

这个要看sql怎么写的。

SQL> desc user_info
名称                                     是否为空? 类型
----------------------------------------- -------- ----------------------------

ID                                              NUMBER
CODE                                              VARCHAR2(10)
NAME                                              VARCHAR2(20)
FDATE                                           DATE
REMARK                                           VARCHAR2(50)

在name,fdate上建立复合索引。

select name,fdate from user_info where fdate=trunc(sysdate)

这样可以利用到索引，如果查询中还有别的列，那么就不行了！

一般我们认为like '%xx%'是不会走索引的，可是如果只查询了索引列或count等，还是可以利用索引的！ [/B]

SQL> create table test1(id int,code varchar2(20),zode varchar2(10));

Table created.

SQL> begin
  2    for i in 1..1000 loog
  3  /
      for i in 1..1000 loog
                     *
ERROR at line 2:
ORA-06550: line 2, column 19:
PLS-00103: Encountered the symbol "LOOG" when expecting one of the following:
* & - + / at loop mod remainder rem <an exponent (**)> ||
multiset

SQL> begin
  2    for i in 1..1000 loop
  3    insert into test1 values(i,'xxx'||i,'zode'||i);
  4    end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> create index ind_id_code on test1(id,code);

Index created.

SQL> set autotrace on
SQL> set autotrace trace
SQL> set autotrace
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autotrace trace exp
SQL> select id,code from test1 where code='xxx10';

Execution Plan
----------------------------------------------------------
Plan hash value: 3343096337

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

----

| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time
|

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

----

| 0 | SELECT STATEMENT    |          |    1 | 25 |    3 (0)| 00:00:

01 |

|*  1 |  INDEX FAST FULL SCAN| IND_ID_CODE |    1 | 25 |    3 (0)| 00:00:

01 |

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

----

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("CODE"='xxx10')

Note
-----
- dynamic sampling used for this statement

SQL>

是index fast full scan啊，

seamanczg1984 · 发表于 2007-11-15 21:59

SQL> select id,code from test1 where id=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2428865550

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

| Id  | Operation       | Name       | Rows  | Bytes | Cost (%CPU)| Time    |

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

| 0 | SELECT STATEMENT |          |    1 | 25 |    2 (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IND_ID_CODE |    1 | 25 |    2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("ID"=10)

Note
-----
- dynamic sampling used for this statement

SQL>
如果这么写就是INDEX RANGE SCAN了，
估计oracle看到sql是只查询id,code这两个字段，index中正好已经包括这两个字段，因此不用scan table了。但是上面一个查询条件是code用不到index 的key因此使用了full index scan，而这个的查询条件是id用到了key，因此使用了index range scan，实际上这两个是不一样的

个人认为，建index的目的是为了让oracle使用index range scan。个人愚见，望大佬指正。