Greetings,
Learning from the execution plan method
explain plan for select NVL(SUM(year_1),0) year_1, NVL(SUM(year_2),0) year_2, NVL(SUM(year_3),0) year_3, NVL(SUM(year_4),0) year_4, NVL(SUM(year_5),0) year_5, NVL(SUM(year_6),0) year_6, NVL(SUM(year_7),0) year_7 from ( select NVL(SUM(CASE WHEN rnk = 1 then nvl(total_rov, 0) else 0 end),0) year_1, NVL(SUM(CASE WHEN rnk = 2 then nvl(total_rov, 0) else 0 end),0) year_2, NVL(SUM(CASE WHEN rnk = 3 then nvl(total_rov, 0) else 0 end),0) year_3, NVL(SUM(CASE WHEN rnk = 4 then nvl(total_rov, 0) else 0 end),0) year_4, NVL(SUM(CASE WHEN rnk = 5 then nvl(total_rov, 0) else 0 end),0) year_5, NVL(SUM(CASE WHEN rnk = 6 then nvl(total_rov, 0) else 0 end),0) year_6, NVL(SUM(CASE WHEN rnk = 7 then nvl(total_rov, 0) else 0 end),0) year_7 from (
SELECT a.fy, a.hull_availability, h.hull_short_name, c.hull_class_id_name, c.ent_id, h.hull_class_id, n.pe_id
, (fn_getFuncTR(n.req_id, a.fy, h.hull_id, s.nsa_worksite_id, a.hull_availability)
* fn_getRateTR(n.req_id,h.hull_class_id,n.pe_id,a.fy)) * (s.percentage/100) as total_rov
,dense_rank() over (partition by a.nsa_id order by a.fy ) rnk
FROM nsa n
JOIN nsa_hull_availability a on n.nsa_id = a.nsa_id
JOIN nsa_split s on n.nsa_id = s.nsa_id
AND a.fy = s.fy
JOIN hull h on n.hull_id = h.hull_id
JOIN hull_class c on h.hull_class_id = c.hull_class_id
JOIN rmc_notional_work_type wt on s.rmc_notional_work_type_id = wt.rmc_notional_work_type_id
WHERE n.req_id = 42094
AND s.nsa_worksite_id = 156
AND c.ent_id = 47
AND n.status like 'A'
AND wt.rmc_notional_work_type_id = 1
AND h.hull_id != 777
AND a.fy between 2025 and 2031
--AND c.hull_class_id like :7
--AND n.nsa_hullsite_id like :8
)
UNION ALL
SELECT /*+ dynamic_sampling(5) */ *
FROM TABLE (SHIPMAINT.NON_CNO_CALCULATIONS.Transfer_I_Level_Material(42094,156,47,'A',0,0))
UNION ALL
SELECT /*+ dynamic_sampling(5) */ *
FROM TABLE (SHIPMAINT.NON_CNO_CALCULATIONS.RMC_Material_Others(42094,156,47,'A',0,0))
);
select * from table(dbms_xplan.display);
The result
Plan hash value: 832020333
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 142 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 91 | | |
| 2 | VIEW | | 3 | 273 | 142 (1)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | SORT AGGREGATE | | 1 | 26 | | |
| 5 | VIEW | | 13 | 338 | 120 (1)| 00:00:01 |
| 6 | WINDOW SORT | | 13 | 858 | 120 (1)| 00:00:01 |
| 7 | NESTED LOOPS | | 13 | 858 | 119 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 18 | 858 | 119 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 18 | 954 | 83 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 17 | 578 | 32 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 51 | 1377 | 32 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED| NSA | 51 | 969 | 30 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX_UNQ_NSA | 493 | | 5 (0)| 00:00:01 |
| 14 | VIEW | index$_join$_008 | 483 | 3864 | 2 (0)| 00:00:01 |
|* 15 | HASH JOIN | | | | | |
| 16 | INDEX FAST FULL SCAN | FK_HULL_CLASS | 483 | 3864 | 1 (0)| 00:00:01 |
|* 17 | INDEX FAST FULL SCAN | PK_HULL | 483 | 3864 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | IDX_HULLCLASS_ENT | 1 | 7 | 0 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID BATCHED | NSA_SPLIT | 1 | 19 | 3 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PK_NSASPLIT | 1 | | 2 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PK_NSA_HULL_AVAILABILITY | 1 | | 1 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | NSA_HULL_AVAILABILITY | 1 | 13 | 2 (0)| 00:00:01 |
| 23 | COLLECTION ITERATOR PICKLER FETCH | TRANSFER_I_LEVEL_MATERIAL | 1 | 2 | 11 (0)| 00:00:01 |
| 24 | COLLECTION ITERATOR PICKLER FETCH | RMC_MATERIAL_OTHERS | 1 | 2 | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - access("N"."HULL_ID"="H"."HULL_ID")
13 - access("N"."REQ_ID"=42094 AND "N"."STATUS"='A')
filter("N"."STATUS"='A' AND "N"."HULL_ID"<>777)
15 - access(ROWID=ROWID)
17 - filter("H"."HULL_ID"<>777)
18 - access("H"."HULL_CLASS_ID"="C"."HULL_CLASS_ID" AND "C"."ENT_ID"=47)
20 - access("N"."NSA_ID"="S"."NSA_ID" AND "S"."FY">=2025 AND "S"."NSA_WORKSITE_ID"=156 AND
"S"."RMC_NOTIONAL_WORK_TYPE_ID"=1 AND "S"."FY"<=2031)
filter("S"."NSA_WORKSITE_ID"=156 AND "S"."RMC_NOTIONAL_WORK_TYPE_ID"=1)
21 - access("N"."NSA_ID"="A"."NSA_ID" AND "A"."FY"="S"."FY")
filter("A"."FY">=2025 AND "A"."FY"<=2031)
Note
-----
- dynamic statistics used: dynamic sampling (level=5)
My question is in line 16 and 17, both indexes FK_HULL_CLASS (hull_class_id) & PK_HULL (hull_id) are in HULL table.
The NSA table has the index IDX_UNQ_NSA that contains multiple columns including hull_id, hull_class_id is not present.
Is the optimizer getting confuse?
should I remove FK_HULL_CLASS indexes that I have for the hull_class table?
or, are the indexes okay?
Thanks, ~Johnny