原来这种情况下的全表扫描不是真的全表扫描

看到我们的程序中较多的使用类似下面的语句来取得字段类型等信息,一看执行计划,紧张了一下,全表扫描

后来通过多次试验才发现,这种全表扫描是假的,它不读数据,所以不耗资源.

(注意:第一次执行时,会较多的递归SQL,造成逻辑读存在的假象,所以需要第二次以上执行后观察)

SQL> select * from 病人信息 where rownum<1;

未选定行
Execution Plan
-------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (FULL) OF '病人信息'
Statistics
-------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       2954  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

如果采用指定索引字段的方法,反而会带来两块逻辑读.

SQL> select * from 病人信息 where 病人id=0;

未选定行
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF '病人信息'
   2    1     INDEX (UNIQUE SCAN) OF '病人信息_PK' (UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       2954  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed