problem:
How should such SQL optimize?
SELECT * from Sys_User
Where user_code = 'zhangyong'
OR user_code in
(SELECT GRP_CODE
From Sys_GRP
Where sys_grp.user_code = 'zhangyong')
Execution Plan
-------------------------------------------------- ------------
0 SELECT Statement Optimizer = Rule
1 0 Filter
2 1 Table Access (Full) of 'Sys_User'
3 1 Index (Unique Scan) of 'PK_SYS_GRP' (UNIQUE)
Statistics
-------------------------------------------------- ------------
14 Recursive Calls
4 DB Block Gets
30590 CONSISTENT GETS
0 Physical READS
0 redo size
1723 BYTES SENT VIA SQL * NET to Client
425 Bytes Received Via SQL * Net from Client
2 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
3 Rows Processed
The number of records returned in the inside is generally only one or two, but the data of the sys_user table is much data, how can this SQL drive table with SYS_GRP? The records in the table are as follows:
SQL> SELECT Count (*) from sys_grp;
Count (*) ---------- 25130
SQL> SELECT Count (*) from sys_user;
Count (*)
------------
15190
optimization:
Reduce logical reading is one of the basic principles of optimizing SQL
We tried to speed up the execution of SQL by reducing logical reading.
Here we use the OR to expand to overwrite the SQL query:
Select * from sys_user where user_code = 'zhangyong'
Union all
Select * from sys_user where user_code <> zhangyong '
And user_code in (SELECT GRP_CODE AUS_GRP where sys_grp.user_code = 'zhangyong')
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
0 DB Block Get
130 Consistent Gets
0 Physical READS
0 redo size
1723 BYTES SENT VIA SQL * NET to Client
425 Bytes Received Via SQL * Net from Client
2 SQL * NET ROUNDTRIPS TO / FROM Client
1 Sorts (Memory)
0 Sorts (Disk)
3 Rows Processed
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Rule1 0 Union-all
2 1 Table Access (by index rowid) of 'sys_user'
3 2 INDEX (Unique Scan) of 'Pk_sys_User' (Unique)
4 1 Nested Loops
5 4 View of 'VW_NSO_1'
6 5 sort (unique)
7 6 Table Access (by index rowid) of 'sys_grp'
8 7 Index (Range Scan) of 'FK_SYS_USER_CODE' (Non-Unique)
9 4 Table Access (By Index Rowid) of 'Sys_USER'
109 Index (Unique Scan) of 'PK_SYS_USER' (UNIQUE)
We noticed that by rewriting, logical reading is reduced to 130, from 30590 to 130 this is a huge improvement, reducing logic reads will eventually reduce resource consumption, improve SQL execution efficiency.
This rewrite converts Filter to Nest Loop, and the index is fully utilized. Thus greatly improving performance.
We also noticed that a sort is introduced here.
Sort from this step:
-------------------------------------------------- ---------------------------------------
6 5 sort (unique)
7 6 Table Access (by index rowid) of 'sys_grp'
8 7 Index (Range Scan) of 'FK_SYS_USER_CODE' (Non-Unique)
-------------------------------------------------- ----------------------------------------
In the 'sys_grp' table, user_code is the unique key value in the IN value, to do sort unique sort, remove the repetition value
The UNION ALL here is no need to sort
