Oracle 11g 查询花了将近 30 分钟才完成，但在 SQL Server 中大约需要 5 分钟

Question

我需要生成使用预付费的用户的详细信息报告。

基本上我有2张桌子。TBL_USER，也TBL_PREPAID_DETAILS

tbl_user只列出用户，而tbl_prepaid_details将拥有所有预付信息，例如借记卡，信用卡，余额，总充值。

该报告将产生如下内容：-

FULLNAME USER_ID NEW_BALANCE CREATED DEPOSIT DEBIT TOTAL_DEPOSIT
USER1 1 250 21/05/2011 500 250 1000
USER2 2 250 21/05/2011 500 250 1000
USER3 3 250 21/05/2011 500 250 1000
USER4 4 250 21/05/2011 500 250 1000

在此之前，我使用SQL服务器作为数据库，但在2012/2013年，我们需要从SQL服务器迁移到Oracle 11g。

从前面的sql查询（SQL Server）中，我得出了以下查询（Oracle 11g）。我可以得到结果，但花了将近 30 分钟才能完成，而在 SQL 服务器中只花了大约 5 分钟。 它只有500+用户

是我错过了什么吗？？有没有简化的版本来加快这个过程？

SELECT DISTINCT A.FULLNAME AS NAME,
                A.USER_ID AS ID,
                B.NAME,
  (SELECT T.NEW_BAL
   FROM
     (SELECT NEW_BAL,
             USER_ID
      FROM TBL_PREPAID_DETAILS
      WHERE DATE_INSERT BETWEEN '01/01/2010' AND '14/11/2012'
      ORDER BY PREPAID_DETAIL_ID DESC) T
   WHERE T.USER_ID = A.USER_ID
     AND ROWNUM = 1) AS NEW_BALANCE,
  (SELECT T2.DATE_INSERT
   FROM
     (SELECT DATE_INSERT,
             USER_ID
      FROM TBL_PREPAID_DETAILS
      WHERE DATE_INSERT BETWEEN '01/01/2010' AND '14/11/2012'
      ORDER BY DATE_INSERT DESC)T2
   WHERE T2.USER_ID = A.USER_ID
     AND ROWNUM = 1) AS DATE_INSERT,
  (SELECT T3.PREV_BAL
   FROM
     (SELECT PREV_BAL,
             USER_ID
      FROM TBL_PREPAID_DETAILS
      WHERE DATE_INSERT BETWEEN '01/01/2010' AND '14/11/2012'
      ORDER BY PREPAID_DETAIL_ID ASC)T3
   WHERE T3.USER_ID = A.USER_ID
     AND ROWNUM = 1) AS DEPOSIT,
  (SELECT SUM(T4.CREDIT)
   FROM
     (SELECT CREDIT,
             USER_ID
      FROM TBL_PREPAID_DETAILS
      WHERE DATE_INSERT BETWEEN '01/01/2010' AND '14/11/2012'
      ORDER BY PREPAID_DETAIL_ID ASC)T4
   WHERE T4.USER_ID = A.USER_ID
     AND ROWNUM = 1) AS DEBIT,
  (SELECT SUM(T5.DEBIT + T5.PREV_BAL)
   FROM
     (SELECT DEBIT,
             PREV_BAL,
             USER_ID
      FROM TBL_PREPAID_DETAILS
      WHERE DATE_INSERT BETWEEN '01/01/2010' AND '14/11/2012'
      ORDER BY PREPAID_DETAIL_ID ASC)T5
   WHERE T5.USER_ID = A.USER_ID
     AND ROWNUM = 1) AS TOTAL_DEPOSIT
FROM TBL_USER A
LEFT JOIN TBL_PREPAID_DETAILS C ON A.USER_ID = C.USER_ID
LEFT JOIN TBL_ORGANIZATION_INFO B ON A.ORGANIZATION_ID = B.ORGANIZATION_ID
WHERE C.DATE_INSERT BETWEEN '01/01/2010' AND '14/11/2012'
  AND (A.USER_ID NOT IN ('xxx',
                         'xxx',
                         'xxx'))
GROUP BY A.FULLNAME,
         A.USER_ID,
         C.DATE_INSERT,
         B.NAME
ORDER BY A.FULLNAME;

Answer 1

请查看此页面以获取有关 Oracle 调优的一般概述帮助。 整体甲骨文调优页面

Oracle 提供了大量工具来调整性能，包括可以添加哪些索引来改进特定查询。当我遇到运行缓慢的查询时，这是我做的第一件事。在您的情况下，我会考虑对您的查询运行解释计划，看看 Oracle 的建议。以下是有关运行说明计划的文档。甲骨文解释计划