Re: index clarification

From: Tim Gorman <tim_at_evdbt.com>
Date: Thu, 30 Aug 2012 09:18:54 -0600
Message-ID: <503F845E.2040807_at_evdbt.com>

Jeremy,
It would be even more interesting to hear what this technical lead believed existed in index entries other than ROWIDs? :-)

If Richard Foote's articles aren't convincing enough, then this technical lead should feel free to take his/her own block dumps and post a contrary article for peer review. The command "ALTER SYSTEM DUMP DATAFILE [ /file#/| /'file-name'/] BLOCK MIN /mmmmm /BLOCK MAX /nnnnnn/" can be used to produce the dumps to trace file in the USER_DUMP_DEST directory on the database server, and the values for /file#/, /mmmmm/, and /nnnnnn/ can obtained from the DBA_EXTENTS view where SEGMENT_NAME = '/index-name/'. Everyone would be delighted to see new and revealing information posted.

An additional note: ROWIDs stored in non-partitioned indexes and locally-partitioned indexes are "restricted" pre-Oracle8 ROWIDs which are 6-bytes in length, while ROWIDs stored in global partitioned indexes are "extended" ROWIDs which are 10-bytes in length. So, the last "column" (a.k.a. ROWID) shown in block dumps of index entries will be either 6- or 10-bytes in length, depending on the type of index.

One situation where ROWIDs are not strictly used are /secondary indexes/ on index-organized tables (IOTs), where UROWID <http://docs.oracle.com/cd/B10501_01/server.920/a96524/c13datyp.htm#918>s are stored instead. UROWIDs are composed of a "physical guess" (a.k.a. ROWID) as well as a "logical ROWID" (a.k.a. PK data value). When using a secondary index, Oracle tends to first use the "physcial guess" to access by ROWID and compare the PK value found in the table row to the "logical ROWID" value stored in the index entry. If a match, then all is good and we've got our table row. If no match, then Oracle tries again using the "logical ROWID" to access the table through it's PK index. More information about how ROWIDs and UROWIDs are used can be found online here
<http://docs.oracle.com/cd/B10501_01/server.920/a96524/c13datyp.htm#966>.

So, is it perhaps possible that a misunderstanding has occurred and your technical lead was possibly thinking about secondary indexes on IOTs? Even so, he/she would still be mistaken, but a little more understandably so due to the complexity of secondary indexes and IOTs.

Hope this helps...

-Tim

On 8/30/2012 8:44 AM, Paul Drake wrote:
> Jeremy,
> Did someone say ... Block Dumps?
>
> https://richardfoote.wordpress.com/2010/10/07/index-block-dump-index-only-section-part-ii-station-to-station/
>
> "Note this is a *Non-Unique *index and so the index entries have a specific
> format that enables each index entry to still be defined in a unique
> manner. This is effectively achieved by including the rowid as an
> additional column within each index entry."
>
>
> (part II from this series)
> https://richardfoote.wordpress.com/2010/02/08/index-block-dumps-and-treedumps-part-i-knock-on-wood/
>
> Paul
>
> putting out fire with gasoline.
>
>
> On Thu, Aug 30, 2012 at 10:36 AM, Sheehan, Jeremy <
> JEREMY.SHEEHAN_at_nexteraenergy.com> wrote:
>
>> This may seem like a newbie question, but my technical lead told me
>> something yesterday that was contrary my thinking and assumptions on how
>> indexes work.
>>
>> I was always under the assumption (from studies and readings) that an
>> index contains the values for the indexed columns and a rowid that points
>> back to the table. My technical lead says that indexes do not have a
>> rowid. Is he right? If it doesn't contain the rowid, how does it relate
>> back to the table?
>>
>> Thanks in advance...
>>
>> Jeremy
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 30 2012 - 10:18:54 CDT