Optimization of SQL with beauty

A beautiful female DBA wants to learn SQL optimization, and is often confused with me. My favorite beauty. Today, he sent an SQL statement asking me to help you.

The execution plan is as follows:

Select "A1 ". "code", "A1 ". "device_id", "A1 ". "sideb_port_id", "A1 ". "version" from (select "A2 ". "Code" "code", "A2 ". "device_id" "device_id", "A2 ". "sideb_port_id" "sideb_port_id", "A3 ". "version" "version", row_number () over (partition by "A4 ". "prod_id" order by "A4 ". "hist_time" DESC) "RN" from "RM ". "h_prod_2_rf_serv" "A4", "RM ". "h_rsc_facing_serv_line_item" "A3", "RM ". "connector" "A2" where "A4 ". "serv_id" = "A3 ". "serv_id" and "A3 ". "line_id" = "A2 ". "connector_id" and exists (select 0 from "RM ". "device_item" "A5" where "A5 ". "device_id" = "A2 ". "device_id" and "A5 ". & quot; item_spec_id & quot; = 200006 and & quot; A5 & quot ". "value" = '7') and "A4 ". "prod_id" = 313) "A1" where "A1 ". "RN" = 1 ----- 9.437 seconds 48194511 rows a535467304 row a2execution plan limit plan hash value: 10111536 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | minute | 0 | SELECT statement | 1 | 175 | 20 (10) | 00:00:01 | * 1 | View | 1 | 175 | 20 (10) | 00:00:01 | * 2 | window sort pushed rank | 1 | 109 | 20 (10) | 00:00:01 | 3 | nested loops | 1 | 109 | 19 (6) | 00:00:01 | 4 | nested loops | 1 | 80 | 17 (6) | 00:00:01 | 5 | merge join Cartesian | 1 | 60 | 13 (8) | 00:00:01 | 6 | sort unique | 1 | 36 | 6 (0) | 00:00:01 | * 7 | table access by index rowid | device_item | 1 | 36 | 6 (0) | 00:00:01 | * 8 | index range scan | idx_device_item_vale | 9 | 4 (0) | 00:00:01 | 9 | buffer sort | 4 | 96 | 7 (15) | 00:00:01 | 10 | table access by index rowid | h_prod_2_rf_serv | 4 | 96 | 6 (0) | 00:00:01 | * 11 | index range scan | idx_hp2rs_prodid_servid | 4 | 2 (0) | 00:00:01 | 12 | table access by index rowid | h_rsc_facing_serv_line_item | 2 | 40 | 4 (0) | 00:00:01 | * 13 | index range scan | idx_hrfsli_serv | 2 | 2 (0) | 00:00:01 | * 14 | table access by index rowid | connector | 1 | 29 | 2 (0) | 00:00:01 | * 15 | index unique scan | pk_connector | 1 | 1 (0) | 00:00:01 | identified predicate information (identified by Operation ID ): ------------------------------------------------- 1-filter ("A1 ". "RN" = 1) 2-filter (row_number () over (partition by "A4 ". "prod_id" order by internal_function ("A4 ". "hist_time") DESC) <= 1) 7-filter ("A5 ". "item_spec_id" = 200006) 8-access ("A5 ". "value" = '7') 11-access ("A4 ". & quot; prod_id & quot; = 313) 13-access (& quot; A4 & quot ". "serv_id" = "A3 ". "serv_id") 14-filter ("A5 ". "device_id" = "A2 ". "device_id") 15-access ("A3 ". "line_id" = "A2 ". "connector_id ") statistics defaults 0 recursive cballs 0 dB block gets 2539920 consistent gets 0 physical reads 0 redo size 735 bytes sent via SQL * Net to client 492 bytes received via SQL * Net From Client 2 SQL * Net roundtrips to/from client 3 sorts (memory) 0 sorts (Disk) 1 rows processed

 

14:56:39
Select count (*)
From "RM". "device_item" "A5"
Where "A5". "item_spec_id" = 200006
And "A5". "value" = '7'
14:56:44
View the returned results
Beautiful QQ 14:58:02

Count (*)
----------
68384
15:20:15
Select count (*) from h_prod_2_rf_serv
Where prod_id = 313;
15:20:18
Return result
Beautiful QQ 15:21:24
Running
15:24:03
No results yet ..
Beautiful QQ 15:24:24
Select count (*) from RM. h_prod_2_rf_serv;

Count (*)
----------
17036077
 
Beautiful QQ 15:24:25
OK
Beautiful QQ 15:24:36
Amount! Wrong
Beautiful QQ 15:24:38
Wait
Beautiful QQ 15:24:51
Select count (*) from RM. h_prod_2_rf_serv where prod_id = 313;

Count (*)
----------
6
Beautiful QQ 15:24:56
This is correct

15:33:20
Select "A1". "code", "A1". "device_id", "A1". "sideb_port_id", "A1". "version"
From (select/* + use_nl (A4, A3) leading (A4) use_nl (A3, A2) use_hash (A5 )*/
"A2". "code" "code ",
"A2". "device_id" "device_id ",
"A2". "sideb_port_id" "sideb_port_id ",
"A3". "version" "version ",
Row_number () over (partition by "A4". "prod_id" order by "A4". "hist_time" DESC) "RN"
From "RM". "h_prod_2_rf_serv" "A4 ",
"RM". "h_rsc_facing_serv_line_item" "A3 ",
"RM". "connector" "A2"
Where "A4". "serv_id" = "A3". "serv_id"
And "A3". "line_id" = "A2". "connector_id"
And exists (select 0
From "RM". "device_item" "A5"
Where "A5". "device_id" = "A2". "device_id"
And "A5". "item_spec_id" = 200006
And "A5". "value" = '7 ')
And "A4". "prod_id" = 313) "A1"
Where "A1". "RN" = 1
 
Beautiful QQ 15:33:42
The effort is successful! I just tested it on the production database, 0.188 seconds

The following is the execution plan after optimization.

15:50:13 SQL> SELECT "A1"."CODE", "A1"."DEVICE_ID", "A1"."SIDEB_PORT_ID", "A1"."VERSION"15:50:14   2    FROM (SELECT /*+ use_nl(a4,a3) leading(a4) use_nl(a3,a2) use_hash(a5) */15:50:14   3           "A2"."CODE" "CODE",15:50:14   4           "A2"."DEVICE_ID" "DEVICE_ID",15:50:14   5           "A2"."SIDEB_PORT_ID" "SIDEB_PORT_ID",15:50:14   6           "A3"."VERSION" "VERSION",15:50:14   7           ROW_NUMBER() OVER(PARTITION BY "A4"."PROD_ID" ORDER BY "A4"."HIST_TIME" DESC) "RN"15:50:14   8            FROM "RM"."H_PROD_2_RF_SERV"            "A4",15:50:14   9                 "RM"."H_RSC_FACING_SERV_LINE_ITEM" "A3",15:50:14  10                 "RM"."CONNECTOR"                   "A2"15:50:14  11           WHERE "A4"."SERV_ID" = "A3"."SERV_ID"15:50:14  12             AND "A3"."LINE_ID" = "A2"."CONNECTOR_ID"15:50:14  13             AND EXISTS (SELECT 015:50:14  14                    FROM "RM"."DEVICE_ITEM" "A5"15:50:14  15                   WHERE "A5"."DEVICE_ID" = "A2"."DEVICE_ID"15:50:14  16                     AND "A5"."ITEM_SPEC_ID" = 20000615:50:14  17                     AND "A5"."VALUE" ='7')15:50:14  18             AND "A4"."PROD_ID" = 313) "A1"15:50:14  19   WHERE "A1"."RN" = 115:50:15  20  ;Execution Plan----------------------------------------------------------Plan hash value: 3121894527----------------------------------------------------------------------------------------------------------------| Id  | Operation                        | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                 |                             |     1 |   175 |    40   (3)| 00:00:01 ||*  1 |  VIEW                            |                             |     1 |   175 |    40   (3)| 00:00:01 ||*  2 |   WINDOW SORT PUSHED RANK        |                             |     1 |   109 |    40   (3)| 00:00:01 ||*  3 |    HASH JOIN SEMI                |                             |     1 |   109 |    39   (0)| 00:00:01 ||   4 |     NESTED LOOPS                 |                             |     7 |   511 |    33   (0)| 00:00:01 ||   5 |      NESTED LOOPS                |                             |     7 |   308 |    19   (0)| 00:00:01 ||   6 |       TABLE ACCESS BY INDEX ROWID| H_PROD_2_RF_SERV            |     4 |    96 |     7   (0)| 00:00:01 ||*  7 |        INDEX RANGE SCAN          | IDX_HP2RS_PRODID_SERVID     |     4 |       |     3   (0)| 00:00:01 ||   8 |       TABLE ACCESS BY INDEX ROWID| H_RSC_FACING_SERV_LINE_ITEM |     2 |    40 |     4   (0)| 00:00:01 ||*  9 |        INDEX RANGE SCAN          | IDX_HRFSLI_SERV             |     2 |       |     2   (0)| 00:00:01 ||  10 |      TABLE ACCESS BY INDEX ROWID | CONNECTOR                   |     1 |    29 |     2   (0)| 00:00:01 ||* 11 |       INDEX UNIQUE SCAN          | PK_CONNECTOR                |     1 |       |     1   (0)| 00:00:01 ||* 12 |     TABLE ACCESS BY INDEX ROWID  | DEVICE_ITEM                 |     1 |    36 |     6   (0)| 00:00:01 ||* 13 |      INDEX RANGE SCAN            | IDX_DEVICE_ITEM_VALE        |     9 |       |     4   (0)| 00:00:01 |----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("A1"."RN"=1)   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "A4"."PROD_ID" ORDER BY              INTERNAL_FUNCTION("A4"."HIST_TIME") DESC )<=1)   3 - access("A5"."DEVICE_ID"="A2"."DEVICE_ID")   7 - access("A4"."PROD_ID"=313)   9 - access("A4"."SERV_ID"="A3"."SERV_ID")  11 - access("A3"."LINE_ID"="A2"."CONNECTOR_ID")  12 - filter("A5"."ITEM_SPEC_ID"=200006)  13 - access("A5"."VALUE"='7')Statistics----------------------------------------------------------          0  recursive calls          0  db block gets      14770  consistent gets          0  physical reads          0  redo size        735  bytes sent via SQL*Net to client        492  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          1  sorts (memory)          0  sorts (disk)          1  rows processed

If you know the execution plan of an SQL statement, you are the master of SQL optimization.