Hi!
SUMMARY
I have a "small" local database and a huge remote database which I can access via a database link only. Trying to pull remote data, restricted by data from a local table...
SELECT * FROM remote_data@remote_system WHERE id IN (SELECT id FROM local_table);
...runs extremely long and finally returns an error (ORA-01652: unable to extend temp segment by 128 in tablespace TEMP), only
FYI:
select * from v$version;
yields:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
IN DETAIL:
I have SQL access to a reporting database and can access data from the production database via a database link, only.
On the reporting database, I defined a view like this:
CREATE VIEW remote_data AS (
SELECT
...
FROM
table1@prod a
JOIN table2@prod b ON b.col_x=a.col_x
JOIN table3@prod c ON c.col_y=b.col_y
);
A typical use on the reporting database would be:
SELECT * FROM remote_data WHERE id=123456789;
Now, I created table candidates with ~15,000 rows where one column is the id of which I want to pull the remote data. I tried:
SELECT * FROM remote_data WHERE id IN (SELECT id FROM id_list);
The query run ~3h and the stopped with the error message: "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP"
I tried to check not all id's at once, but looking at just 10 via:
SELECT * FROM remote_data WHERE id IN (SELECT id FROM id_list WHERE ROWNUM<=10);
But this had no effect. The query again runs for hours and finally just returns the same error message. Doing the same manually, works pretty fine. I.e. I did
SELECT id FROM id_list WHERE ROWNUM<=10;
first, and the created a query listing the id's as constant values in a list:
SELECT * FROM remote_data WHERE id IN (123456789, 223456789, 323456789, 423456789, ...);
This executed fine and returned the expected data in ~30 secs. So, I created the same with a list of all 15,000 id's. But I got an error. Lists may have only up to 1000 values. So, ok, I could split my 15000 id's into 15 blocks with 1000 id's each, but that's a cumbersome approach which doesn't scale. So I started searching.
It looks like the root issue is a join between a local and a remote table. Well, there is no JOIN in may query, but most-likely
SELECT * FROM remote_data WHERE id IN (SELECT id FROM id_list)
is optimized / executed as
SELECT a.* FROM remote_data a JOIN (SELECT id FROM id_list) b on b.id=a.id;
And it looks like the system would then (a) first try to execute (SELECT * FROM remote_data) on the prod system, then (b) copy the result to the reporting system and finally do the join with (SELECT id FROM id_list) locally. Due to the huge amount of data, this fails.
But it should be done the other way round! Assuming the query would be issued local on the prod database, it would first run (SELECT id FROM id_list) on the remote reporting system, send the resulting list of only 15000 id's back to the production system and finally do the select locally, restricted on that id's.
I found the DRIVING_SITE() hint and tried:
SELECT /*+ DRIVING_SITE(r) */
*
FROM
remote_data r
WHERE id IN (SELECT id FROM id_list where rownum<=10)
;
But it did not really work. Well, this did not run 3 hours anymore, but just a few mins. However, the only result I got again was: "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP"
Any hints / ideas?
Thx for your help!