sql - Find all dates between 2 different date ranges for each row in a huge volume of table data - Stack Overflow

admin管理员组

文章数量:1422504

We have a table containing 100 million records where we need to find all dates between date ranges present in each row of the table.

Tabl name: item_details

----------------------------------------
Item_no | item_type | active_from | active_to |
----------------------------------------
10001|AAC|2023-01-01|2099-12-31|
--------------------------------
10002|BOC|2024-06-03|2025-10-01
--------------------------------
10003|COD|2025-01-01|2025-01-01
---------------------------------

Output
Item_no|item_type|active_dates
----------------------------------------
10001|AAC|2023-01-01|
10001|AAC|2023-01-02|
10001|AAC|2023-01-03|
:
:
10001|AAC|2099-12-30|
10001|AAC|2099-12-31|
10002|BOC|2024-06-03|
10002|BOC|2024-06-04|
:
:
10002|BOC|2025-09-30|
10002|BOC|2025-10-01|
10003|COD|2025-01-01|
--------------------------------

I tried cross join and connect by level, but it is taking huge time to print result as date is almost 75 years +..so looping through each record, it gets hung.

tried connect by level by passing date from each row, but query got hang without any results...

We have a table containing 100 million records where we need to find all dates between date ranges present in each row of the table.

Tabl name: item_details

----------------------------------------
Item_no | item_type | active_from | active_to |
----------------------------------------
10001|AAC|2023-01-01|2099-12-31|
--------------------------------
10002|BOC|2024-06-03|2025-10-01
--------------------------------
10003|COD|2025-01-01|2025-01-01
---------------------------------

Output
Item_no|item_type|active_dates
----------------------------------------
10001|AAC|2023-01-01|
10001|AAC|2023-01-02|
10001|AAC|2023-01-03|
:
:
10001|AAC|2099-12-30|
10001|AAC|2099-12-31|
10002|BOC|2024-06-03|
10002|BOC|2024-06-04|
:
:
10002|BOC|2025-09-30|
10002|BOC|2025-10-01|
10003|COD|2025-01-01|
--------------------------------

I tried cross join and connect by level, but it is taking huge time to print result as date is almost 75 years +..so looping through each record, it gets hung.

tried connect by level by passing date from each row, but query got hang without any results...

• sql
• oracle-database
• plsql

Share Improve this question Follow edited Jan 18 at 0:26 Narasimhan M asked Jan 17 at 17:50 Narasimhan MNarasimhan M 1933 bronze badges 4

• 4 If you have 100M records and you need to explode a fromdate/todate into individual dated rows and your average date range is 1 year you are going to end up with 36.5B records. I'm guessing at the average, but if you have lots of end-dated records of 2099-12-31 then that average may be underestimating. At any rate, this is very solvable with a calendar table if you are prepared for the sizable result set. – JNevill Commented Jan 17 at 18:21
• 1 CONNECT BY is known to consume lot of memory and generating all dates between now (or the max date before 2099) and December 2099 is it reallly usefull from a business point of view? So first step could be a merge of existing intervals (with MATCH_RECOGNIZE) and stored in MATERIALIZED VIEW, then JOINed with a calendar table. There is also a good session on DATE indexing in the AskTOM Office hours sessions. – p3consulting Commented Jan 18 at 8:49
• 1 What are you going to do with those billions of rows that your query shall generate? – Thorsten Kettner Commented Jan 19 at 10:28
• does your table has any index or partitioning on your desired date row? – hmmftg Commented Jan 27 at 13:57

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I'd tend to materialize the connect by level into a table

CREATE TABLE dates (t DATE) PCTFREE 0;

INSERT INTO dates (t) 
SELECT DATE '2022-12-31' + level FROM dual CONNECT BY LEVEL < 365.25*77; 
EXEC dbms_stats.gather_table_stats(null, 'dates');

and then join this table to your 100 million row table

   SELECT i.item_no, i.item_type, d.t as active_dates
     FROM item_details i
     JOIN dates d ON d.t BETWEEN i.active_from AND i.active_to;

本文标签：