Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: playing with dbms_rowid

Re: playing with dbms_rowid

From: Jonathan Lewis <>
Date: Wed, 31 Mar 2004 17:21:13 +0000 (UTC)
Message-ID: <c4eum8$4l5$>

Note in-line.


Jonathan Lewis

The Co-operative Oracle Users' FAQ

April 2004 Iceland
June  2004      UK - Optimising Oracle Seminar

"Ed Stevens" <> wrote in message

> "(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.
> 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 Wed Mar 31 2004 - 11:21:13 CST

Original text of this message