Reading Oracle Explain the plan
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 outline of the explanation. Looking at the outline of the explanation and to my understanding, first of all the FTS of the LIS_TEM and TEM_ELE tables are executed and then they are joined using a HASH JOIN.
Question 1 - can I change this HASH JOIN to NESTED LOOP?
I ask only to know the purpose of the training. I know HASH JOIN is good here. After HASH JOIN, I think it will be ID3, i.e. VIEW VW_NSO_1 and ID7 MAT_VIEW.
Question 2 - What is this VIEW VW_NSO_1 that the SQL query is responsible for?
Question 3 . How will I read the rest of the outline?
Question 4 - Why does HASH JOIN RIGHT ANTI appear in the picture?
Please help me understand the above explanation plan. Thanks and let me know if you need more information.
source to share
"can I change this HASH JOIN to NESTED LOOP?"
You can try the hint use_nl
. Find out more
"what is this VIEW VW_NSO_1, which SQL query question was responsible to bring it here?"
VW_NSO_1
indicates the opacity of the subquery IN
in the view. This is because your subquery uses DISTINCT, so it guarantees a unique set. This operation is not documented in the 11g docs, but you can find it in the old setup guides.
"How am I going to read the rest of the plan?"
Ummm, with due attention to detail? Basically, all the tables involved are small enough to have no useful indexes, so the optimizer is loaded to scan the full table.
"Why did HASH JOIN RIGHT ANTI appear in the photo?"
Think of a subquery NOT IN
as an outer join. You need entries in MView that don't match the rows in the subquery - anti join. Your request will have a LEFT OUTER JOIN. However, the optimizer decided that it would be more efficient to collect the result set of the subquery first than to evaluate the anti-join on MView, which is the correct outer join. Dion Cho wrote a very good example. Find out more .
source to share
You can use query hints to "force" a query for a specific connection, although I would not recommend it. If he uses a hash, then this is probably the best thing for him. Make sure your stats are up to date if you think it's better to use a different connection
Oracle docs help look at what each operation does: https://docs.oracle.com/cd/B10501_01/server.920/a96533/ex_plan.htm
source to share
The VW_NSO_1 view is your inline view in your query:
(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
)
The optimizer has converted your "in" clause into a hash join. It also converted your "not in" sentence into an anti-right hash join.
All of your tables are pretty small, so a full scan doesn't bother you - the optimizer has probably chosen the best tactic for your data.
source to share