Rowid High-Speed Paging resolution

Source: Internet
Author: User

-- Select t. rowid rid, t. lastdate from t_test t order by t. lastdate desc; -- obtain the maximum number of pages by page in step 2. select rownum rn, rid from (select t. rowid rid, t. lastdate from t_test t order by t. lastdate desc) where rownum <= 10; -- obtain the minimum number of pages in step 3 of the page. select rn, rid from (select rownum rn, rid from (select t. rowid rid, t. lastdate from t_test t order by t. lastdate desc) where rownum <= 10) where rn> 5; -- query the specific data select t1. * based on the physical address in step 4 of the page .*, t1.rowid from t_test t1 where t1.rowid in (select rid from (select rownum rn, rid from (select rowid rid, t. lastdate from t_test t order by t. lastdate desc) where rownum <= 10000) where rn> 5000 );

 

Before 8i, rowid consists of file # + block # + row #, which occupies 6 bytes of space, 10 bit file #, 22 bit block #, and 16 bit row #.

Oracle dba (data block address) is 32 bits, including 10-bit file # And 22-bit block #.

Since no 0-numbered file exists, the maximum number of files in oracle is 2 ^ 10-1 = 1023

The maximum size that datafile can achieve is 2 ^ 22 * db_block_size.

If db_block_size is 4 K, the datafile max size is 16 GB.

If db_block_size is 8 K, the datafile max size is 32 GB.

From oracle 8, rowid is changed to extend rowid, which is composed of data_object_id # + rfile # + block # + row # And occupies 10 bytes of space,

32bit data_object_id #, 10 bit rfile #, 22bit block #, and 16 bit row #. Since the composition of rowid changes from file # To rfile #,

Therefore, the limit on the number of data files also changes from the maximum number of data files in the entire database to the maximum number of data files per data_object_id. Of course, you may want to ask,

Why Does oracle not adjust the number of bits in rowid indicating file #? This should be caused by compatibility. The rowid stored in the oracle7 Index

It is file # + block # + row #, because after such processing, oracle8 and oracle7 are not changed about the storage of indexes.

Although oracle uses extend rowid, bytes rowid is still stored in common indexes. Only 10 bytes are stored in global indexes.

Extend rowid, and extend rowid is also a necessary condition for the emergence of global index.

 

1. Basic concepts of rowid:
1) rowid is a pseudo column used to ensure the uniqueness of the row in the table. It does not indicate the physical location of the trip, but can be used to locate the row.

2) rowid is a set of established values stored in the index (when the row is determined ). We can select it like a normal column in a table.

3) using rowid is the fastest way to access a row in the table

4) The rowid needs 10 bytes for storage and is displayed as an 18-Bit String.

 


2. Under what circumstances will rowid change?
Generally, when the rows in the table are determined, the rowid will not change. However, when the following conditions occur, rowid will change:


1) Move the tablespace of a table


2) After EXP/IMP is performed on a table

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.