like any other block?

we realize "hey, we need the root block of index I". The database knows (from the dictionary) that the root block is on block 42 of file 55. The "DBA" data block address of this block is (file,block) = (55,42). We look for that block in the cache and either

a) find it and get it
b) don't find it, ask for it to be read in, read it, put it in the cache and get it

Then, upon processing the root block, we determine we need to go "right" in the index (the data we need is on the right hand side of the index). The root block tells us the "right most block below me in the b*tree is to be found in file 5, block 100"). We now have yet another DBA (5,100) and we do the same thing above.

block by block

I really liked your last paragraph...

That is why I stress over and over in my talks "do not accept advice that doesn't come with some very compeling evidence - evidence, examples, use cases - whatever"

It would be trivial to prove the above "wrong", about 2 minutes of work

a) create table from all_objects
b) index it
c) flush buffer cache OR offline/online tablespace that index is in
d) turn on autotrace
e) select /*+ index(t idx_name ) */ indexed_column from t where rownum = 1 and indexed_column is not null;
f) observe that physical IO is not large.



ops$tkyte@ORA9IR2> create table t as select * from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2> create index t_idx on t(object_name,owner,object_type,last_ddl_time,timestamp) tablespace USERS;
 
Index created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter tablespace users offline;
 
Tablespace altered.
 
ops$tkyte@ORA9IR2> alter tablespace users online;
 
Tablespace altered.
 
ops$tkyte@ORA9IR2> set autotrace traceonly
ops$tkyte@ORA9IR2> select /*+ index( t t_idx ) */ object_name, owner from t where object_name is not null and rownum = 1;
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=319 Card=1 Bytes=31)
   1    0   COUNT (STOPKEY)
   2    1     INDEX (FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=319 Card=29220 Bytes=905820)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          3  physical reads
          0  redo size
        462  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
ops$tkyte@ORA9IR2> select /*+ index( t t_idx ) */ object_name, owner from t where object_name is not null;
 
29220 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=319 Card=29220 Bytes=905820)
   1    0   INDEX (FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=319 Card=29220 Bytes=905820)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2248  consistent gets
        316  physical reads
          0  redo size
     903015  bytes sent via SQL*Net to client
      21916  bytes received via SQL*Net from client
       1949  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      29220  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off

<b>3 phyiscal IO's getting the first row, 316 getting them all...</b>


By the way, this is where test cases EXCEL.  If you have a hypothesis "an index is read entirely into memory", you can easily dream up examples that would have to be true for that to be true.  Run one and see :)

Test cases excel at showing something isn't always true.