Sonntag, 28. Dezember 2008

no row information in data dictionary

It all began with a simple question how to get all ROWIDs from a table (without selecting it)? I posted on oracle-l. After some private forks of conversations I got an email by Jared Still where he mentioned:
"If you constructed a rowid, and did a select on the row, it probably doesn't
matter if the row exists or not.

Oracle has to read the block from disk to read the row directory.
The block would get cached whether or not the row exists.

I haven't tested this, but I imagine you will.  :)
"
Of course, he was right:
My testcase was very simple:

  1. preparation
     create table jared as select * from dual;
  2. testcase
    ALTER SESSION SET events '10046 trace name context forever, level 12';
    
    Session altered.
    
    select * from jared where rowid='AAAPa5AABAAATHhAAB';
    
    no rows selected
    
    /
    
    no rows selected
    
    select * from jared where rowid='AAAPa5AABAAATHhAAA';
    
    D
    -
    X
    
    which created a tracefile.
  3. summary
    the first statement hase one related wait: 'db file sequential read' the second and the third have no such waits!
    I crosschecked the situation by asking the buffer:


    select tch from x$bh where obj=63161 and dbablk=78305 and dbarfil=1;
    and - as expected - it just counted up from no rows selected (before the first run) counting 1, 2, 3.
  4. conclusio
    Disclaimer: Even I hope it's well coated, I never can be 100% sure. A modern RDBMS has so many ways to do it's job I would have to ask the developers to prove. (which I can't).

    • the buffer_cache caches blocks, not rows. (obvious?)
    • even if a row does not exist, it's including block is cached, if it's available.
    • as the smallest granularity the RDBMS takes care at IO is a block, and it even stores blocks if the row asked for does not exist.
    this brings me to my own answer to my initial question:
    There is no ROWID information for a given table stored in data dictionary.
Kommentar veröffentlichen