ITPub博客

首页 > 数据库 > Oracle > stopkey对索引扫描的影响测试

stopkey对索引扫描的影响测试

原创 Oracle 作者:lsq_008 时间:2014-01-28 12:13:10 0 删除 编辑
测试思路:构造一个表,共50万条记录,前99999条记录对应的id为1,status为1,第10万条记录对应的id也为1,但status为2,
这样,当以id=1 and status=2 and rownum=1 为条件进行查询时,如果只在id上建立索引,那么oracle将从索引中第一条id=1的
记录开始扫描,每扫描一条索引记录就返回表中查找该记录中的status是否为2,这样反复一直扫描到第10万条记录,
才能找到第一条复合条件的记录并返回结果,这种情况下显然效率不高。而如果在id和status上面创建了复合索引,oracle只需要读取一条索引
记录即可找到复合条件的记录,这种情况下查询效率与该索引的选择性实际上是没有关系的。
SQL> desc t
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 ID                                                                                CHAR(20)
 LEV                                                                               VARCHAR2(10)
 BRANCH                                                                            VARCHAR2(10)
 STATUS                                                                            VARCHAR2(1)
 COMM                                                                              VARCHAR2(100)
SQL> begin
  2  for i in 1 .. 500000 loop
  3  if i<100000 then
  4  insert into t values('1',mod(100000,5),lpad(mod(i,20),5,'x'),'1','comm');
  5  elsif i=100000 then
  6  insert into t values('1',mod(100000,5),lpad(mod(i,20),5,'x'),'2','comm');
  7  elsif i>100000 and i<=200000 then
  8  insert into t values('2',mod(100000,5),lpad(mod(i,20),5,'x'),'2','comm');
  9  elsif i>200000 and i<=300000 then
 10  insert into t values('3',mod(100000,5),lpad(mod(i,20),5,'x'),'2','comm');
 11  elsif i>300000 and i<=400000 then
 12  insert into t values('4',mod(100000,5),lpad(mod(i,20),5,'x'),'2','comm');
 13  else 
 14  insert into t values('5',mod(100000,5),lpad(mod(i,20),5,'x'),'2','comm');
 15  end if;
 16  end loop;
 17  end;
 18  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select count(*) from t;

  COUNT(*)
----------
    500000

SQL> select count(*) from(select distinct id,status from t);

  COUNT(*)
----------
         6

SQL> select count(distinct id) from t;


COUNT(DISTINCTID)
-----------------
                5


SQL> create index idx_id on t(id) tablespace users;

Index created.

SQL> create index idx_id_status on t(id,status) tablespace users;

Index created.

SQL> select index_name,column_name,column_position from user_ind_columns where table_name='T';

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
IDX_ID                         ID                                           1
IDX_ID_STATUS                  ID                                           1
IDX_ID_STATUS                  STATUS                                       2

SQL> select index_name,distinct_keys from user_indexes where table_name='T';

INDEX_NAME                     DISTINCT_KEYS
------------------------------ -------------
IDX_ID                                     5
IDX_ID_STATUS                              6

----测试
SQL> select /*+ index(t idx_id) */ id,status,comm from t where id='1' and status='2' and rownum=1;

ID                   S COMM
-------------------- - ----------------------------------------------------------------------------------------------------
1                    2 comm

Execution Plan
----------------------------------------------------------
Plan hash value: 415658150

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    28 |     4   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |        |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T      |     2 |    56 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_ID |   100K|       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   2 - filter("STATUS"='2')
   3 - access("ID"='1')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        899  consistent gets
          0  physical reads
          0  redo size
        540  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--查看10046 trace,可以清楚的看到oracle一边读索引一边读表,obj#=55900为表,obj#=55901为索引
..............
WAIT #1: nam='db file sequential read' ela= 228 file#=4 block#=13227 blocks=1 obj#=55900 tim=1356771811774771
WAIT #1: nam='db file sequential read' ela= 298 file#=4 block#=18829 blocks=1 obj#=55901 tim=1356771811775194
WAIT #1: nam='db file sequential read' ela= 1095 file#=4 block#=13228 blocks=1 obj#=55900 tim=1356771811776411
WAIT #1: nam='db file sequential read' ela= 123 file#=4 block#=18830 blocks=1 obj#=55901 tim=1356771811776828
WAIT #1: nam='db file sequential read' ela= 535 file#=4 block#=13229 blocks=1 obj#=55900 tim=1356771811777482
WAIT #1: nam='db file sequential read' ela= 115 file#=4 block#=13230 blocks=1 obj#=55900 tim=1356771811777755
WAIT #1: nam='db file sequential read' ela= 380 file#=4 block#=18831 blocks=1 obj#=55901 tim=1356771811778220
WAIT #1: nam='db file sequential read' ela= 943 file#=4 block#=13231 blocks=1 obj#=55900 tim=1356771811779412
WAIT #1: nam='db file sequential read' ela= 607 file#=4 block#=18832 blocks=1 obj#=55901 tim=1356771811780184
WAIT #1: nam='db file sequential read' ela= 556 file#=4 block#=13232 blocks=1 obj#=55900 tim=1356771811781034
WAIT #1: nam='db file sequential read' ela= 779 file#=4 block#=18833 blocks=1 obj#=55901 tim=1356771811782041
WAIT #1: nam='db file sequential read' ela= 1215 file#=4 block#=13233 blocks=1 obj#=55900 tim=1356771811783368
WAIT #1: nam='db file sequential read' ela= 522 file#=4 block#=18834 blocks=1 obj#=55901 tim=1356771811784071
WAIT #1: nam='db file sequential read' ela= 283 file#=4 block#=13234 blocks=1 obj#=55900 tim=1356771811784434
WAIT #1: nam='db file sequential read' ela= 400 file#=4 block#=13235 blocks=1 obj#=55900 tim=1356771811784991
WAIT #1: nam='db file sequential read' ela= 252 file#=4 block#=18835 blocks=1 obj#=55901 tim=1356771811785346
WAIT #1: nam='db file sequential read' ela= 323 file#=4 block#=13236 blocks=1 obj#=55900 tim=1356771811785840
WAIT #1: nam='db file sequential read' ela= 313 file#=4 block#=18836 blocks=1 obj#=55901 tim=1356771811786263
WAIT #1: nam='db file sequential read' ela= 149 file#=4 block#=13237 blocks=1 obj#=55900 tim=1356771811786528
WAIT #1: nam='db file sequential read' ela= 256 file#=4 block#=18837 blocks=1 obj#=55901 tim=1356771811786913
WAIT #1: nam='db file sequential read' ela= 277 file#=4 block#=13238 blocks=1 obj#=55900 tim=1356771811787278
WAIT #1: nam='db file sequential read' ela= 317 file#=4 block#=18838 blocks=1 obj#=55901 tim=1356771811787778
WAIT #1: nam='db file sequential read' ela= 272 file#=4 block#=13239 blocks=1 obj#=55900 tim=1356771811788153
..............................


--当使用复合索引查询时,逻辑读明显降低
SQL> select /*+ index(t idx_id_status) */ id,status,comm from t where id='1' and status='2' and rownum=1;

ID                   S COMM
-------------------- - ----------------------------------------------------------------------------------------------------
1                    2 comm

Execution Plan
----------------------------------------------------------
Plan hash value: 3889392193

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     1 |    28 |     4   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |               |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T             |     2 |    56 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_ID_STATUS | 80360 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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


   1 - filter(ROWNUM=1)
   3 - access("ID"='1' AND "STATUS"='2')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        540  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
---此时观察10046 trace文件,只有4次db file sequential read的等待产生,正好符合4个逻辑读的结果
WAIT #1: nam='db file sequential read' ela= 277989 file#=4 block#=7772 blocks=1 obj#=55902 tim=1356771851449041
WAIT #1: nam='db file sequential read' ela= 21429 file#=4 block#=21569 blocks=1 obj#=55902 tim=1356771851471110
WAIT #1: nam='db file sequential read' ela= 6088 file#=4 block#=21365 blocks=1 obj#=55902 tim=1356771851478010
WAIT #1: nam='db file sequential read' ela= 16579 file#=4 block#=13628 blocks=1 obj#=55900 tim=1356771851495606
通过数据块的dump可以确认,其中前两行为branch block,第三行为leaf block,第四行为表的数据块
SQL> select id,status,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t where id='1' and status='2' and rownum=1;

ID                   S DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
-------------------- - ------------------------------------ ------------------------------------
1                    2                                    4                                13628
符合条件的记录正好位于file_id为4,block_id为13628的数据块上。

结论:对于这种where条件中有rownum<=n且n较小的查询,在where条件中所有的列上创建一个复合索引会明显的提高查询性能。

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

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

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1245849