如我有一个表 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');
下面的內容是使用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.
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
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。个人愚见,望大佬指正。