CODEWITHSUNDEEP
sqloracle-databaseperformancequery-optimizationsql-view
OracleLearner
OracleLearner

Reputation: 45

Performance issue with a view on large tables with more than 25 billion rows

The STG_USR_ACCSS table has more than 25 billion rows. Creating a view by joining other tables, when we query the data from the view, it's 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  ;

Upvotes: 1

Views: 73

Answers (0)

Related Questions