ok I was blindsided yesterday.
apparently I inherited this piece of code that has been around since 2007 with no problems but yesterday they mentioned the application was hanging
Anyway I found the offending select statement (and I'm not really sure what it supposed to be doing)
by I tried to recreate it.
it looks like it takes a certain row in a table and then partially joins the row to the same table.
then it goes to yet another table and does some sort of not exists thing
anyway it used to work ok because there were a minimal number of rows in the table (5 or 6) but apparently now there is some scenario where there can be upwards of 60,000 rows in the table.
so I tried to recreate what it is doing here
if you uncomment out the commented piece it will simulate the 40,000 row problem
with t as
( select 1 id, 'A' title from dual union
select 2, 'A' from dual union
select 3, 'A' from dual union
select 4, 'B' from dual union
select 5, 'C' from dual union
select 6, 'A' from dual union
select 7, 'A' from dual union
select 8, 'A' from dual union
select 9, 'C' from dual union
select 10, 'A' from dual
-- union select level + 10, 'A' from dual connect by level < 40000
),
u as
(select 1 id, 'Z' text from dual union
select 2, 'X' from dual union
select 3, 'Y' from dual union
select 4, 'W' from dual union
select 5, 'Z' from dual union
select 6, 'Y' from dual union
select 7, 'C' from dual union
select 8, 'R' from dual union
select 9, 'Q' from dual union
select 10, 'Z' from dual
)
select t1.id t1_id, t2.id t2_id, t1.title from
t t1, t t2
where t1.id != t2.id
and t1.title = t2.title
and not exists (select 'x'
from u u1, u u2
where t1.id = u1.id
and t2.id = u2.id
and u1.text != u2.text
)
order by t1_id
any thoughts?
Edited by: pollywog on May 5, 2010 10:08 AM