[ACE-618] updateOltPort is having Performance issue 1.5s versus 3s Created: 26/Feb/26  Updated: 02/Mar/26  Due: 23/Feb/26

Status: Ready for QA
Project: ACE
Component/s: FN
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Urgent
Reporter: Mbaye Lo Assignee: Mbaye Lo
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Customer:
OMAN-Tel
Planned Start:
Planned End:

 Description   

updateOltPort is having Performance issue 1.5s versus 3s

<ser:updateOltPort>
<!-Optional:->
<msanName>IH95_01_MA5800_OLT</msanName>
<newOltPort>1/0/3/0/15</newOltPort>
<subscriberNo>24777728</subscriberNo>
</ser:updateOltPort>

 Comments   
Comment by Zein Trabelsi [ 02/Mar/26 ]

optimize FN_AP_VIEW performance by rewriting PIVOT to CASE/WHEN aggregation

The view was taking ~1 seconds due to the Oracle PIVOT operation on
ELEMENTADDITIONALINFO scanning 83,520 rows (98.7% of total query cost)
before joining to the target record.

Replaced PIVOT with conditional MAX(CASE WHEN) aggregation and added
an ATTRIBUTE IN filter to reduce scanned rows. Added MATERIALIZE hint
on SELECTED_VERSION and merged redundant CTEs to minimize temp table
reads. Converted implicit joins to explicit JOIN syntax for better
optimizer predicate pushdown.

Result: query time reduced from ~1s to 0.02s.

Generated at Wed Apr 15 09:36:52 EEST 2026 using JIRA 6.1.4#6159-sha1:44eaedef2e4a625c6c7183698b2468d4719c20dc.