测试思路：构造一个表，共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博客

stopkey对索引扫描的影响测试

最新文章