SQL> select count(*) from zzrk2004.jb_fq_tab where xb='1' and jwh like '330522%' ;
COUNT(*) ---------- 726772 已用时间: 00: 00: 06.44 执行计划 ---------------------------------------------------------- Plan hash value: 2664883756 -------------------------------------------------------------------------------- --------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------- --------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 53913 (71)| 00:10 :47 | | | | 1 | SORT AGGREGATE | | 1 | 10 | | | | | | 2 | PARTITION RANGE SINGLE| | 888K| 8678K| 53913 (71)| 00:10 :47 | 8 | 8 | |* 3 | TABLE ACCESS FULL | JB_FQ_TAB | 888K| 8678K| 53913 (71)| 00:10 :47 | 8 | 8 | -------------------------------------------------------------------------------- --------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("XB"='1' AND "JWH" LIKE '330522%') Note ----- - dynamic sampling used for this statement (level=2) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 58576 consistent gets 58565 physical reads 0 redo size 529 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(*) from zzrk2004.jb_tab where xb='1' and jwh like '330522%'; COUNT(*) ---------- 726772 已用时间: 00: 00: 01.21 执行计划 ---------------------------------------------------------- Plan hash value: 3198461302 -------------------------------------------------------------------------------- --------------- | Id | Operation | Name | Rows | Bytes | Cost (%C PU)| Time | -------------------------------------------------------------------------------- --------------- | 0 | SELECT STATEMENT | | 1 | 15 | 3693 (1)| 00:00:45 | | 1 | SORT AGGREGATE | | 1 | 15 | | | |* 2 | TABLE ACCESS BY INDEX ROWID| JB_TAB | 1802 | 27030 | 3693 (1)| 00:00:45 | |* 3 | INDEX RANGE SCAN | IDX_JB_TAB_JWH | 9011 | | 33 (0)| 00:00:01 | -------------------------------------------------------------------------------- --------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("XB"='1') 3 - access("JWH" LIKE '330522%') filter("JWH" LIKE '330522%') 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 529018 consistent gets 0 physical reads 0 redo size 529 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> jb_fq_tab是一分区表，jb_tab是一普通表， 现在的问题1是 select count(*) from zzrk2004.jb_fq_tab where xb='1' and jwh like '330522%' ;无论执行多少次都有physical reads select count(*) from zzrk2004.jb_tab where xb='1' and jwh like '330522%';只要执行第二次就没有physical reads buffer 657M 问题2，两个表采用那个效率高点，请说是原因文章来源：【】