explain plan for
SELECT * FROM schema2.mv_a_data mv
WHERE mv.routing_code_type_id = 3
AND mv.static_data_status_id IN(5, 8)
AND mv.acct_currency_ind_id NOT IN
(SELECT DISTINCT te.acct_currency_ind_id
FROM schema1.tem_ele te
WHERE te.lis_tem_id IN
(SELECT lis_tem_id
FROM schema1.lis_tem
WHERE lis_tem.template_type_id = 2
AND lis_tem.deleted_flag = 'N' )
AND te.acct_currency_ind_id IS NOT NULL
)
ORDER BY mv.treasury_region_code,
mv.legal_entity_mnemonic,
mv.currency_code;
select *
from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', null,'ADVANCED'));
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 892 | 318K| 69 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 892 | 318K| 69 (2)| 00:00:01 |
|* 2 | HASH JOIN RIGHT ANTI | | 892 | 318K| 68 (0)| 00:00:01 | `
| 3 | VIEW | VW_NSO_1 | 1457 | 8742 | 11 (0) | 00:00:01 |
|* 4 | HASH JOIN | | 1457 | 33511 | 11 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | lis_tem | 100 | 1100 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | tem_ele | 3271 | 39252 | 8 (0)| 00:00:01 |
|* 7 | MAT_VIEW ACCESS FULL| mv_a_data | 2348 | 825K| 57 (0)| 00:00:01 |
I am trying to read the given explain plan. After looking at the explain plan and according to my understanding, first of all, FTS of LIS_TEM table and TEM_ELE are done and then they are joined using HASH JOIN.
Question 1 - can I change this HASH JOIN to NESTED LOOP?
I am asking just for learning purpose. I know HASH JOIN is good here. After HASH JOIN, I guess it is going to ID3 ie VIEW VW_NSO_1 and ID7 MAT_VIEW.
Question 2 - what is this VIEW VW_NSO_1, which clause of the SQL query was responsible to brought it here?
Question 3 - How I will read the rest of the plan?
Question 4 - Why HASH JOIN RIGHT ANTI came in picture?
Please assist me to understand the above explain plan in detail. Thanks and let me know if you need any further details.