ORACLE_PL, sql_nested Loop

1.SQL

Select/*+ Index (T1 xpk_tran_hist_topic) */sum (T1. Local_amount) as Totallocalamount,sum (T1. Usd_amount) as Totalusdamountfrom tran_hist_topic T1, Mdl_check_result t2where T2. Trs_seqno = T1. Seqnoand T2. case_id =: 1AND t1. Soc_no = To_char (: 2)

Sql>select Count (*) from Mdl_check_result where case_id= ' DK2018050300000003620000000002 '; T2

COUNT (*)
----------
3

Sql>select Count (*) from tran_hist_topic where soc_no= ' 123 '; T1

COUNT (*)

----------
229194

2.

Sql>explain plan for Select/*+ Index (T1 xpk_tran_hist_topic) Use_nl (T1,T2) leading (T1) */2 SUM (T1. Local_amount) as Totallocalamount, 3 SUM (T1. Usd_amount) as Totalusdamount from Tran_hist_topic 4 T1, Mdl_check_result T2 WHERE T2. Trs_seqno = T1. Seqno 5 and T2. case_id = ' DK2018050300000003620000000002 ' and T1. Soc_no = To_char (123); Sql>select * FROM table (dbms_xplan.display); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -----------------Plan Hash value:3321045997--------------------------------------------------------------------- --------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------   --------------------| 0 |         SELECT STATEMENT            |     |   1 |   158 | 575K (1) |       01:55:08 |       |   ||  1 |                      SORT AGGREGATE |     |   1 |            158 |          |       |       |   ||   2 |                      NESTED LOOPS |    |  57 |   9006 | 575K (1) |       01:55:08 |       |   ||    3 |                      PARTITION LIST Single |   |    229k|   12m| 116K (1) |   00:23:16 |   KEY |   KEY | |     4 | TABLE ACCESS by LOCAL INDEX rowid|   Tran_hist_topic |    229k|   12m| 116K (1) |    00:23:16 |    40 | 40 | |      * 5 | INDEX RANGE SCAN |   Xpk_tran_hist_topic |       437 |  | 2318 (1) |    00:00:28 |    40 | 40 | |    * 6 | INDEX UNIQUE SCAN |     Xpk_mdl_check_result |   1 |     102 | 2 (0) |       00:00:01 |       | |-------------------------------------------------------------------------------------------------------------- ---------------predicate information (identified By Operation ID):---------------------------------------------------5-access ("T1". " Soc_no "= ' 123 ') 6-access (" T2 ".") case_id "=u ' DK2018050300000003620000000002 ' and" T2 "." Trs_seqno "=" T1 "." Seqno ")

3.

Sql>explain plan for Select/*+ Index (T1 xpk_tran_hist_topic) Use_nl (T1,T2) leading (T2) */2 SUM (T1. Local_amount) as Totallocalamount, 3 SUM (T1. Usd_amount) as Totalusdamount from Tran_hist_topict1, Mdl_check_result T2 WHERE T2. Trs_seqno = T1. Seqno 4 5 and T2. case_id = ' DK2018050300000003620000000002 ' and T1. Soc_no = To_char (123); Sql>select * FROM table (dbms_xplan.display); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -----------------Plan Hash value:3118427468--------------------------------------------------------------------- -------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------   -------------------| 0 |            SELECT STATEMENT        |     |   1 |   158 | 119 (0) |       00:00:02 |       |   ||  1 |                      SORT AGGREGATE |     |   1 |            158 |          |       |       |   ||   2 |                      NESTED LOOPS |    |  57 |   9006 | 119 (0) |       00:00:02 |       |   ||    3 |                      NESTED LOOPS |    |  57 |   9006 | 119 (0) |       00:00:02 |       | ||     * 4 | INDEX RANGE SCAN |    Xpk_mdl_check_result |  57 |     5814 | 5 (0) |       00:00:01 |       |   ||     5 |                      PARTITION LIST Single |     |       1 |     | 1 (0) |   00:00:01 |   KEY | KEY | |      * 6 | INDEX UNIQUE SCAN |     Xpk_tran_hist_topic |       1 |     | 1 (0) |    00:00:01 |    40 |   40 | |    7 | TABLE ACCESS by LOCAL INDEX rowid|     Tran_hist_topic |    1 |     56 | 2 (0) |    00:00:01 |    40 | --------------------------------------------|--------------------------------------------------------------------------------Predicate information ( identified by Operation ID):---------------------------------------------------4-access ("T2". " case_id "=u ' DK2018050300000003620000000002 ') 6-access (" T1 "." Soc_no "= ' 123 ' and" T2 "." Trs_seqno "=" T1 "." Seqno ")

ORACLE_PL, sql_nested Loop