[SQL] Performance issue with a view on large tables with more than 25 billion records

In below query STG_USR_ACCSS have more then 25 billion records, creating a view by joining other tables, when we query the data from the view, its taking forever to return the results. Please help me to rewrite the query for the view to increase the performance of the query for faster retrieval of the results.

CREATE or REPLACE VIEW large_data_vw as
SELECT /*+ DRIVING_SITE(A,B,C,D,E,F) */ DISTINCT NVL(M.TEST_MR,
SUBSTR(C.ID,-8)) MR,NVL(N.TEST_MR, SUBSTR(E.ID,-8))
ACS_MR,A.UA_TM,UPPER(F.ABR) TP_OF_ACT
FROM STG_USR_ACCSS A
JOIN STG_PAT B ON A.PT_ID=B.PT_ID
JOIN STG_ID C ON B.T_ID=C.T_ID AND C.TYPE_ID=14567
JOIN STG_PAT D ON A.WHO_AC=D.PT_ID
JOIN STG_ID E ON D.T_ID=E.T_ID AND E.TYPE_ID=14567
JOIN STG_TYPE F ON A.TPE_C=F.TPE_C
LEFT JOIN STG_MER M ON SUBSTR(C.ID,-8) = M.MR
LEFT JOIN STG_MER N ON SUBSTR(E.ID,-8) = N.MR ;

Continue reading...