I created a similar test case. I took 150,000 rows from the TPCH.LINEITEM table and put a non-unique index on L_ORDERKEY, similar to the non-unique index on PATIENT_ID of ERADMIN.ADMISSION. I ran a similar query to the one Robin did:

select 
   count(*) 
from  
   tpch.lineitem
where 
   l_orderkey between 1 and 500000;

This query returns a count(*) of 123,895 rows, where Robin's query appears to return 120,002 rows. Not exact, but similar enough.

In both cases (8k block and 16k block) the index is fully cached in the db cache.

Here are the results:

Index using 8k block

=====================

PARSING IN CURSOR #6 len=67 dep=0 uid=63 oct=3 lid=63 tim=1166631260373388 hv=1410791531 ad='7be2521c'

select count(*) from

LINEITEM where L_ORDERKEY between 1 and 500000

END OF STMT

PARSE #6:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1166631260373382

EXEC #6:c=999,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1166631260373564

WAIT #6: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=355 tim=1166631260373594

FETCH #6:c=38994,e=38239,p=0,cr=340,cu=0,mis=0,r=1,dep=0,og=1,tim=1166631260411862

WAIT #6: nam='SQL*Net message from client' ela= 125 driver id=1650815232 #bytes=1 p3=0 obj#=355 tim=1166631260412149

FETCH #6:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1166631260412202

WAIT #6: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=355 tim=1166631260412233

WAIT #6: nam='SQL*Net message from client' ela= 179 driver id=1650815232 #bytes=1 p3=0 obj#=355 tim=1166631260412439

*** SESSION ID:(536.24188) 2007-11-09 09:46:50.663

STAT #6 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=340 pr=0 pw=0 time=38254 us)'

STAT #6 id=2 cnt=123895 pid=1 pos=1 obj=452157 op='INDEX FAST FULL SCAN IDX8K (cr=340 pr=0 pw=0 time=123965 us)'

********************************************************************************

select count(*) from

LINEITEM where L_ORDERKEY between 1 and 500000



call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.03       0.03          0        340          0           1

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

total        4      0.03       0.03          0        340          0           1



Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 63



Rows     Row Source Operation

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

     1  SORT AGGREGATE (cr=340 pr=0 pw=0 time=38254 us)

 123895   INDEX FAST FULL SCAN IDX8K (cr=340 pr=0 pw=0 time=123965 us)(object id 452157)

Index using 16k block

=====================

PARSING IN CURSOR #1 len=67 dep=0 uid=63 oct=3 lid=63 tim=1166632133848789 hv=1410791531 ad='7be2521c'

select count(*) from

LINEITEM where L_ORDERKEY between 1 and 500000

END OF STMT

PARSE #1:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1166632133848783

EXEC #1:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1166632133848969

WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1166632133849012

FETCH #1:c=41993,e=41520,p=0,cr=172,cu=0,mis=0,r=1,dep=0,og=1,tim=1166632133890564

WAIT #1: nam='SQL*Net message from client' ela= 360 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1166632133891113

FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1166632133891173

WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1166632133891208

WAIT #1: nam='SQL*Net message from client' ela= 578 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1166632133891812

XCTEND rlbk=0, rd_only=1

STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=172 pr=0 pw=0 time=41548 us)'

STAT #1 id=2 cnt=123895 pid=1 pos=1 obj=452273 op='INDEX FAST FULL SCAN IDX16K (cr=172 pr=0 pw=0 time=124066 us)'

********************************************************************************

select count(*) from

LINEITEM where L_ORDERKEY between 1 and 500000



call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.04       0.04          0        172          0           1

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

total        4      0.04       0.04          0        172          0           1



Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 63



Rows     Row Source Operation

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

     1  SORT AGGREGATE (cr=172 pr=0 pw=0 time=41548 us)

 123895   INDEX FAST FULL SCAN IDX16K (cr=172 pr=0 pw=0 time=124066 us)(object id 452273)