"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:
create table jared as select * from dual;
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 - Xwhich created a tracefile.
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.
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.
There is no ROWID information for a given table stored in data dictionary.