complex subquery error

Hi,

I have a complex query that runs successfully on an on Oracle 8i database but errors on a 9i database. Behold the query:

select cd.company_id||'~'||x.report_instance_id||'~"'||cd.company_name||'"~'||
to_char(rts1.financial_year, 'yyyymmdd')||'~'||ri1.industry_code||'~'||ri2.industry_code||'~'||
rts1.series_name||'~'||rts1.number_value||'~'||rts2.number_value
from (
select rs.company_id, rs.report_instance_id, max(ri.report_instance_id) prev_RID
from report_set rs, rpt_instance ri
where rs.set_name = :SetName
and rs.report_instance_id is not null
and ri.company_id = rs.company_id
and rs.inactive_flag = 0
and ri.report_instance_id <> rs.report_instance_id
and ri.generation_finish_time < sysdate - 30
group by rs.company_id, rs.report_instance_id, ri.industry_code) X,
company_details cd,
rpt_instance ri1, rpt_instance ri2,
rpt_time_series rts1, rpt_time_series rts2
where cd.company_id = X.company_id
and ri1.report_instance_id = X.REPORT_INSTANCE_ID
and rts1.report_instance_id = X.REPORT_INSTANCE_ID
and rts1.series_name = 'ST_OVERALL'
and ri2.report_instance_id = X.PREV_RID
and rts2.report_instance_id = X.PREV_RID
and rts2.series_name = rts1.series_name
and rts2.financial_year = rts1.financial_year
and rts2.number_value <> rts1.number_value
order by cd.company_name, rts1.financial_year;

The subquery X is causing all of the grief. If I insert that query resulset into a normal table and then join on that table, the query works fine in the 9i database.

When I run it the query 'thinks' for several minutes, then crashes the session with this error:
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

The database has a 4GB TEMP tablespace, and it seems hard to believe that this query is consuming all temporary memory.

Here's the db versions:
SQL> select * from v_$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

Does anyone have any idea what is causing this?

This is not a sql error, meaning that syntax is valid, you can check it with an explain, which should also tell you if there is any carthesian product, which might explain your error

No, there isn't a cartesian join. As I said, this query runs find on Oracle 8i. Do I have the wrong version of 9i?

check the maxextents of your temp tablespace. Also the size of it, I know you said 4G but that sounds rather large, y would you have that big of a temp tablespace?

Please do the most obvious thing, check that query with explain and post here the results if you need more help

Here's the plan from the 8i database:
SQL> execute show_plan;
SELECT STATEMENT Cost = 8
SORT ORDER BY Cost = 8
NESTED LOOPS Cost = 7
NESTED LOOPS Cost = 6
NESTED LOOPS Cost = 6
VIEW Cost = 6
SORT GROUP BY Cost = 6
NESTED LOOPS Cost = 4
TABLE ACCESS FULL RR_OWNER.REPORT_SET Cost = 4
TABLE ACCESS BY INDEX ROWID RR_OWNER.RPT_INSTANCE
INDEX RANGE SCAN RR_OWNER.PK_RPT_INSTANCE
TABLE ACCESS BY INDEX ROWID RR_OWNER.RPT_TIME_SERIES
INDEX RANGE SCAN RR_OWNER.PK_RPT_TIME_SERIES
TABLE ACCESS BY INDEX ROWID RR_OWNER.RPT_TIME_SERIES
INDEX UNIQUE SCAN RR_OWNER.PK_RPT_TIME_SERIES
TABLE ACCESS BY INDEX ROWID RR_OWNER.COMPANY_DETAILS Cost = 1
INDEX UNIQUE SCAN RR_OWNER.PK_COMPANY_DETAILS

Explining the query on 9i gives this error:
ERROR:
ORA-03114: not connected to ORACLE


explain plan for
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

hedge: I don't know the maxextents of the TEMP tablespace. How do you do that? Here's what I have:
SQL> select * from v_$temp_extent_pool;

TABLESPACE_NAME FILE_ID EXTENTS_CACHED EXTENTS_USED BLOCKS_CACHED BLOCKS_USED BYTES_CACHED BYTES_USED RELATIVE_FNO
------------------------------ ---------- -------------- ------------ ------------- ----------- ------------ ---------- ------------
TEMP 1 0 0 0 0 0 0 1
TEMP 2 3999 0 511872 0 4193255424 0 2

I don't think it's that. TEMP grew to be that large with we imp'd some data.

This is seeming more and more like I need a patch.

Well, if it bombs out just doing an explain I think you hit a bug, check MetaLink and upgrade to the latest Oracle version/patchset