Oracle 性能问题

嗨，汤姆
我有以下查询

SELECT Q.*,ROWNUM -2 ROWNUM_ 
FROM ( 
SELECT NULL ORDER_ID,NULL REQ_ID, NULL TXN_ID , 0 ORDER_PROCESS_TYPE FROM DUAL 
UNION ALL
SELECT  
        T1.ID ORDER_ID,
        T2.ID REQ_ID,
        T3.ID TXN_ID ,
        25 AS TYPE
      FROM Table_1 T1
      INNER JOIN Table_2 T2 ON T1.ID = T2.T1_ID 
      INNER JOIN Table_3 T3 ON T2.ID = T1.T2_ID
    
      WHERE (T3.STATUS = 32)
      AND   (T3.CREATION_DATE   >= TO_DATE('01/02/2018 00:00:00','DD/MM/YYYY HH24:MI:SS') -120 /1440) 
      AND   (T3.CREATION_DATE  <= TO_DATE('28/02/2018 00:00:00','DD/MM/YYYY HH24:MI:SS') -120 /1440 )  
      AND   (T1.COLUMN_1 = 553 )  
      
      ORDER BY 3 DESC,1 DESC ) Q ; 
复制

快速获得结果的最佳实践是什么，
在那里我必须创建索引，

对于信息，所有三个表都很大，并且包含超过5000万条记录

SQL_ID  fz4gw8hu8rh4j, child number 0
-------------------------------------
SELECT Q.*,ROWNUM -2 ROWNUM_ FROM ( SELECT NULL ORDER_ID,NULL REQ_ID, 
NULL TXN_ID , 0 ORDER_PROCESS_TYPE FROM DUAL  UNION ALL SELECT          
 O.ID ORDER_ID,         CCR.ID REQ_ID,         CCT.ID TXN_ID ,         
25 AS ORDER_PROCESS_TYPE       FROM FORT.PF_ORDERS O       INNER JOIN 
FORT.PF_CREDIT_CARDS_REQUESTS CCR ON O.ID = CCR.ORDER_ID       INNER 
JOIN FORT.PF_CREDIT_CARDS_TRANSACTIONS CCT ON CCR.ID = 
CCT.CREDIT_CARD_REQ_ID        WHERE  CCT.STATUS = 32       AND   
(CCT.CREATION_DATE   >= TO_DATE('01/04/2019 00:00:00','DD/MM/YYYY 
HH24:MI:SS') -120 /1440)        AND   (CCT.CREATION_DATE  <= 
TO_DATE('01/05/2019 00:00:00','DD/MM/YYYY HH24:MI:SS') -120 /1440 )     
   AND   (O.MERCHANT_ENTITY_ID = 100 )    ORDER BY 3 DESC,1 DESC ) Q
 
Plan hash value: 195169236
 
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                          |      1 |        |     14 |00:00:15.03 |   49917 |   6693 |       |       |          |
|   1 |  COUNT                 |                          |      1 |        |     14 |00:00:15.03 |   49917 |   6693 |       |       |          |
|   2 |   VIEW                 |                          |      1 |    158 |     14 |00:00:15.03 |   49917 |   6693 |       |       |          |
|   3 |    SORT ORDER BY       |                          |      1 |    158 |     14 |00:00:15.03 |   49917 |   6693 |  2048 |  2048 | 2048  (0)|
|   4 |     UNION-ALL          |                          |      1 |        |     14 |00:00:15.03 |   49917 |   6693 |       |       |          |
|   5 |      FAST DUAL         |                          |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |       |       |          |
|   6 |      NESTED LOOPS      |                          |      1 |    157 |     13 |00:00:15.03 |   49917 |   6693 |       |       |          |
|   7 |       NESTED LOOPS     |                          |      1 |   2906 |  16424 |00:00:07.14 |   18634 |   3173 |       |       |          |
|*  8 |        INDEX RANGE SCAN| REPORT_ORDERS            |      1 |   2892 |  16899 |00:00:00.34 |     113 |    111 |       |       |          |
|*  9 |        INDEX RANGE SCAN| CC_REQUESTS_ORDER_ID     |  16899 |      1 |  16424 |00:00:06.77 |   18521 |   3062 |       |       |          |
|* 10 |       INDEX RANGE SCAN | PF_CC_TRANSACTIONS_FIN_1 |  16424 |      1 |     13 |00:00:07.88 |   31283 |   3520 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - access("O"."MERCHANT_ENTITY_ID"=100)
   9 - access(SYS_OP_UNDESCEND("O"."SYS_NC00025$")="CCR"."ORDER_ID")
  10 - access(SYS_OP_UNDESCEND("CCR"."SYS_NC00031$")="CCT"."CREDIT_CARD_REQ_ID" AND "CCT"."STATUS"=32 AND 
              "CCT"."CREATION_DATE">=TO_DATE(' 2019-03-31 22:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CCT"."CREATION_DATE"<=TO_DATE(' 2019-04-30 
              22:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  
复制

谢谢... 但是那个计划是不可读的!

请将其放在代码标签中 (如您的查询) 以保留格式。

并确保计划包括A行，E行和缓冲区列。

--

为了帮助解决性能问题，我们需要查看查询的执行计划。

通过运行得到这个:

set serveroutput off

alter session set statistics_level = all;



select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
复制

用你得到的计划更新这个问题。

在以下位置阅读有关此的更多信息:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan

=

附录

看起来像是选择性问题。第8行说您要进入100的商家ID，并且有16000这些商家。这意味着16,000对信用卡历史进行了调查，因此16000在第9行和第10行 “开始”。你可以尝试一些前导提示的变化，从CCT开始，并向后驱动 (这也需要轻弹状态到CCT索引的前面)，但我怀疑你需要看看物理设计的变化，以加快这一点，除非你有更多的谓词，你可以添加。