1.测试表demo；
SQL> desc demo;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 ADDRESS                                            VARCHAR2(60)
 PHONE#                                             VARCHAR2(12)
 NO                                                 NUMBER(38)
索引 idx_demo。创建在3列上，
SQL> create index idx_demo on demo(no,name,address);
 
Index created.

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
IDX_DEMO                       DEMO                           NAME
IDX_DEMO                       DEMO                           ADDRESS
IDX_DEMO                       DEMO                           NO

2.按不同查询条件测试
（1）测试以no为查询条件
SQL> select name,phone# from demo where no=199;
 
NAME                           PHONE#
------------------------------ ------------
asdf                           fffff
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEMO'
   2    1     INDEX (RANGE SCAN) OF 'IDX_DEMO' (NON-UNIQUE)
（2）测试以no和name为查询条件
SQL> select name,phone# from demo where no=199 and name='asdf';
 
NAME                           PHONE#
------------------------------ ------------
asdf                           fffff
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEMO'
   2    1     INDEX (RANGE SCAN) OF 'IDX_DEMO' (NON-UNIQUE)
（3）测试以no,name,address为查询条件
SQL>  select name,phone# from demo where no=199 and name='asdf' and address='aaaa';
 
NAME                           PHONE#
------------------------------ ------------
asdf                           fffff
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEMO'
   2    1     INDEX (RANGE SCAN) OF 'IDX_DEMO' (NON-UNIQUE)
（4）测试以name为查询条件
SQL> select name,phone# from demo where name='199';
 
NAME                           PHONE#
------------------------------ ------------
199                            fffff
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DEMO'
（5）测试以name，address为查询条件
SQL> select name,phone# from demo where name='199' and address='aaaa';
 
NAME                           PHONE#
------------------------------ ------------
199                            fffff
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DEMO'

可见，对于复合索引，只有查询条件里包含了索引的第一个字段时，查询时才会走索引，否则，将执行全表扫描

3.对于包含空值列的索引测试

SQL> update demo set name='' where name='199';
 
1 row updated.
 
 
Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT ptimizer=CHOOSE
   1    0   UPDATE OF 'DEMO'
   2    1     TABLE ACCESS (FULL) OF 'DEMO'
 
 
 
SQL> commit;
 
Commit complete.
 
SQL> select * from demo where name is null;
 
NAME                           ADDRESS                                                      PHONE#               NO
------------------------------ ------------------------------------------------------------ ------------ ----------
                               aaaa                                                         fffff               199
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DEMO'
 
 
 
SQL> create index idx_demo_name on demo(name);
 
Index created.
 
SQL> select * from demo where name is null;
 
NAME                           ADDRESS                                                      PHONE#               NO
------------------------------ ------------------------------------------------------------ ------------ ----------
                               aaaa                                                         fffff               199
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DEMO'
 
 
 
SQL> select * from demo where name='99';
 
NAME                           ADDRESS                                                      PHONE#               NO
------------------------------ ------------------------------------------------------------ ------------ ----------
99                             aaaa                                                         fffff                99
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEMO'
   2    1     INDEX (RANGE SCAN) OF 'IDX_DEMO_NAME' (NON-UNIQUE)
 可见，对于包含空值列的索引，当查询条件里包含null值时，不走索引，不包含null时，仍然可以走索引。