The concept of Rowid:
rowid is a pseudo column. Since it is a pseudo column, this column is not defined by the user, but added by the system itself. There is a rowid pseudo column for each table, but the value of the ROWID column is not physically stored in the table. However, you can use it like any other column, but you cannot delete or change the column, nor can you modify or insert the value of the column. Once a row of data is inserted into the database, the rowid is unique during the life cycle of the row, that is, even if the row undergoes row migration, the rowid will not change.
Why use ROWID
rowid provides the fastest access method to a given row in a table. The corresponding data block can be directly located through ROWID and then read into memory. When we create an index, the index not only stores the value of the index column, but also stores the ROWID of the row corresponding to the index value. In this way, after we quickly find the ROWID of the corresponding row through the index, we can quickly query the data through the ROWID. . This is why it is faster when we use index queries.
In previous versions of ORACLE8, ROWID consisted of FILE, BLOCK, and ROW NUMBER. With the expansion of the object concept in Oracle8, ROWID has changed. ROWID consists of OBJECT, FILE, BLOCK, and ROW NUMBER. You can use DBMS_ROWID to decompose the rowid into the above parts, or you can combine the above parts into a valid rowid.
Recursive SQL concept Sometimes in order to execute a sql statement issued by the user, Oracle must execute some additional statements. We call these additional statements 'recursive calls' or 'recursive SQL statements'. For example, when a DDL statement is issued, ORACLE always implicitly issues some recursive SQL statements to modify the data dictionary information so that the user can successfully execute the DDL statement. Recursive calls often occur when the required data dictionary information is not in shared memory. These Recursive calls read the data dictionary information from the hard disk into memory. Users do not care about the execution of these recursive SQL statements. ORACLE will automatically execute these statements internally when needed. Of course, both DML statements and SELECT may cause recursive SQL. Simply put, we can think of triggers as recursive SQL.
Row Source
Used in queries, the set of qualifying rows returned by the previous operation can be a set of all row data in the table; it can also be a set of partial row data in the table; or it can be a set of the above two row sources. A collection of row data obtained after a connection operation (such as join connection).
Predicate
WHERE constraints in a query
Driving Table
This table is also called the outer table (OUTER TABLE). This concept is used in nested and HASH joins. If the row source returns more row data, it will have a negative impact on all subsequent operations. Note that although this is translated as driving table, it is actually more accurately translated as driving row source. Generally speaking, after applying query restrictions, the table with fewer row sources is returned as the driving table. Therefore, if a large table has restrictions (such as equality restrictions) in the WHERE condition, the large table will also be used as the driving table. Appropriate, so it is not that only smaller tables can be used as driving tables. The correct statement should be that after applying the query constraints, the table that returns fewer row sources is used as the driving table. In the execution plan, it should be the upper row source. Specific instructions will be given later. In our subsequent description, this table is generally referred to as row source 1 of the join operation.
Probed Table (probed table)
This table is also called the inner table (INNER TABLE). After we get a specific row of data from the driver table, we look for rows in the table that meet the join conditions. So the table should be a large table (actually it should be a table that returns a larger row source) and there should be indexes on the corresponding columns. In our subsequent description, this table is generally referred to as row source 2 of the join operation.
combined index (concatenated index)
An index composed of multiple columns, such as create index idx_emp on emp(col1, col2, col3, ...), then we call the idx_emp index a composite index. There is an important concept in the combined index: the leading column. In the above example, the col1 column is the leading column. When we make a query, we can use "where col1 = ?" or "where col1 = ? and col2 = ?". Such restrictions will use the index, but the "where col2 = ?" query will not use the index. . Therefore, only when the leading column is included in the restriction, the combined index will be used for the restriction.
Selectivity:
Comparing the number of unique keys in a column with the number of rows in the table determines the selectivity of the column. If the ratio of the "number of unique keys/number of rows in the table" of the column is closer to 1, the selectivity of the column is higher, the column is more suitable for creating an index, and the selectivity of the index is also higher. When querying on highly selectable columns, less data will be returned, so index queries are more suitable.
With this background knowledge in place, we begin to introduce the execution plan. In order to execute a statement, Oracle may have to implement many steps. Each of these steps might be physically retrieving the rows of data from the database or preparing them in some way for use by the user issuing the statement. The combination of these steps that Oracle uses to execute a statement is called an execution plan. The execution plan is the most complex and critical part of SQL optimization. Only by knowing how ORACLE executes the SQL statement internally can we know whether the execution plan selected by the optimizer is optimal. Execution plans are as important to DBAs as financial statements are to financial personnel. So the main problems we face are: how to get the execution plan; how to analyze the execution plan to find out the main problems affecting performance. The following will start with analyzing the tree execution plan, then introduce how to obtain the execution plan, and then introduce how to analyze the execution plan.
Example:
This example shows the execution plan for the following SQL statement.
SELECT ename, job, sal, dname
FROM emp, dept
WHERE emp.deptno = derpt.deptno
AND NOT EXISTS
(SELECT *
FROM salgrade
WHERE emp.sal BETWEEN losal AND hisal );
This statement queries the name, job, salary, and department name of all employees whose salary does not fall within any of the recommended salary ranges.
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/lcyhjx/archive/2009/12/20/5044672.aspx