日期:2014-05-16  浏览次数:21174 次

《Pro Oracle SQL》Chapter3--3.2 Index Scan Access Methods--3.2.1 Index Structure

Index Scan Access Methods????? 索引扫描访问方法 ? (page 95)
??? If you have a book about U.S. Presidents and want to find information on Jimmy Carter, you could start
on the first page and visually scan each page until you came to the section of the book about Carter. ?
However, it would take a lot of time to do that scan so you might find it more expedient to look up
Carter in the book’s index.? Once you have the page number, you can go directly to that location. An
index scan operation is conceptually similar to using an index in a book.
??? 如果你有一本关于美国总统的书,且想要找出Jimmy Carter的信息,你可从第一页开始扫描每一页直到你到达关于Cater的那一节。然而,扫描将耗费你很多时间,如此的话,你可通过在书的索引中查找Carter更为方便的找到它。一旦你有了页码号,你就能直接的到达位置。索引扫描操作概念上相似于使用书中的索引。
??? The default index type is a B-tree index and is the only type I am going to discuss in this chapter. ?
Indexes are created on one or more table columns or column expressions and store the column values
along with a rowid.?
There are other pieces of information stored in the index entry, but for your
purposes you’re only going to concern yourselves with the column value and the rowid.? The rowid is a
pseudocolumn that uniquely identifies a row within a table.?
It is the internal address of a physical
table row and consists of an address that points to the data file that contains the table block that
contains the row and the address of the row within the block that leads directly to the row itself.
? ?
Listing 3-7 shows how to decode the rowid into a readable form.
??? 默认的索引类型是B-tree索引且也是我在本章中唯一讨论的。索引创建在一个或多个表列或列的表达式上,且存储的列值带有一个rowid。 还有其他一些信息也存于索引记录中,但是为了便于理解,你现在只需关心列值和rowid。rowid是一个伪列,唯一的标示表中的一行。 它是物理表行的内部地址又组成了指向数据文件的地址,数据文件包含表块,表块包含行而在块中的行地址就能找到行本身。 列表3-7展示了如何解码rowid成可读的形式。
Listing 3-7. Decoding rowid
SQL> column filen format a50 head 'File Name'
SQL>
SQL> select? e.rowid ,
? 2????????? (select file_name
? 3???????????? from dba_data_files
? 4??????????? where file_id = dbms_rowid.rowid_to_absolute_fno(e.rowid, user, 'EMP'))???? filen,
? 5????????? dbms_rowid.rowid_block_number (e.rowid) block_no,
? 6????????? dbms_rowid.rowid_row_number (e.rowid) row_no
? 7??? from? emp e
? 8?? where? e.ename = 'KING' ;
?
ROWID?????????????????????????? File Name?????????????????????????????????????????????????????????????????????????????? BLOCK_NO?? ROW_NO
------------------??????????????????? ----------------------------------------------- ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ---------???????? --------
AAANprAAEAAAWVvAAI?? C:\ORACLE\PRODUCT\11.2.0\ORADATA\DB\USERS01.DBF?? 91503?????? ?? 8
?
1 row selected.
? ? As you can see, the rowid points to the exact location of a particular row.? Therefore, when an index
is used to access a row, all that happens is that a match is made on the access criteria provided in the
predicate, then the rowid is used to access the specific file/block/row of data.? Block accesses made via
an index scan are made using single-block reads.?
That makes sense when you consider how the rowid
is used.? Once the index entry is read, only the single block of data identified by that rowid is retrieved;
once it is retrieved, only the row specified by the rowid is accessed.??
??? 如你所见,rowid指向某一行的准确位置。 因此,当用某索引访问某行时,所发生的事就是匹配在谓词中提供的访问标准,然后用rowid访问特定的