Shulou(Shulou.com)06/01 Report--

For the optimizer, it is very important for the optimizer to decide how to access the data when parsing the target SQL and getting the execution plan, that is, the optimizer decides what way and method to access the data stored in the oracle database that the target SQL needs to access.

The data that the target SQL needs to access is generally stored in the table, and there are two ways for oracle to access the data in the table: one is to access the table directly, the other is to access the index first and return to the table (of course, if the data to be accessed by the target sql can be obtained only by accessing the relevant indexes, then there is no need to return to the table at this time)

There are two methods for direct orientation table data in oracle database: one is full table scan, but the other is rowid scan.

1. Full table scan

Full scan means that when oracle accesses the data in the target table, it will scan from the first block (BLOCK) of the first area (EXTENT) occupied by the table to the high water level line (HWM) of the table. All data blocks oracle in this range must be read. Of course, oracle applies the conditions specified in the where condition of the target SQL to all data read during this period, and finally returns only those data that meet the filter criteria.

It is not that full table scanning is not good. In fact, oracle will use multiple reads when doing full table scanning, which is very efficient when the amount of data in the target table is small. But the biggest problem with full table scanning is that the execution time of the target sql scanning is unstable and uncontrollable, and this execution time will certainly increase with the increase of the target table data. Because the high water mark of the target table will continue to rise with the increase of the amount of data of the target table, the number of data blocks that need to be read when the table is scanned by the whole table is also increasing, which means that the Iscaro resources required for scanning the table by the whole table will continue to increase, and of course, the time required to complete the full scan of the table will also increase. In addition, for CBO, the increasing cost of Imax O resources means that the cost of full table scanning increases with the increase of the amount of data in the target table.

In oracle, if you constantly insert data into the target table, the high watermark moves up when the existing space allocated to the table is insufficient, but if you delete data from the table with the delete statement, the high watermark does not move down with it. The negative effect of this characteristic of the high water mark is that even if the delete statement is used to delete all data, the high water mark is still in its original position, which means that when the full table scans the table, it still needs to scan all the data blocks under the table's high water mark. Therefore, the time spent on the full table scan operation of the table will not be improved compared with before.

2.ROWID scanning

ROWID scanning means that when oracle accesses the data in the target table, it locates and accesses the data directly through the rowid where the data is located. Rowid represents the physical storage address where the row records of the oracle are located, which means that the rowid actually corresponds to the row records in the data block in the oracle.

Since rowid represents the physical storage address where the data rows of the table are located, when oracle knows the rowid of the data rows to be accessed, it can naturally access the relevant data rows of the corresponding table directly according to rowid, which is the definition of rowid.

Strictly speaking, rowid scanning in oracle has two meanings: one is to directly access the corresponding data row records according to the value of rowid entered by the user in the sql statement; the other is to access the relevant index first, and then return to the table to access the corresponding row records according to the rowid obtained after accessing the index.

For the heap table in oralce, we can get the value of the rowid where the corresponding row record is located through the built-in rowid fake (note that this rowid is just a fake This column does not exist in the actual table block.) then we can translate the values of the above pseudo columns into the actual physical storage address of the corresponding data row through the relevant methods in the dbms_rowid package (dbms_rowid.rowid_relative_fno,dbms_rowid.rowid_block_number and dbms_rowid.rowid_row_number).

SQL > select empno,ename,rowid,dbms_rowid.rowid_relative_fno (rowid) | |'_'| | dbms_rowid.rowid_block_number (rowid) | |'_'| | dbms_rowid.rowid_row_number (rowid) location from emp

EMPNO ENAME ROWID LOCATION

7369 SMITH AAAVREAAEAAAACXAAA 4_151_0

14 rows selected.

As can be seen from the above display, the value of the rowid pseudo column corresponding to the row record with an empno of 7369 is "AAAVREAAEAAAACXAAA", and the translated value of this column using the dbms_rowid package is 4 "151. 0. This table is the row record with an empno of 7369. The actual physical storage address is located in the 0th row of the 151st data block of file 4 (the record number of the data row in the data block starts from 0).

The value of the above rowid pseudo-column can be directly used in the where condition in the sql statement, which is the first of the two meanings of rowid scanning in oralce, which accesses the data row record directly according to the value of rowid entered by the user in the sql statement.

SQL > select empno,ename from emp where rowid='AAAVREAAEAAAACXAAL'

EMPNO ENAME

--

7900 JAMES

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.