Scenario #$4$

Question and scenario:$$ Optimization

Please provide the following:

$1.$ Detail description of what the code is doing. Be sure to include an explanation of each functions;

$2.$ A revised SQL statement containing your suggestions for optimizing query performance

DDL Statement

CREATE TABLE t$\_$get$\_$rec

AS

WITH

t$\_$get$\_$rec

AS

$($SELECT $/*+$parallel $($auto$)*/$

S$\_$UUID,

E$\_$UUID,

SNPSHT$\_$ID$,$

ROW$\_$NUMBER $()$

OVER $($PARTITION BY S$\_$UUID ORDER BY SNPSHT$\_$ID$)$ RNK

FROM tbl$\_$snpshot

WHERE S$\_$STATUS IN $($SELECT S$\_$STATUS FROM S$\_$STATUS$\_$DIM WHERE S$\_$STATUS IN

$($

'COMPLETED',

'CANCELED',

'ADMIN$\_$COMPLETED',

'ADMIN$\_$CANCELED'$)$

$)$

SELECT

B$.$SOURCE$\_$DATA,

B$.$ESR$\_$ID$,$

B$.$E$\_$UUID,

B$.$T$\_$UUID,

B$.$D$\_$UUID,

to$\_$char$($B$.$TRANSACTION$\_$DATE, 'Month DD$,$YYYY$),$

B$.$T$\_$UUID,

B$.$T$\_$TYPE,

B$.$D$\_$TYPE,

B$.$E$\_$STATUS,

B$.$C$\_$UUID,

B$.$S$\_$UUID

B$.$E$\_$DATE,

B$.$C$\_$ID$,$

CASE WHEN A$.$RNK $=1$ THEN B$.$TRANSACTION$\_$DATE END C$\_$DATE

FROM t$\_$get$\_$rec A

RIGHT OUTER JOIN tbl$\_$trans B

ON A$.$S$\_$UUID $=$ B$.$S$\_$UUID AND A$.$E$\_$UUID $=$ B$.$E$\_$UUID

WHERE TRUNC $($B$.$TRANSACTION$\_$DATE$)>=($SELECT D$\_$DATE FROM DATE$\_$DIM

WHERE D$\_$DATE $=$ TO$\_$DATE $(\text{'}12/15/2022\text{'},\text{'}$MM$/$DD$/$YYYY$\text{'}))$;