Re: playing with dbms_rowid

"(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?

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.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c4cqkp$9nb$1_at_hercules.btinternet.com>...
> It looks like you may have a 4K block size.
> In LMTs the first 64K (usually) is taken up
> by file control data - including the file's
> bitmap blocks. Block 17 in your case
> would be the table's segment header block,
> leaving block 18 as the first data block in
> the table.
>
> (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).
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> April 2004 Iceland http://www.index.is/oracleday.php
> June 2004 UK - Optimising Oracle Seminar
>
>
> "Ed Stevens" <nospam_at_noway.nohow> wrote in message
> news:7hnj60hhnnees8sfua4p9m98d3op03ii4u_at_4ax.com...
> > Oracle 8.1.4 on Win2k
> >
> > Created a test tablespace. LMT w/ uniform extents.
> > Created table and pk index in the new TS
> > Ran a PL/SQL loop to load the new table with test data.
> >
> > Then ran the following query:
> >
> > select supl_key "key",
> > dbms_rowid.rowid_block_number(rowid) "block",
> > dbms_rowid.rowid_row_number(rowid) "row"
> > from mytable
> > order by supl_key
> > /
> >
> > I was somewhat surprised to find that the lowest # block-id was 18.
> > Am I right in thinking that blocks 1 thru 17 are used for TS
> > management?
Received on Wed Mar 31 2004 - 10:28:17 CST