Optimization process of the next SQL statement in Oracle (more detailed)

The original statement is as follows:

Select sum (sl0000) from xstfxps2 where
Dhao00 in (
Select dhao00 from xstfxps1 where trunc (ywrq00) = trunc (sysdate)
And khdm00 = '000000 ');
Time in use: 00: 02: 49.04

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose
1 0 sort (aggregate)
2 1 nested loops
3 2 Table Access (full) of 'xstfxps2'
4 2 Table Access (by index rowid) of 'xstfxps1'
5 4 index (unique scan) of 'xstfxps1 _ pK' (unique)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
17355138 consistent gets
34141 physical reads
2912 redo size
198 bytes sent via SQL * Net to client
275 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed
We can see that 17355138 logical reads and 34141 physical Io are performed in the statistics, which is quite scary. In the execution plan, we can see that xstfxps2 has a full table scan.
First, let's take a look at the total data volume of the two tables:
SQL> select count (*) from xstfxps2;

Count (*)
----------
5585018
Here we can see that xstfxps2 has 5585018 records.
SQL> select count (*) from xstfxps1;

Count (*)
----------
702121
The table structure of the two tables is as follows:
SQL> DESC xstfxps1
Name type nullable default comments
-----------------------------------------
Dhao00 number (8)
Lhdh00 number (8) y
Fldh00 number (8) y
Fplb00 varchar2 (2) y
Ywrq00 date y
Ywry00 varchar2 (8) y
Shrq00 date y
Xsqrrq date y
Xsqrry varchar2 (8) y
Khdm00 varchar2 (12)
Xkzh00 varchar2 (12)
Ckdm00 varchar2 (2) y
Thckdm varchar2 (2) y
Xsfsdm varchar2 (2) y
Fxrydm varchar2 (4) y
Shrydm varchar2 (4) y
Shbj00 varchar2 (1) 'n'
Fxbj00 varchar2 (1) 'n'
Skbj00 varchar2 (2) y
Fkdm00 varchar2 (2) y

SQL> DESC xstfxps2
Name type nullable default comments
-----------------------------------------
Dhao00 number (8)
Spdm00 varchar2 (8)
Djia00 number (7,2) 0
Fxsl00 number Y 0
Sl0000 number Y 0
Thsl00 number Y 0
Je0000 number Y 0
Se0000 number y
Fpbbh0 varchar2 (11) y
Fphao0 varchar2 (10) y
Rbdh00 number (8) y

The header of the customer order in xstfxps1 stores the customer information and order date of the order. Xstfxps2 is the table body of the Order, which records the product, price, quantity, and other information of the customer's order in detail.

The first step of adjustment is to extract the subquery and then look at the statement execution plan. Generally, if the statement can avoid the use of subqueries, try not to use subqueries. Because the sub-query overhead is quite expensive. The modified statement is as follows:
Select sum (sl0000)
From xstfxps2 A, (select dhao00 from xstfxps1 where trunc (ywrq00) = trunc (sysdate)
And khdm00 = '000000') B
Where a. dhao00 = B. dhao00;
Used time: 00: 00: 03.05
Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose
1 0 sort (aggregate)
2 1 Table Access (by index rowid) of 'xstfxps2'
3 2 nested loops
4 3 Table Access (full) of 'xstfxps1'
5 3 index (range scan) of 'xstfxps2 _ pK' (unique)
Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
11974 consistent gets
225 physical reads
832 redo size
211 bytes sent via SQL * Net to client
275 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

We can see that the logic Io dropped from the original 17355138 times to 11974 times, with an increase of magnitude. The execution time also drops from nearly three minutes to more than three seconds. Obviously, the performance has been greatly improved. However, we can see that the xstfxps1 table in the execution plan still has a full table scan. In general, we should try our best to avoid the existence of full table scanning, especially for large tables, we should create appropriate indexes to avoid the generation of FTS. Let's take a look at the index information of the two tables:

Select index_name, column_name from dba_ind_columns where table_name like 'xstfxps %'
Index_name column_name
-----------------------------------------------------------------
Xstfxps1_pk dhao00
Xstfxps2_pk dhao00
Xstfxps2_pk spdm00

We can see that the two tables do not have any other indexes except the primary key constraints. Based on the statement query, we have created the following composite index:
Create index idx_xstfxps1_khdm00_ywrq00 on xstfxps1 (khdm00, ywrq00) tablespace indx;

To use indexes, we must adjust the conditions of the original date field. Because a trunc () function exists, the statement will not use the index. We only need to understand that trunc (ywrq00) = trunc (sysdate) is actually equivalent to ywrq00 greater than trunc (sysdate), less than trunc (sysdate + 1) minus one second, we have a better solution.
This condition. The modified statement is as follows:
Select sum (sl0000)
From xstfxps2 A, xstfxps1 B
Where a. dhao00 = B. dhao00
And B. khdm00 = '000000'
And B. ywrq00 between trunc (sysdate)
And trunc (sysdate) + 1-1/(24*60*60 );
Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose
1 0 sort (aggregate)
2 1 Table Access (by index rowid) of 'xstfxps2'
3 2 nested loops
4 3 Table Access (by index rowid) of 'xstfxps1'
5 4 index (range scan) of 'idx _ xstfxps1_khdm00_ywrq00'
NON-UNIQUE)

6 3 index (range scan) of 'xstfxps2 _ pK' (unique)
Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
3 consistent gets
0 physical reads
0 redo size
210 bytes sent via SQL * Net to client
275 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed
At this time, we can see that the logic Io has been reduced to three times, and the statement execution plan is also in line with our adjustment goal. The created index has produced a relatively large effect. The adjustment of this statement has come to an end.