oracle 大表缓存,关于ORACLE表缓存化的一些困惑 请教高手

个人见解，这是一个明显的小表和大表的连接问题，只要索引建立得当并且统计信息也已经收集，那么这时ORACLE应该会使用nested loops进行连接查询，性能应该不差

SQL> create table bigtab(id number,name varchar2(32));

Table created.

SQL> create table smalltab(id number);

Table created.

--insert data to bigtab

declare

cnt number;

begin

for i in 1..1000000 loop

insert into bigtab values(i,sys_guid());

cnt := cnt + 1;

if mod(cnt,10000) = 0 and cnt >= 10000 then

commit;

end if;

end loop;

commit;

end;

/

SQL> select count(*) from bigtab;

COUNT(*)

----------

1000000

--insert data to smalltab

declare

n number := 0;

begin

for i in 1..200 loop

while (n=0) loop

n := floor(dbms_random.value*1000000);

end loop;

insert into smalltab values(n);

end loop;

commit;

end;

/

SQL> select count(*) from smalltab;

COUNT(*)

----------

200

SQL> create index idx_bigtab_id on bigtab(id);

Index created.

SQL> exec dbms_stats.gather_index_stats('SCOTT','IDX_BIGTAB_ID');

PL/SQL procedure successfully completed.

SQL> create index idx_smaltab_id on smalltab(id);

Index created.

SQL> exec dbms_stats.gather_index_stats('SCOTT','IDX_SMALTAB_ID');

PL/SQL procedure successfully completed.

explain plan for select t1.name from bigtab t1,smalltab t2 where t1.id = t2.id;

select * from table(dbms_xplan.display());

sen hash value: 3332719998

----------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |   200 |  8800 |   402   (0)| 00:00:05 |

|   1 |  TABLE ACCESS BY INDEX ROWID| BIGTAB         |     1 |    31 |     3   (0)| 00:00:01 |

|   2 |   NESTED LOOPS              |                |   200 |  8800 |   402   (0)| 00:00:05 |

|   3 |    INDEX FULL SCAN          | IDX_SMALTAB_ID |   200 |  2600 |     1   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN         | IDX_BIGTAB_ID  |     1 |       |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

lect * from table(dbms_xplan.display());

个人认为只有非常频繁的查询一张表并且表比较小时才需要把表keep到buffer_pool中，如在一个循环内部查询一张字典表，这时把这张字典表keep到内存后可以有效降低physical read，但是logical read还是避免不了的。