oracle - How to avoid expensive Cartesian product using row generator -

I am working on a query (Oracle 11G) which manipulates a lot of dates by using a line generator , I am testing every date within the dates of each record in another table of each table. Through another query, I know that my line generator needs to generate 8500 dates, and this amount will increase by 365 days each year. Also, there are about 18000 records about the table I see, and this table is expected to have several thousand records in one year.

The problem comes in the line generator in the second table to get the date range for each order. The SQL Tuning Advisor says that there is an expensive cartesian product, which makes sense that the query can currently reach 8500 x 18000 records. Here is the query in all of your empty arguments without dates:

 with  n (choose Level from Level Dual Level Level & lt; = 8500) select t.id, t T + n - 1 generated from RGDT + n (select ID, origin, date closed with my_table) & lt; Join N = close - Here the problem is sorted by t.id, t.origdate;  

Is there an alternative way to join these two tables without a cartian product?

I need to calculate the time elapsed for each of these records, weekends and federal vacations, so that I can sort through time. In addition, the page is server-side pointing for the table, so we can not just load it in the table and sort client-side.

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

Once upon a time I have resigned to add one area to resign myself, to compute it once and for the time being, and to update all open records every night. Make a scheduled work for

I think if you have to write down the condition of joining you, Will receive:

(select level from double level connected to level & lt; = 8500) select t.id, start with t.origdate + n (select ID from my_table, Genesis , Discontinued) Origin + 1 & lt; = N - yy also function-based index order tid, t. Can be made by ogate;