One, how to view the execution plan of SQL statement
1, in the Sql*plus environmentPerform set AUTOTRACE on to turn on startup auto-tracking, as follows (the red font below is the typed command):chenzw>
set Autotrace onChenzw>
select * from EMP; empno ename job mgr HireDate sal COMM DEPTNO--------------------- -------------------------------------------------------------- 7369 SMITH cle RK 7902 1 July-December -80 800 &NB Sp 20 7499 ALLEN salesman &NBS P 7698 20月-February -81 300 30 7521 WARD salesman 7698 2 February-February -81 &NBSP ; 1250 500 30 7566 JONES &NB Sp managER 7839 February-April -81 2975 & nbsp 20 7654 MARTIN salesman 7698 2 August-September -81 1250 1400 30 7698 BLAKE manager 7839 January-May -81 2850 30 7782 CLARK &NBSP ; MANAGER 7839 September-June -81 2450 &NB Sp 10 7788 SCOTT analyst &NBSP ; 7566 1 September-April -87 $ &NBS p;20 7839 KING President 17-11 month -81 &N Bsp 10 7844 TURNER salesman 7698 August-September -81 &N Bsp 0 30 7876 ADAMS clerk &NBSP ; 7788 2 March-May -87 1100 &NBS P 20 7900 JAMES clerk 7698 March-December-81 950  30&NBSP ; 7902 FORD ANALYST 7566 March-December -81 &NBSP ; 20 7934 MILLER Clerk 7782 2 March-January -82 1300 &NBS P 10 14 lines have been selected. execution plans----------------------------------------------------------plan hash value:3956160932 ------ --------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | |  14 | 518 | 3 (0) | 00:00:01 | | 1 | table ACCESS full| EMP |  14 | 518 | 3 (0) | 00:00:01 |-------------------------------------------------------------------------- statistical information---------------------------------------------------------- 476 Recursive calls 0 db block gets 90 consiste NT gets 9 physical reads 0 redo size 1423 bytes sent via sql*net to client 416 bytes received via S Ql*net from client 2 sql*net roundtrips to/from client 7 sorts (memory) 0 sorts (disk)  14 & Nbsp;rows processed Like
describeOrders can be saved slightly
descThe same
set Autotrace offOrders can also be saved slightly.
set Autot off。 If the rows in the execution plan are too wide, you can set the line width to 200 by executing the following command:chenzw>
Set LinesizeChenzw>
2. In PL/SQL developer softwareIn the PL/SQL developer window, click "File" in the "New" menu and select "Windows" to enter the SQL you want to execute, such as the above
SELECT * from EMP;Then click on the "F5" button, the system will automatically pop up the "Explain the plan window", and the execution plan of the SQL will be displayed in the "Interpretation plan window" or directly Create "Interpretation plan Window" can also enter the Interpretation Plan window. Now look back at this SQL statement, using the "
TABLE ACCESS full"How does the optimizer use a full table query?" There are several cases in which the database selects the full table index. First, when the concurrency is high, if the
Dba_tablesInside the View
degree (the number of threads per instance for scanning the table)When the value of a field is higher, the optimizer chooses a full table scan. Second, on the table, if there is no index, the so-called "paddle", the optimizer can only select a full table scan
Full TABLE SCANWay out of it. Third, when querying the large data volume, such as the above SQL statement, the optimizer looks at the back of a where condition is not, or the optimizer thinks that the query will query to most of the data, then use a full table scan, in this case, even if the SQL can use the index, it will not be used. The fourth type, when querying the small standard, in the initialization parameters there is a
Db_file_multiblock_read_count, this parameter specifies the number of blocks per read, but if you find that the table currently being queried is less than the value of the parameter, the optimizer selects the full table scan.
ii. Several common index scan types for SQL statements
First, there is a concept that, in addition to getting the value of the index itself, using the index will ultimately use the table's
ROWIDTo get the data. That is
TABLE ACCESS by ROWID。 What is ROWID? The following query: chenzw>
Select t.rowid,t.* from emp t; rowid empno ename job MGR hiredate sal comm DEPTNO--------------------------------------------------------------------aaaq+ JAAEAAAAAEAAA 7369 SMITH clerk 7902 1 July-December -80 800  20AAAQ+JAAEAA Aaaeaab 7499 ALLEN salesman 7698 20月-February -81  30AAAQ+JAAEAAAAAEAAC 7521 WARD S Alesman 7698 2 February-February -81 1250 30aaaq+jaaeaaaaaeaad 7566 JONES manager 7839 February-April-81 2975  20AAAQ+JAAEAAAAAEAAE 7654 MARTIN salesman 7698 2 August-September -81 1250 1400 30aaaq+ja Aeaaaaaeaaf 7698 BLAKE manager 7839 January-May -81 2850 30aaaq+jaaeaaaaaeaag   ; 7782 CLARK manager 7839 September-June -81 2450 10aaaq+jaaeaaaaaeaah 7788 SCOTT   ; ANALYST 7566 1 September-April -87  20AAAQ+JAAEAAAAAEAAI 7839 KING President 1 July-November -81, &NBSP ; 10aaaq+jaaeaaaaaeaaj 7844 TURNER salesman 7698 August-September -81 0 30aaaq+jaaeaaaaaeaak   ; 7876 ADAMS clerk 7788 2 March-May -87 1100 20aaaq+jaaeaaaaaeaal 7900 JAMES clerk 7698 March-December -81 950  30AAAQ+JAAEAAAAAEAAM 7902 FORD A Nalyst 7566 March-December -81 20aaaq+jaaeaaaaaeaan 7934 MILLER clerk 7782 2 March-January -82 1300 10 rowid field holds a lot of information, such as the data block, the data file, the information in the data block, etc. It is very convenient to find the location of the record through ROWID information, and in Oracle, using ROWID to find a record is the fastest. When using an index, rather than querying the value of the index itself (for example, a primary key), but rather querying other field information for the row where the primary key is located, Oracle is also the first to get the ROWID information, and then to find the information for the other fields and return. Find some of the most common types of scanning on the Web: the first kind:
Index Unique ScanIndex unique scan, the optimizer chooses this type of scan when it is possible for the optimizer to discover that a query condition can take advantage of a primary key, a unique key, a column with a foreign key constraint, or simply access the data where the index of a row is located. The second type:
Index Range ScanIndex range scanning, when the optimizer finds that the use of greater than, less than, greater than equals, less than equals, and between on a unique column will use range scanning, only partial queries on the combined columns, resulting in multiple rows of data being queried. Any activity on a non-unique indexed column will be used
Index Range Scan。 The third type:
Index Full ScanFull index scans, which use full index scans if the data to be queried can be obtained from all indexes. The fourth type:
index fast full scanIndex quick Scan, scan all the data block in the index, and the way of full index scan is basically similar. The obvious difference between the two is that the index quick scan does not sort the data for the query, and the data is not sorted when it is returned. "In this access method, you can use the multi-block read function, or you can use parallel read-in, resulting in maximum throughput and reduced execution time."
three, several common types of index simulationSql:create table TBL that creates the test table and test data (USERID number, NAME VARCHAR2, DEPTNO number (20));--analog data begin for I In 0..1000 LOOP inserts into TBL VALUES (i,concat (' TBL ', I), MOD (i,2)); END LOOP; END; SELECT * from TBL; 1)
TABLE ACCESS by USER ROWIDExecute the following statement to get several rowid:chenzw> select rowid,tbl.* from TBL where Rownum<10; rowid userid NAME DEPTNO ----------------------------------------------------------aaauhaaaeaaaaicaaa 796 TBL796 & nbsp 0aaauhaaaeaaaaicaab &NBSP 797 TBL797 &NBSP;1AAAUHAAAEAAAAICAAC 798 TBL798 &NBSP;0AAAUHAA Aeaaaaicaad 799 TBL799 &N Bsp 1aaauhaaaeaaaaicaae 800 TBL800 &N Bsp &NBSP;0AAAUHAAAEAAAAICAAF 801 TBL801 & nbsp 1aaauhaaaeaaaaicaag 802 TBL802 &N Bsp 0aaauhaaaeaaaaicaah 803 TBL803 &N Bsp &NBSP;1AAAUHAAAEAAAAICAAI 804 TBL804 &N Bsp 0 then take rowid to check the execution plan at this time: Chenzw> select * From tbl where rowid = ' aaauhaaaeaaaaicaaa '; execution plan----------------------------------------------------------plan hash value:2623095911 -----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | &NBSP;50 | 1 (0) | 00:00:01 | | 1 | table ACCESS by USER rowid| TBL | 1 | &NBSP;50 | 1 (0) | 00:00:01 |-----------------------------------------------------------------------------------above Table ACCESS by USER rowID is one of the most efficient ways to get data. &NBSP;2)
Index Unique scan Modifying the table above, adding a primary key, equals automatically adding a unique key constraint: ALTER table TBL add CONSTRAINT tblid PRIMARY key (USERID); Execute the following SQL statement:chenzw> SELECT * from tbl where userid = 100; plan hash value for execution plans----------------------------------------------------------: 1167568666 -------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | &NBSP;38 | 2 (0) | 00:00:01 | | 1 | table ACCESS by INDEX rowid| TBL | 1 | &NBSP;38 | 2 (0) | 00:00:01 | | * 2 | INDEX UNIQUE SCAN | Tblid | 1 | | 1 (0) | 00:00:01 |------------------------------------------------------------------------------------- As you can see from the execution plan above, the optimizer first finds 100 of the ROWID based on the index tblid that was just created, and then finds the row data for 100 based on ROWID. &NBSP;2)
Index Full ScanAdd a federated index to the table above, and on the Tbl.name and tbl.deptno two columns, SQL is as follows: CREATE INDEX Index_tbl_name_deptno on TBL (NAME,DEPTNO); 3)
Index Range ScanExecute the following statement: Chenzw> select * from TBL where userid between and 100, 91 rows have been selected. Execution plans----------------------------------------------------------Plan hash value:2314926374 ------------------ -------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | &NBSP;91 | 3458 | 3 (0) | 00:00:01 | | 1 | table ACCESS by INDEX rowid| TBL | &NBSP;91 | 3458 | 3 (0) | 00:00:01 | | * 2 | INDEX RANGE SCAN | Tblid | &NBSP;91 | | 2 (0) | 00:00:01 |-------------------------------------------------------------------------------------use greater than equals, less than equals, between, etc. on an index column, the optimizer first operates on the index column, and after an index range scan has been rowid, Then find the corresponding data according to the ROWID. There is a very interesting question in this: Chenzw> select * from TBL where userid between and the order by 1, 91 rows have been selected. Execution plans----------------------------------------------------------Plan hash value:2314926374 ------------------ -------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | &NBSP;91 | 3458 | 3 (0) | 00:00:01 | | 1 | table ACCESS by INDEX rowid| TBL | &NBSP;91 | 3458 | 3 (0) | 00:00:01 | | * 2 | INDEX RANGE SCAN | Tblid | &NBSP;91 | | 2 (0) | 00:00:01 |-------------------------------------------------------------------------------------look at the execution plan, We have specified in the SQL statement that we need to sort the result set, but in the execution plan, there is no sort of action, what is this? Chenzw> select * from TBL where userid between and the order by 1 DESC; 91 rows have been selected. Execution plans----------------------------------------------------------Plan hash value:4193471386 ------------------ --------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | &NBSP;91 | 3458 | 3 (0) | 00:00:01 | | 1 | table ACCESS by INDEX ROWID | TBL | &Nbsp;91 | 3458 | 3 (0) | 00:00:01 | | * 2 | INDEX RANGE SCAN descending| Tblid | &NBSP;91 | | 2 (0) | 00:00:01 |-------------------------------------------------------------------------------------- What is the difference between this implementation plan and the above implementation plan? The first execution plan is an index range scan, but the second execution plan is the index range descending scan (index ranges scan descending). From this you can see that for a unique index, Oracle is already in a positive order. That is, the unique index is not very expensive when it is sorted, because it is stored in ascending order when it is saved. &NBSP;4)
index fast full scanIf we only need to get all the UserID, how will the optimizer make the query? The userid we're looking for is all in the index, execute the following statement, get the following execution plan:chenzw> select UserID from tbl; 100001 rows selected. Execution plans----------------------------------------------------------Plan hash value:3798421822------------------------ ------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 115k| 1471k| 63 (2) | 00:00:01 | | 1 | INDEX FAST Full scan| Tblid | 115k| 1471k| 63 (2) | 00:00:01 |------------------------------------------------------------------------------See the above execution plan is an index quick Scan (index FAST full SCAN), because all the data needed is in the index, so it's OK to just take the required data out of the index.
Several common types of index scans
