Oracle-oracle查询疑惑

Question

我有一个表

create table Z_LOAD_TEST
(
  guid     CHAR(32) not null,
  division CHAR(15),
  status   CHAR(1),
  title    NVARCHAR2(30)
)

Answer 1

第一个查询是全表扫描;
算一下, 全表数据大小差不多是:78 * 1kw byte;假如一个DB_BLOCK_SIZE= 8k(oracle default), 一个block用50%存数据, 那么全表扫描, 至少要过19 5000 block;

据CBO fundamentals, 当db_file_multiblock_read_count为128, ajusted dbf_mbrc为40.82(不过他的数据是8i的); 那么需要读盘19 5000 /40.82 = 4777 次;

如果我们参考http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams047.htm
Oracle现在可以自动调节db_file_multiblock_read_count;而且不建议用户自己去设置. 看系统的IO能力, 最多一次可读取 ((max I/O size)/DB_BLOCK_SIZE), 而一般环境里 max I/O size为1M, 那么设block大小为8k, ((max I/O size)/DB_BLOCK_SIZE)=128. 那么理论上只需读盘1524次(实际肯定是达不到.)
实际在全表扫描时, Oracle会做很多优化, 比如cache.

再来说第二个查询,
先INDEX RANGE SCAN, 在division的索引上找出division = '219000000000000' 的2000个rowid; 读索引的时间可以忽略了, 再根据2000个rowid去取相应的block(因为还要判断status='1', 必须读盘), 因为楼主说是平均分布, 所以一个取一个block, 读盘2000次.

这样的话, 我们可以看到, 根据楼主的数据, 第二种查询, 2000次差不多是肯定的;
但第一种查询, 和系统的配置(更牛的机器, 存储), Oracle参数的调整有关.
楼主看到第一种查询更快, 可能是优化做的好吧.

附录:
贴上我在11g上看到的执行计划(空表 无数据)

SQL> select count(*) from z_load_test
2 where 1=1
3 and status = '1';

COUNT(*)

0

Execution Plan

Plan hash value: 2041872663

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|

| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 3 | | |

|* 2 | TABLE ACCESS FULL| Z_LOAD_TEST | 1 | 3 | 2 (0)| 00:00:01 |

Predicate Information (identified by operation id):

2 - filter("STATUS"='1')

Note
------ dynamic sampling used for this statement (level=2)

Statistics

55 recursive calls
0 db block gets
51 consistent gets
0 physical reads
0 redo size
345 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from z_load_test
2 where 1=1
3 and division = '219000000000000'
4 and status = '1';

COUNT(*)

0

Execution Plan

Plan hash value: 3152119255

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 20 | | |

|* 2 | TABLE ACCESS BY INDEX ROWID| Z_LOAD_TEST | 1 | 20 | 1 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | IDX_Z_LOAD_TEST_DIV | 1 | | 1 (0)| 00:00:01 |

Predicate Information (identified by operation id):

2 - filter("STATUS"='1')
3 - access("DIVISION"='219000000000000')

Note

dynamic sampling used for this statement (level=2)

Statistics

1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
345 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

推荐 <<Cost-Based Oracle Fundamentals>>, 第二章, Tablescans; 第四章, Simple B-tree Access.