ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 学习索引-1

学习索引-1

原创 Linux操作系统 作者:lsq_008 时间:2008-03-12 12:59:00 0 删除 编辑

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时,仍然可以走索引。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-204057/,如需转载,请注明出处,否则将追究法律责任。

上一篇: group by 子句学习
请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    323
  • 访问量
    1223540