Hi Tom
I have the below query
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 ;
what is the best practice to get the result quickly,
where I must create indexes,
for info, all three tables are big and contain more than 50m records
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'))
Thanks... but that plan is unreadable!
Please place it in code tags (like your query) to preserve formatting.
And ensure that the plan includes the A-rows, E-rows, & buffers columns.
--
To help with performance problems, we need to see the query's execution plan.
Get this by running:
set serveroutput off
alter session set statistics_level = all;
<your query>
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
And update this question with the plan you get.
Read more about this at:
https://blogs.oracle.com/sql/how-to-create-an-execution-plan ================
Addenda
Looks a like selectivity issue. Line 8 says you are going in on merchant ID of 100, and there are 16000+ of those merchants. That means 16,000 probes into the credit card history, hence the 16000 "starts" on line 9 and 10. You could try some variations of the LEADING hint to start with CCT and drive backward to ORDER (which would also require flicking STATUS to the front of the index on CCT), but I suspect you will need to look at physical design changes to speed this up unless you have more predicates you can add.