Creating an oracle index is far behind

I have several Oracle stored procedures, two of which involve recursive operations, which are relatively time-consuming. These Oracle databasesProgramI gave them numbers P1, P2, P3, P4, and P5. P2 and P5 have complex recursive operations, involving four tables: T1, T2, t3, T4. There are also some basic tables.

First, there is no data in the basic table T1, T2, T3, and T4.

The data structures of T1, T2, T3, and T4 are basically the same,
P1 calculates the data in the base database according to the rules of different categories (six categories) and saves the results to T1.
P2 performs recursive operations on data in T1 according to different types. Store results in T2
P3 indicates that the unique numbers of different classes in T1 are the same (Interpretation: each type of data has a unique number that corresponds to other classes, that is, if Category 1 has a unique number of 10020, Category 2, category 3, category 4, Category 5, and category 6 also have a corresponding unique number of 10020 records) summarize the data. The result is stored in T3. Data in T3. Because it is summarized.
P4 summarizes the data in T2,AlgorithmSimilar to P3, P4 only summarizes data that has undergone recursive operations. The final result is stored in T4, which is the final result.
P5 performs recursive operations on the summarized data of T3. The algorithm is similar to P2. However, only one recursion is performed at this time. P2 recursion is performed on each category. The final result is the same as that of the P4 operation theoretically. That is, the final result (T4) can be obtained from the basic data through two paths ).

First, I created 3-4 indexes in four tables: Unique Numbers (ID) and p_id (explanation: p_id and unique number (ID) create an index for each of the more complex tree structures. The other two fields used for condition restriction are also indexed.

Test the five operations. Result:

Program execution schedule

	Max (seconds)	Min (seconds)	AVG (seconds)
P1	67	19	20
P2	101	67	70
P3	6	2.5	3
P4	6	2.5	3.5
P5	19	8	10

Then I deleted the indexes on the two fields used as the condition limitation, leaving only the indexes on the ID and p_id. Another test was conducted. Result:

Program execution schedule

	Max (seconds)	Min (seconds)	AVG (seconds)
P1	16	12	14
P2	66	54	58
P3	1.6	1.3	1.4
P4	1.5	1.3	1.4
P5	10.8	8.6	9

It can be seen that the performance improvement of P5 is only 10%. The other improvements are all over 20%.

The index we created improves the query performance, but reduces the performance when inserting or modifying or deleting data. That is, if the index is too large, the performance may not be improved. So far. Therefore, when creating an index, we need to create an index on the corresponding field or field combination based on the specific needs and specific circumstances to ensure the highest performance return.