An example of index usage prompt for Oracle SQL Optimization

A ORA-01555 error was found during database security check:

This SQL statement is obviously not completed after running for a long time. In the Statspack report, this SQL statement is also on top.
SQL occupies a large amount of db cache. Large physical reads.

After completing other tasks in the afternoon, I plan to optimize the SQL statement.
First, check the SQL Execution Plan.
Run the SQL statement in the execution plan window of PL/SQL Developer and get the execution plan:

We can see that the prompt/* + all_rows */is used in the nested query (this is my fault, because during the last five days, I found that a similar nested statement is the same as another statement. I used this/* + all_rows */prompt to optimize it, the developer thinks that the statement in the first figure should also be added with this prompt. The result is that this statement has a problem today .)
The Person table performs full index scan, which is a little inefficient, but worse, the mailsend table performs full table Scan Based on the conditions in the statement.
Select * from mailsend MS whereMs. personid = p. useridAnd (sysdate-15) <= ms. senddate and ms. mailid = 1102
From the execution plan, we can see that secondary queries do not use indexes. In dba_indexs, check whether the mailsend table has indexes.
Select * from dba_indexs I where I. table_name = 'mailsend'
No index.
Therefore, create an index:
Create index idx_perid_mailsend on mailsend (personid );
At the same time, this table is analyzed.
Analyzed table mailsend compute statistics;
The keyword "in" is still used in the SQL statement. in the query, it is best to replace "in" with "exists" to improve the performance.
The modified SQL statement is as follows:

 

Take a look at the execution plan:
 

At this time, the whole table scan of the mailsend table is solved, but the outermost layer of the person table is still the full table scan (although the inner layer is the primary key index scan), which is very important, because update needs to update the result set of the inner layer, it takes a full table scan and does not use indexes. This is obviously a slow reason.
At this time, you can view the related indexes of person. There are only two composite indexes.
At this time, I remembered that I could use the prompt to force the INDEX execution. So I added an INDEX prompt/* + INDEX (tablename indexname) */(syntax)
The Modification result is as follows:
Update/* + INDEX (per INDEX3_PERSON) */person per
Set per. sort = nvl (per. sort, 0) + 1
Where exists
(Select userid
From (select
P. userid, p. email
From Person p
Where (sysdate-p. lastupdate)> =
(P. lastupdate + 3-p. lastupdate)
And p. status = 3
And p. sort! = 3
And not exists (select *
From mailsend MS
Where ms. personid = p. userid
And (sysdate-15) <= ms. senddate
And ms. mailid = 1102) us where us. userid = per. userid)

Let's take a look at the execution plan:

IO consumption reduced to 66.
This article is complete.