Oracle SQL optimization 2 and OracleSQL optimization 2

Oracle SQL optimization 2 and OracleSQL optimization 2
After receiving an SQL statement submitted by the developer, it is slow in high concurrency and can be optimized:
This SQL statement is used to perform Left join on another table. First, let's take a look,
SQL> select l. vcno, opttype, add_points, optdate, remark, memid, id, FAMILYID, create_Username, billno, billtype, billsubcase, example, l. addnum, addpresentum
2 from HQ_07310066.m_mempoint_logs l left join HQ_07310066.m_memdetail m on l. vcno = m. vcno where 1 = 1 AND l. memid = 'wx000000361 'order by optdate desc;
Row 208 has been selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2095947206
Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 4 | 464 | 172 (2) | 00:00:03 |
| 1 | sort order by | 4 | 464 | 172 (2) | 00:00:03 |
| * 2 | table access full | M_MEMPOINT_LOGS | 4 | 464 | 171 (1) | 00:00:03 |
Bytes --------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("L". "MEMID" = 'wx000000361 ')
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
626 consistent gets
0 physical reads
0 redo size
7125 bytes sent via SQL * Net to client
663 bytes encoded ed via SQL * Net from client
15 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
208 rows processed
This may be the reason for the primary and foreign key constraints. The optimizer automatically removes the table for HQ_07310066.m_memdetail. Therefore, if there are strict primary and foreign key constraints between the two tables, you can directly remove unnecessary tables in SQL, as shown below:
SQL> select l. vcno, opttype, add_points, optdate, remark, memid, id, FAMILYID, create_Username, billno, billtype, billsubcase, performance_points, l. addnum, addpresentum, performancenum,
2 bytes cepresentnum
3 from HQ_07310066.m_mempoint_logs l
4 where l. memid = 'wx000000361'
5 order by optdate desc;
Row 208 has been selected.
Used time: 00: 00: 00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 2095947206
Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 4 | 464 | 172 (2) | 00:00:03 |
| 1 | sort order by | 4 | 464 | 172 (2) | 00:00:03 |
| * 2 | table access full | M_MEMPOINT_LOGS | 4 | 464 | 171 (1) | 00:00:03 |
Bytes --------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("L". "MEMID" = 'wx000000361 ')
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
626 consistent gets
0 physical reads
0 redo size
11245 bytes sent via SQL * Net to client
663 bytes encoded ed via SQL * Net from client
15 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
208 rows processed
Create index HQ_07310066. I _m_mempoint_logs_optdate on HQ_07310066.m_mempoint_logs (optdate );
SQL> select l. vcno, opttype, add_points, optdate, remark, memid, id, FAMILYID, create_Username, billno, billtype, billsubcase, performance_points, l. addnum, addpresentum, performancenum,
2 bytes cepresentnum
3 from HQ_07310066.m_mempoint_logs l
4 where l. memid = 'wx000000361'
5 order by optdate desc;
Row 208 has been selected.
Used time: 00: 00: 00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3466595853
Bytes ----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------------------------
| 0 | select statement | 4 | 464 | 6 (17) | 00:00:01 |
| 1 | sort order by | 4 | 464 | 6 (17) | 00:00:01 |
| 2 | table access by index rowid | M_MEMPOINT_LOGS | 4 | 464 | 5 (0) | 00:00:01 |
| * 3 | index range scan | I _M_MEMPOINT_LOGS_OPTDATE | 4 | 1 (0) | 00:00:01 |
Bytes ----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-access ("L". "MEMID" = 'wx000000361 ')
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
44 consistent gets
2 physical reads
0 redo size
11245 bytes sent via SQL * Net to client
663 bytes encoded ed via SQL * Net from client
15 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
208 rows processed
Optimized.