Ways to access data

For the optimizer, it is critical to parse the target SQL and get the execution plan, which is the method that determines the access to the data, that is, the way the optimizer decides how and how 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 that Oracle accesses the data in the tables: one directly accesses the table, the other is to access the index first, and, of course, if the target SQL has access to the data only by accessing the relevant index, then the table is not needed at this time.

There are two ways to directly position data in an Oracle database: One is a full table scan, but a ROWID scan

1. Full table Scan

Full scan means that when Oracle accesses the data in the target table, it starts scanning from the first block (block) of the first area occupied by the table, scanning to the table's high watermark (HWM), which all of the data blocks in the range of Oracle must read. Of course, Oracle imposes the conditions specified in the Where condition of the target SQL for all data read during this period, and finally returns only those data that satisfies the filter criteria.

Not a full table scan is not good, in fact, Oracle in the full table scan operation will use a multi-block read, which is very high when the data volume of the target table is very small, but the biggest problem with full table scan is the execution time of the target SQL that goes full table scan is not stable, not controllable, This execution time must be incremented as the amount of data in the target table increases. Because as the target table data increases, its high watermark continues to rise, so the number of data blocks that need to be read when the table is scanned is increasing, which means that the amount of I/O resources required to scan the table is increasing, and the amount of time it takes to complete a full scan of the table will increase as well. In addition, for the CBO, the constant increase in I/O resources means that the cost value of the full table scan increases as the amount of data in the target table increases.

In Oracle, if you are inserting data into the target table, the high watermark moves up when there is not enough existing space assigned to the table, but if you delete the data from the table with a DELETE statement, the high watermark does not move down with it. The negative effect of this characteristic of the high watermark is that even if all data is deleted using the DELETE statement, the high watermark is still in its original position, which means that the full table scans the table with all the data blocks under the table's high watermark. Therefore, the time spent on the table's full table scan will not be improved as compared to the previous one.

2.ROWID Scanning

ROWID scanning is when Oracle accesses the data in the target table and locates and accesses the data directly through the ROWID of the data. ROWID represents the physical storage address of the Oracle data row record, which means that rowid is actually corresponding to the row record one by one in the data block in Oracle.

Since ROWID represents the physical storage address where the table's data rows reside, when Oracle knows the ROWID of data rows to be accessed, it is natural to have direct access to the relevant data rows of the corresponding table based on ROWID, which is the definition of ROWID.

In strict terms, the ROWID scan in Oracle has two meanings: one is to directly access the corresponding data row records based on the value of the user entering ROWID in the SQL statement, the other is to access the relevant index first, and then to access the corresponding row records based on the ROWID obtained after accessing the index.

For the Oralce heap table, we can get the value of the rowid of the corresponding row record by the built-in ROWID and shoddy (note that this rowid is only a fake, the column does not exist in the actual table block) and then we can pass the relevant method in the Dbms_rowid package (dbms_ Rowid.rowid_relative_fno,dbms_rowid.rowid_block_number and Dbms_rowid.rowid_row_number) translate the value of the pseudo-column above into the actual physical storage address of the corresponding data row.

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

Rows selected.

As you can see from the above display, the value of the ROWID pseudo-column corresponding to the row record for Empno 7369 is "aaavreaaeaaaacxaaa", and the value of the column translated with Dbms_rowid package is 4_151_ 0, this table is the row record of the Empno 7369, the actual physical storage address is located in the 151th data block of the 4th file, the No. 0 row of records (data block data row record number starting from 0)

The value of the ROWID pseudo-column can be used directly in the Where condition in the SQL statement, which is the first of the two meanings of the ROWID scan in Oralce, which accesses the data row records directly based on the value of the ROWID that the user enters in the SQL statement.

Sql> Select Empno,ename from emp where rowid= ' Aaavreaaeaaaacxaal ';

EMPNO ename

---------- ----------

7900 JAMES

Ways to access data