What are the common index access methods in the Oracle execution plan?
Xiaobian to share with you what are the common index access methods in Oracle execution plan, I hope you have gained something after reading this article, let's discuss it together!
[SQL]
SELECT COUNT(*)
FROM FDC_DATA
WHERE EQPID = :B4
AND UNIT = :B3
AND PPID=:B2
AND SENSOR=:B1
AND EVENTTIME>SYSDATE-1/24
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.25 | 18125 |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.25 | 18125 |
|* 2 | INDEX FULL SCAN| FDC_DATA_IDX1 | 1 | 1 |00:00:00.25 | 18124 |
-----------------------------------------------------------------------------------
OLD INDEX：
FDC_DATA_IDX1 : SYSID, EQPID, UNIT, PPID, SENSOR, PRODUCT, EVENTTIME, SPEC
---------------------------------------------------------------------------------------------
SELECT COUNT(*),COUNT(DISTINCT(SYSID) SYSID_NDV,.................... FROM TABLE;
NDV(Number Of Distinct Value)
COUNT(*) SYSID_NDV EQPID_NDV UNIT_NDV PPID_NDV SENSOR_NDV GLASSID_NDV
--------- --------- --------- --------- --------- ---------- -----------
650430 100675 115 656 1515 2233 30139
NEW INDEX:
FDC_DATA_IDX1 : SYSID
FDC_DATA_IDX2 : EQPID, UNIT, PPID, EVENTTIME, SENSOR, GLASSID
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.001 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.001 | 3 |
|* 2 | INDEX RANGE SCAN| FDC_DATA_IDX2 | 1 | 1 |00:00:00.001 | 3 |
------------------------------------------------------------------------------------
Understanding: When creating an index
1. Analyze the growth of the data volume of the fields used in the application of SQL conditions and the subsequent fields;
2. To check the distinct quantity value of each field of table;
3. Theoretically, when building a composite index, fields with high distinct number values should be placed in the first place of the composite index because of high distribution;
4. If the number of distinct in a field is very high, it is not recommended to use a composite index with too many fields. It is recommended to establish a composite index with only a few fields.
Index Related Reference Link: www.xifenfei.com/2012/04/%e6%89%a7%e8%a1%8c%e8%ae%a1%e5%88%92%e4%b8%ad%e5%b8%b8%e8%a7%81index%e8%ae%bf%e9%97%ae%e6%96%b9%e5%bc%8f.html
http://www.xifenfei.com/2012/04/hint%e6%8c%87%e5%ae%9aindex%e7%9a%84%e6%b7%b1%e5%85%a5%e7%90%86%e8%a7%a3.html
index range scan:
1. For unique index, if the where condition is followed by,between... and... index range scan, if the where condition is followed by =, then index unique scan will be performed.
2. For none unique index, if the where condition follows =,>,
