I am trying to run a query on a local Oracle DB and output the results to a .CSV file. The query runs in a decent amount of time (usually less than 5 minutes), but returns tens of thousands (possibly over 100,000) records. Upon export, the query runs through and fetches only 500 records at a time, and the whole process takes over an hour (very possibly longer!).
I have already tried some tricks that were not completely helpful (specifically turning off Display all results in grid), so I'm wondering if there's anything else I can do to get my data more quickly?
Here's the explain plan for the query. I only have read access to this db.
Plan hash value: 782492959
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 1860 | 110 (0)| 00:00:01 | | |
|* 1 | VIEW | | 3 | 36 | 2 (0)| 00:00:01 | | |
| 2 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7532_BC34A52C | 3 | 1677 | 2 (0)| 00:00:01 | | |
|* 3 | VIEW | | 3 | 36 | 2 (0)| 00:00:01 | | |
| 4 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7532_BC34A52C | 3 | 1677 | 2 (0)| 00:00:01 | | |
|* 5 | VIEW | | 3 | 36 | 2 (0)| 00:00:01 | | |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7532_BC34A52C | 3 | 1677 | 2 (0)| 00:00:01 | | |
|* 7 | VIEW | | 3 | 36 | 2 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7532_BC34A52C | 3 | 1677 | 2 (0)| 00:00:01 | | |
|* 9 | VIEW | | 3 | 36 | 2 (0)| 00:00:01 | | |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7532_BC34A52C | 3 | 1677 | 2 (0)| 00:00:01 | | |
|* 11 | VIEW | | 3 | 36 | 2 (0)| 00:00:01 | | |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7532_BC34A52C | 3 | 1677 | 2 (0)| 00:00:01 | | |
|* 13 | VIEW | | 3 | 36 | 2 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7532_BC34A52C | 3 | 1677 | 2 (0)| 00:00:01 | | |
|* 15 | VIEW | | 3 | 36 | 2 (0)| 00:00:01 | | |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7532_BC34A52C | 3 | 1677 | 2 (0)| 00:00:01 | | |
| 17 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 18 | LOAD AS SELECT | SYS_TEMP_0FD9D7532_BC34A52C | | | | | | |
| 19 | WINDOW SORT | | 3 | 1677 | 106 (0)| 00:00:01 | | |
| 20 | VIEW | | 3 | 1677 | 106 (0)| 00:00:01 | | |
| 21 | VIEW | | 3 | 1704 | 106 (0)| 00:00:01 | | |
| 22 | NESTED LOOPS | | | | | | | |
| 23 | NESTED LOOPS | | 3 | 1734 | 106 (0)| 00:00:01 | | |
| 24 | VIEW | | 3 | 1614 | 104 (0)| 00:00:01 | | |
|* 25 | COUNT STOPKEY | | | | | | | |
| 26 | VIEW | | 3 | 1614 | 104 (0)| 00:00:01 | | |
| 27 | CONCATENATION | | | | | | | |
| 28 | PARTITION RANGE SINGLE | | 1 | 514 | 35 (0)| 00:00:01 | 89 | 89 |
| 29 | PARTITION LIST SINGLE | | 1 | 514 | 35 (0)| 00:00:01 | 1 | 1 |
|* 30 | TABLE ACCESS BY LOCAL INDEX ROWID| BILL_HDR | 1 | 514 | 35 (0)| 00:00:01 | 529 | 529 |
|* 31 | INDEX RANGE SCAN | BILL_HDR_IE13 | 305 | | 1 (0)| 00:00:01 | 529 | 529 |
| 32 | PARTITION RANGE SINGLE | | 1 | 514 | 35 (0)| 00:00:01 | 89 | 89 |
| 33 | PARTITION LIST SINGLE | | 1 | 514 | 35 (0)| 00:00:01 | 1 | 1 |
|* 34 | TABLE ACCESS BY LOCAL INDEX ROWID| BILL_HDR | 1 | 514 | 35 (0)| 00:00:01 | 529 | 529 |
|* 35 | INDEX RANGE SCAN | BILL_HDR_IE13 | 305 | | 1 (0)| 00:00:01 | 529 | 529 |
| 36 | PARTITION RANGE SINGLE | | 1 | 514 | 35 (0)| 00:00:01 | 89 | 89 |
| 37 | PARTITION LIST SINGLE | | 1 | 514 | 35 (0)| 00:00:01 | 1 | 1 |
|* 38 | TABLE ACCESS BY LOCAL INDEX ROWID| BILL_HDR | 1 | 514 | 35 (0)| 00:00:01 | 529 | 529 |
|* 39 | INDEX RANGE SCAN | BILL_HDR_IE13 | 305 | | 1 (0)| 00:00:01 | 529 | 529 |
|* 40 | INDEX RANGE SCAN | BILL_STATUS_METRIC_IE9 | 1 | | 1 (0)| 00:00:01 | | |
|* 41 | TABLE ACCESS BY GLOBAL INDEX ROWID | BILL_STATUS_METRIC | 1 | 40 | 1 (0)| 00:00:01 | 529 | 529 |
|* 42 | HASH JOIN OUTER | | 3 | 1860 | 5 (20)| 00:00:01 | | |
| 43 | VIEW | | 3 | 1746 | 2 (0)| 00:00:01 | | |
| 44 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7532_BC34A52C | 3 | 1677 | 2 (0)| 00:00:01 | | |
| 45 | VIEW | | 3 | 114 | 2 (0)| 00:00:01 | | |
| 46 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7532_BC34A52C | 3 | 1677 | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FNL_C"."SYST_LCTN_ID"='078' AND "FNL_C"."ICN"=:B1)
3 - filter("FNL_C"."SYST_LCTN_ID"='079' AND "FNL_C"."ICN"=:B1)
5 - filter("FNL_C"."SYST_LCTN_ID"='073' AND "FNL_C"."ICN"=:B1)
7 - filter("FNL_C"."SYST_LCTN_ID"='074' AND "FNL_C"."ICN"=:B1)
9 - filter("FNL_C"."SYST_LCTN_ID"='166' AND "FNL_C"."ICN"=:B1)
11 - filter("FNL_C"."SYST_LCTN_ID"='066' AND "FNL_C"."ICN"=:B1)
13 - filter("FNL_C"."SYST_LCTN_ID"='068' AND "FNL_C"."ICN"=:B1)
15 - filter(("FNL_C"."SYST_LCTN_ID"='028' OR "FNL_C"."SYST_LCTN_ID"='031' OR "FNL_C"."SYST_LCTN_ID"='035' OR
"FNL_C"."SYST_LCTN_ID"='037' OR "FNL_C"."SYST_LCTN_ID"='038' OR "FNL_C"."SYST_LCTN_ID"='039' OR "FNL_C"."SYST_LCTN_ID"='174') AND
"FNL_C"."ICN"=:B1)
25 - filter(ROWNUM<=100000)
30 - filter("SRC1"."TTL_CHRG">0 AND "SRC1"."FNLZD_PAID_POSTING_DT"=TO_DATE(' 2013-02-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"SRC1"."BILLREVW_BE_ID"='008')
31 - access("SRC1"."BILL_ORGN_ID" LIKE 'GAL%')
filter("SRC1"."BILL_ORGN_ID" LIKE 'GAL%')
34 - filter("SRC1"."TTL_CHRG">0 AND "SRC1"."FNLZD_PAID_POSTING_DT"=TO_DATE(' 2013-02-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"SRC1"."BILLREVW_BE_ID"='008')
35 - access("SRC1"."BILL_ORGN_ID" LIKE 'GEE%')
filter("SRC1"."BILL_ORGN_ID" LIKE 'GEE%' AND LNNVL("SRC1"."BILL_ORGN_ID" LIKE 'GAL%'))
38 - filter("SRC1"."TTL_CHRG">0 AND "SRC1"."FNLZD_PAID_POSTING_DT"=TO_DATE(' 2013-02-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"SRC1"."BILLREVW_BE_ID"='008')
39 - access("SRC1"."BILL_ORGN_ID" LIKE 'GSA%')
filter("SRC1"."BILL_ORGN_ID" LIKE 'GSA%' AND LNNVL("SRC1"."BILL_ORGN_ID" LIKE 'GEE%') AND LNNVL("SRC1"."BILL_ORGN_ID" LIKE
'GAL%'))
40 - access("SRC2"."DW_BILL_HDR_ROW_ID"="TEMP_B"."LNK")
41 - filter("SRC2"."BILLREVW_BE_ID"='008' AND "SRC2"."FNLZD_PAID_POSTING_DT"=TO_DATE(' 2013-02-15 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "SRC2"."LAST_ACTN_USER" IS NOT NULL)
42 - access("FNL_A"."LNK"="FNL_B"."LNK"(+) AND "FNL_A"."ROW_NUMBER"="FNL_B"."ROW_NUMBER"(+)+1)
UPDATE
I tried improving the query by eliminating certain calculation steps that can be performed after the fact (e.g. Excel - though ideally this should all be done in SQL at the first step, so as to prevent errors later in processing), and while this still takes some time, the export/fetch of all records appears to be running significantly faster.
Perhaps the issue is in my confusion about what is initially displayed in the data grid. Are the records shown here simply an output of the query with an additional constraint added WHERE ROWNUM<=500?
