I populate data in global temporary table (around 1 million rows) and data is used for report. The below SQL does not finish when I am running it on the global temporary table ar_recon_aging_tmp.
I copied the data from global temporary table to a normal Oracle table and created similar indexes. I chagned the name of the table in the from clause. I ran the below SQL on permanetn table and SQL finished in less than a minute. The explain plan on both the tables is also same. The subquery return 8000 records and bl_fc_aging is a permanent table.
Please advise why the SQL is slow on global temporary table.
SELECT /*+ index(a ARAT_BY_INV_NO) */
a.cash_uid v_cash_uid_2,
a.bl_no v_bl_no_2,
a.inv_no v_inv_no_2,
a.xns_dt v_actg_dt_2,
a.inv_dt v_inv_dt_2,
a.local_amount v_local_amount_2,
'ADV - DATE' v_reason_2
--from ar_recon_aging_tmp_de a
FROM ar_recon_aging_tmp a
WHERE a.country = 'DE'
AND a.rec_typ IN ('P', 'A')
AND a.xns_dt < to_date('30-May-2009', 'dd-Mon-YYYY') + 1
AND a.exec_hist_uid = 800022855586
AND a.inv_no IS NOT NULL
AND a.inv_no IN
(SELECT /*+ index(a2 DFAG_BY_CTRY_INV_XNS_DT) */
a2.inv_no
FROM bl_fc_aging a2
WHERE a2.rec_typ IN ('S', 'C')
AND a2.inv_dt > to_date('30-May-2009', 'dd-Mon-YYYY') + 1
AND a2.country = 'DE')
/
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 3165773756
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 108 | 302 (0)| 00:00:04 |
| 1 | NESTED LOOPS SEMI | | 1 | 108 | 302 (0)| 00:00:04 |
|* 2 | TABLE ACCESS BY INDEX ROWID| AR_RECON_AGING_TMP | 1 | 79 | 1 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN | ARAT_BY_INV_NO | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| BL_FC_AGING | 594 | 17226 | 301 (0)| 00:00:04 |
|* 5 | INDEX RANGE SCAN | DFAG_BY_CTRY_INV_XNS_DT | 1188 | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."COUNTRY"='DE' AND "A"."XNS_DT"<TO_DATE(' 2009-05-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "A"."EXEC_HIST_UID"=800022855586 AND ("A"."REC_TYP"='A' OR "A"."REC_TYP"='P'))
3 - filter("A"."INV_NO" IS NOT NULL)
4 - filter(("A2"."REC_TYP"='C' OR "A2"."REC_TYP"='S') AND
"A"."INV_NO"=NVL("A2"."INV_NO",'@@@'))
5 - access("A2"."COUNTRY"='DE' AND "A2"."INV_DT">TO_DATE(' 2009-05-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "A2"."INV_DT" IS NOT NULL)
23 rows selected.