database - Oracle 中数据库视图的性能改进

Question

我正在努力研究这种观点的表现。我认为物化视图可能会有所帮助，但我想知道是否可以改进查询。

Primary keys fro the tables are:
CONSUMER_DTL :ID
CONSUMER__HDR : ID
PARTICIPANT : ID
P_DEVICE : ID

   CREATE OR REPLACE FORCE EDITIONABLE VIEW "V_TRANSACTION_HISTORY" ("ID", "TRANSACTION_DATE", "TRANSACTION_ID", "REFUND_AMOUNT", "STATUS", "LABEL_ID", "COST_ID", "CONSUMER_ID") AS 
  SELECT ROW_NUMBER() OVER (ORDER BY CH.ID) AS ID,
               CH.TRANSACTED_ON AS TRANSACTION_DATE,
               CH.ID AS TRANSACTION_ID,
               SUM(CD.GROSS_AMOUNT) AS REFUND_AMOUNT,
               CH.STATUS AS STATUS,
               CH.LABEL_ID,
               S.ID AS COST_ID,
               CH.CONSUMER_ID
       FROM CONSUMER_DTL CD,
            CONSUMER__HDR CH,
            P_DEVICE P,
            PARTICIPANT S
       WHERE CH.ID=CD.TRANSACTION_ID
         AND CH.P_DEVICE_ID=P.ID
         AND P.ID=S.ID
             GROUP BY CH.ID,
                   CH.STATUS,
                   CH.LABEL_ID,
                   CH.TRANSACTED_ON,
                   CH.CONSUMER_ID,
                   S.ID;

标签： databaseoracleperformance