Optimization process for Oracle's next SQL statement (more detail) _oracle

The original statement is this:

Select SUM (sl0000) from XSTFXPS2 where
Dhao00 in (
Select dhao00 from Xstfxps1 where Trunc (ywrq00) =trunc (sysdate)
and khdm00= ' 500000003913 ');
Time used: 00:02:49.04
　　
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=choose
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (full) ' XSTFXPS2 '
4 2 TABLE ACCESS (by INDEX ROWID) of ' XSTFXPS1 '
5 4 INDEX (unique SCAN) of ' xstfxps1_pk ' (unique)
　　
Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
17355138 consistent gets
34141 Physical Reads
2912 Redo Size
198 bytes sent via sql*net to client
275 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed
We saw 17,355,138 logical readings and 34,141 physical IO in the statistics, which is a pretty scary number. In the execution plan we see the table XSTFXPS2 a full table scan.
Let's first take a look at the total amount of data in both tables:
Sql> Select COUNT (*) from XSTFXPS2;
　　
COUNT (*)
----------
5585018
We see here XSTFXPS2 this table has 5,585,018 records.
Sql> Select COUNT (*) from XSTFXPS1;
　　
COUNT (*)
----------
702121
The table structure of both tables looks like this:
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 (one) Y
FPHAO0 VARCHAR2 (Y)
RBDH00 Number (8) Y
　　
The header of the XSTFXPS1 customer order, the customer information of the order, the order date and so on. XSTFXPS2 is the form of the order, detailed records of customer orders of goods, prices, quantities and other information.
　　
The first step of the adjustment is to extract the query, and then look at the execution plan of the statement. Generally speaking, if the statement can avoid the use of subqueries, try not to subquery. Because the cost of a subquery is quite expensive. The rewritten statements are as follows:
Select SUM (sl0000)
From Xstfxps2 A, (select dhao00 from Xstfxps1 where Trunc (ywrq00) =trunc (sysdate)
and khdm00= ' 500000003913 ') b
where a.dhao00=b.dhao00;
Time used: 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) ' XSTFXPS1 '
5 3 INDEX (RANGE SCAN) of ' xstfxps2_pk ' (UNIQUE)
Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
11974 consistent gets
Physical Reads
832 Redo Size
211 Bytes sent via sql*net to client
275 bytes received 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 logical IO was reduced from 17,355,138 times to 11,974 times, with an order of magnitude of ascension. The execution time also had the original nearly 3 minutes to descend to now 3 seconds more. Obviously, the performance has been greatly improved. But we see the execution plan inside the table XSTFXPS1 still has a full table scan present. Generally speaking, we should try to avoid the existence of full table scan, especially for large tables, we should establish appropriate indexes to avoid FTS. Let's look at the index information for these 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 see that these two tables do not have another index in addition to the primary KEY constraint. Based on the query of the statement, we established the following composite index:
Create INDEX idx_xstfxps1_khdm00_ywrq00 on XSTFXPS1 (khdm00,ywrq00) tablespace indx;
　　
In order to use the index, we must make some adjustments to the conditions of the original date field. Because of the existence of a trunc () function, the statement will not use the index. As long as we understand that trunc (ywrq00) =trunc (sysdate) is actually equivalent to ywrq00 (trunc), less than sysdate (trunc) minus one second, we have a better way to deal with
This condition. The final rewrite statement is as follows:
Select SUM (sl0000)
From Xstfxps2 A, Xstfxps1 b
where a.dhao00=b.dhao00
and b.khdm00= ' 500000003913 '
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 calls
0 db Block gets
3 Consistent gets
0 physical Reads
0 Redo Size
210 Bytes sent via sql*net to client
275 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed
We are now looking at the logical IO has been reduced to 3 times, the statement of the execution plan also conforms to our adjustment objectives, the creation of the index produced a relatively large effect. The adjustment to this statement concludes.