Re: playing with dbms_rowid

Comments embedded.

On Wed, 31 Mar 2004 17:21:13 +0000 (UTC), "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
<snip>>
>"Ed Stevens" <ed.stevens_at_comcast.net> wrote in message
>news:1512dfb8.0403310828.7c4cec55_at_posting.google.com...
>> "(Of course, you may have created data where the first row, when
>> ordered by pk, was not the first row in the table - but I'm assuming
>> you didn't)."
>>
>> Why would you assume that?
>>
> Because to start with I assumed you were smart enough to
> have worked the answer out for yourself if that had been the
> problem. Then the moment I hit SEND I realised that it was
> irrelevant anyway, but didn't bother to send a correction. I took
> 'lowest' to mean 'appearing first in the output' for just long
>enough
> to add the final unnecessary comment.
>

Ouch. On second reading of my "why would you assume .. . " and first reading of your "I assumed you were smart enough . . ." I can see where it could look like we're starting to get prickly with each other. I truly hope that's not the case. If my question came off with a negative connotation, please accept my apology. It was made in all innocence -- just looking to fill in some more gaps. I'm acutely aware that I don't know what it is that I don't know, and the size and complexity of Oracle leaves me in constant fear that I'm overlooking some key piece of knowledge or understanding.

>> Actually, that gets to what I was trying to prove out when I noticed
>> the "unaccounted for" blocks. (Another 'animated discussion' with my
>> partner.) The table was loaded with a series of INSERTs inside a loop,
>> using the random function to populate the PK. At that point, a SELECT
>> . . ORDER BY block, row showed that the PK was scattered randomly
>> across the blocks. I then did an exp/imp and then SELECT . . . ORDER
>> BY prim_key. My partner said he knew "for a fact" that the import
>> would physically sequence the rows by PK. (as if that meant
>> anything). Unless I missed something, the rows were just as randomly
>> distributed after the import as before.
>>
>
> In general they ought to be. For most of the last N years, exp has
> just done a 'select * from table' to export the data, and that isn't
> likely to use the PK to re-arrange the data.
>
> It's probably possible to make it happen in more recent versions
> of Oracle if the table has become sparse, and you use the option
> for adding a WHERE clause to the export that makes Oracle walk
> the PK index to reach the data because the CBO says that's the
> cheapest option.
Received on Thu Apr 01 2004 - 08:29:48 CST