Shulou(Shulou.com)05/31 Report--

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 =,>,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.