oracle explain an implementation plan. Related Concepts
1 · rowid, pseudo-column: is the system to add their own, each table has a pseudo-column is not physically exist. It can not be modified, deleted, and added, rowid in the life cycle of the line only if inserted into a column to the database, would lead to changes in the line, but the rowid does not change.
2 · recursive sql concept: When the user perform some SQL statement, it will automatically perform some additional statements, we have these additional SQL statements called "recursive calls" or "recursive sql statement", when executing a DDL statement , Oracle always implied issue some Recursiv sql statement used to modify the data dictionary, if the data dictionary is not in shared memory, then, for the implementation of "resursive calls", it will read the data dictionary from the physical to the shared memory. Of course, DML and select statements may cause recursive SQL.
3 · row source line source: in the query from the previous operation returns of eligible data sets, it may be the whole table, it may be some, of course, can also be connected to the two operating tables (join) the resulting data set
4 · predicate: a query where restrictions
5 · driving table-driven table: The table has become the outer table, the gratitude for embedded and HASH connection, if the data is returned greater negative impact and return rows of data-driven form suitable for smaller
6 · probed table is probing the table: The table is also called the inner table, we get a table in the outer layer of the data found in the table to find the conditions for connecting the line.
7 composite index (concatenated index) index by the number of columns in the composite index, there is an important concept, is to guide index
create index idx_tab on tab (col1, col2, col3),
indx_tab is called the composite index,
col1 column is called the boot
Where in the query, you must use the guide index, will use the composite index
8. Selectivity (selectivity) compared the only key to one of the following number and the number of rows in the table, we can determine the selectivity of the column. If the column "the number of unique key / number of rows in the table," the ratio closer to 1, the higher the selectivity of the column, the column is more suitable for creating an index, as the higher selectivity index . In the optional column on the high query, the returned data to fewer, more suitable for use references.
II. Oracle access method to access data
1. Full table scan (Full tabel scans, FTS)
In order to achieve full table scan, Oracle reads every row in the database and check whether each line to meet the limits where the statement read for more than a block, you can make io can read the multi-block data block. IO reduced the number and improve the system throughput. Multi-block method in the use of reading, you can efficiently implement a database full table scan, and, in the case of full table scan, you can use the multi-block read in the method. In this kind of access mode, data blocks read only once.
Use of FTS on the premise that, in the larger table, do not recommend the use of FTS, unless the data out more than 5% -10% of the total, or use the parallel query
2. Access by rowid of the table
ROWID pointing to the line of the bank's data files, data blocks, and the location of the data block, using the rowid can be made quickly Navigate to the data line, in Oracle, this is the quickest way to obtain a single line.
The access method does not use multi-block read, a IO can only read a data block.
3. Index scan (index scan and index lookup)
Index scan through the index to find the corresponding row rowid, rowid from the database and then get specific data. The method consists of two steps,
(1) Scan the index obtained by rowid
Description: The index is more than storing the index value, the line is also stored rowid
(2) get data in the table rowid
1. Because the index often used, so most of them are Cache into memory, so the first step is usually the logical IO, the data can be obtained from memory
2. But the second step, if the data is relatively large, can not be stored in memory, so is a physical operation, is extremely time consuming and, therefore, from the big table
The index scan, if the number is greater than 5% -10% of the total, then the effect will drop a lot of
3. If the index data can be found in memory to avoid the second step operation, to avoid unnecessary IO. Effectiveness will be high.
4. If the SQL statement will sort the index because the index has already lined up a pre-order, the index is not required in the implementation plan in the index order. Where the index type and the different constraints, there are 4 types of index data
1. Unique index (index unique scan)
Find a value through a unique index, usually the rowid, if the table exists in unique, primary key, or if, Oracle usually achieve a unique index;
2. Index range scan (index range scan)
If you want to get multiple rows of data, usually in the range of unique index on the combined operations, such as (>,<)
* Use index rang scan of the three cases
(A) where the unique conditions on the index screen
(B) in the composite index, use the query part of the column, resulting in a multi-line query
(C) non-unique index columns of any query
3. Index full scan (index full scan)
Corresponds with the full table scan is to scan the whole index, it is necessary to ensure that the data to obtain direct access from the index
Index BE_IX is a concatenated index on big_emp (empno, ename)
SQL> explain plan for select empno, ename from big_emp order by empno, ename;
4. Index quick scan (index fash full scan)
Index fast scan and index full scan is similar to the query does not sort out the data.
III. Form the connection between
According to row source connected to different conditions, can be divided into equivalent connection (where a.col3 = b.col4) non-equivalent connections (where a.col3> b.col4) outer join (where a.col3 = b.col4 ( +))
1. A typical type of connection
(A) sort - merge join (sort merge join, SMJ)
(B) nested loops (nested loops, NL)
(C) hash join (hash join,)
A. sort - merge join
1) First row source1 generate the data needed, so after sorting out the associated connection.
2) then generate row source2 data needed, and then the data in accordance with the row socurce1 associated with the column corresponding to the sort operation.
3) on both sides of the sorted data merge operation, the two row source connected in accordance with the join condition.
*. If the row source has been associated with the above sort, that sort join operation does not perform operations will greatly improve efficiency, because the sort is extremely resource-consuming, pre-sorted row source, including the index, or already good order of the columns.
*. Sort is very time-consuming operation, especially the large table, for this reason, SMJ is usually not a good solution, if you already do that sort of work will greatly improve efficiency
*. For non-equivalent connection, the efficiency of such a connection is relatively high.
B. nested loops.
In connection with the concept of table-driven, in fact, the connection process is a two layer nested loop, the outer table as possible.
[Implementation of the principle]
From within the table view, need to be the outer table of each line, to match all the rows within the table, so to maintain the external table as small as possible, and efficient access to internal tables, the key is connectivity.
nested loops can return to the line is connected, without having to wait all the lines connecting end, so they can respond quickly, particularly the need for rapid response with the statement.
Smaller row source used to build hash table and bitmap, and the second to be hansed, and with the first generation of the hash table to match.
bitmap used as a rapid method for the query, hash table used to check whether there are matching rows, especially in the case of a large table, you can not fit in memory, the connection method is very effective. This link has driven the concept of the table, there to construct a hash table, bitmap table is called table-driven, if constructed hash table, bitmap can hold in memory, the efficiency is very high.
1) make the hash join effective, need to set HASH_JOIN_ENABLED = TRUE, by default the parameter is TRUE, also, do not forget to also set hash_area_size parameters for efficient operation of the hash join, because the hash join in the parameter specifies the amount of memory to run, too small, the performance parameters will hash join even lower than other connections.
2) can only be used for equivalent connections.
3) In the two larger connections between the row source will have a relatively good efficiency, small in a row source is able to achieve better efficiency.
Interpret the results:
1.AND-EQUAL the step has two or more sub-steps, each sub-step back a series of ROWID.AND-EQUAL operation of choice is the index sub-operation to return the rowid
conversoin to rowids - bitmap convert from a bitmap index can be used to extract the actual data series of ROWID
conversoin from rowids - ROWID into a series of bitmap representation
conversoin count - the number of rows of the bitmap statistical
index single scan - to extract a single bitmap index
index range scan - the returned bitmap is a range of key values
index full scan - scanning the entire bitmap index
MERGE - Merge two bit map, and returns the result as a bitmap
Minus - The operation is the opposite of merge operations, and can have two or three sub-operations and returns a bitmap
Operation returns the first child of a bitmap as a starting point, the first minus the second bitmap bitmap provide all lines. If the second bitmap empty, then all null columns will also be subtracted.
or - the two bitmaps as input
3.connect by - layer extraction line, because the query uses the CONNECT BY C clause
4.concatenaction - merge multiple rows set one line.
5.count - calculated from the table the number of rows selected
stopkey - calculate the number of rows to be in the query is limited where the rownum
6.filter - a series of rows as input to the where clause and filter the result query
7.first row - extract the first row of query result sets
8.for udpate - for the extraction of the line lock.
9.hash join - use the connection method to connect two hash tables
unique - a unique value from the index to find
range scan - to look from a certain range, is the way in ascending order
range scans desc - scan line within a certain range, but it is the way in descending order
11.inlist iterator - the predicate for a value in the implementation of one or multiple operations
13 intersection - the two result sets into one set, and returns in their common values between
14 merger join - provide a common value, used to connect the results of the two result sets. Is the connection
output - External
anit - Anti-
seml - half
15 nested loops - the operation involves two sub-operation, the first operation of a return a row set, is used for each row in the rowset, the implementation of the second sub-operation.
output - for the implementation of an external set of nested loops
16 partition [partition] - one or more partitions perform operations, partition-start, partition-stop will provide a range of partition
singnal - display operation will be performed on a single partition
iterator - performed in a number of partitions
all - display operation will be performed on all partitions
inlist - display operation performed on the partition and drive with the IN predicate
17 projection - using multiple queries as input and returns a single record set. Regular and intersection, minus, unqie use
aggregate - the line set on the use of group function
unique - sort of line set, remove the duplicate rows
join - and the same merger
group up - Packet Sorting
orday by - sort by orday by
19 table access
full - show all the rows in the specified table
cluster - with a particular key to match all lines
hash - hash
by rowid - Display oracle table extracted from the rowid
20 unqie shows two sets, and remove duplicates
21 view generated view, and returns the result set rows.