How to avoid expensive Cartesian product using row generator

Question

I'm working on a query (Oracle 11g) that does a lot of date manipulation. Using a row generator, I'm examining each date within a range of dates for each record in another table. Through another query, I know that my row generator needs to generate 8500 dates, and this amount will grow by 365 days each year. Also, the table that I'm examining has about 18000 records, and this table is expected to grow by several thousand records a year.

The problem comes when joining the row generator to the other table to get the range of dates for each record. SQLTuning Advisor says that there's an expensive Cartesian product, which makes sense given that the query currently could generate up to 8500 x 18000 records. Here's the query in its stripped down form, without all the date logic etc.:

with n as (
  select level n
  from dual 
  connect by level <= 8500
 )
select t.id, t.origdate + n origdate    
from (
    select id, origdate, closeddate
    from my_table
) t
join n on origdate + n - 1 <= closeddate -- here's the problem join
order by t.id, t.origdate;

Is there an alternate way to join these two tables without the Cartesian product?

I need to calculate the elapsed time for each of these records, disallowing weekends and federal holidays, so that I can sort on the elapsed time. Also, the pagination for the table is done server-side, so we can't just load into the table and sort client-side.

The maximum age of a record in the system right now is 3656 days, and the average is 560, so it's not quite as bad as 8500 x 18000; but it's still bad.

I've just about resigned myself to adding a field to store the opendays, computing it once and storing the elapsed time, and creating a scheduled task to update all open records every night.

Could you explain why do you need to generate so much records ? 8500 x 18000 = 15.300.000 rows, it must be slow. — krokodilko, Feb 18 '14 at 21:19

score 0 · Answer 1 · answered Feb 18 '14 at 20:09

0

I think that you would get better performance if you rewrite the join condition slightly:

with n as (
  select level n
  from dual 
  connect by level <= 8500
 )
select t.id, t.origdate + n origdate    
from (
    select id, origdate, closeddate
    from my_table
) t
join n on Closeddate - Origdate + 1 <= n --you could even create a function-based index
order by t.id, t.origdate;

answered Feb 18 '14 at 20:09

Joseph B

5,519
1
15
19

that did help a little bit, thanks, but not anywhere near enough. – earachefl Feb 19 '14 at 19:13

How to avoid expensive Cartesian product using row generator

1 Answers1