Skip to Main Content
  • Questions

  • Need to duplicate the data for each day based on effective_start_date and effective_end_date column in a table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Vishwanatha.

Asked: September 11, 2015 - 9:31 am UTC

Last updated: September 18, 2015 - 3:45 am UTC

Version: 11g

Viewed 1000+ times

You Asked


Hi,

I have fact table which is storing daily average consumption Volume metric for a effective gap of date ranges. There is no overlapping dates ranges between the records. but there can be gap in between effective_end_date of first record and effective_start_date of next record in case of no consumption.
Example of sample data:
instance_number AVG_DAILY_VOLUME EFFECTIVE_START_DATE EFFECTIVE_END_DATE
1 100 01-Jan-2014 31-JAN-2014
1 105 01-FEB-2014 31-MAR-2014
1 102 01-APR-2014 30-APR-2014
1 107 01-JUN-2014 31-JUL-2015
2 200 01-JAN-2013 31-JAN-2013

Now I have requirement to split these records to each day level based on the effective_start_date and effective_end_date values.
Eg:

Instance_number AVG_DAILY_VOLUME DATE
1 100 01-Jan-2014
1 100 02-Jan-2014
...
1 100 31-Jan-2014
1 105 01-Feb-2014
..
1 105 31-MAR-2014
1 102 01-APR-2014
...
1 102 30-APR-2014
1 107 01-JUN-2014
..
1 107 31-Jul-2015

I could do this by joining with W_DAY_D table by joining DAY_D.DATE between effective_start_date and effective_end_date. But the data volume in fact table is huge though it is maintaining avg daily metric for a date ranges.
So this is taking ever to return me the results.
Is there a better approach to split this at a day level with out joining with W_DAY_D table with between clause. May be using connect_by clause?

Please assist.

Regards,
Vish

and Chris said...

What is the W_DAY_D table you refer to?

To convert your data into one row for each day then the basic approach you've outlined is correct: join to a complete list of dates where these are between the start and end:

create table t (instance_number int, volume int, start_dt date, end_dt date);
insert into t values (1, 100, '01-Jan-2014', '31-JAN-2014');
insert into t values (1, 105, '01-FEB-2014', '31-MAR-2014');
insert into t values (1, 102, '01-APR-2014', '30-APR-2014');

with dates as (
  select date'2013-12-31'+rownum dt
  from   dual
  connect by level <= 90
)
  select t.instance_number, t.volume, dt
  from   t, dates
  where  dt between start_dt and end_dt
  order  by dt;

INSTANCE_NUMBER     VOLUME DT                
--------------- ---------- -------------------
              1        100 01/01/2014 00:00:00
              1        100 02/01/2014 00:00:00
...
              1        100 30/01/2014 00:00:00
              1        100 31/01/2014 00:00:00
              1        105 01/02/2014 00:00:00
              1        105 02/02/2014 00:00:00
...
              1        105 27/02/2014 00:00:00
              1        105 28/02/2014 00:00:00
              1        105 01/03/2014 00:00:00
              1        105 02/03/2014 00:00:00
...
              1        105 30/03/2014 00:00:00
              1        105 31/03/2014 00:00:00

(output trimmed for readability).

You'll need to give us more information to help you with the performance aspect of this. At a minimum we need an execution plan output and table definitions.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the response

Vishwanatha Handadi, September 11, 2015 - 2:16 pm UTC

W_DAY_D is the OBIEE DAY dimension which holds the records for each of the valid days.

I tried to make use of connect by level from dual to have the valid dates for 10 days of September 2015 and tried to join with the target table and used between clause to split the records. But I am not getting the results. May be I am not seeing any difference between using W_DAY_D dimension and temp table using connect by clause on dual table.

I will try to get the explain plan and share further details.
Thanks for your help.
Regards,
Vish

With MODEL Clause this should be simple !

Rajeshwaran, Jeyabal, September 11, 2015 - 3:09 pm UTC 

rajesh@ORA11G> drop table t purge;
rajesh@ORA11G> create table t (instance_number int, volume int, start_dt date, end_dt date);
rajesh@ORA11G> insert into t values (1, 100, to_date('01-Jan-2014','dd-mon-yyyy'), to_date('31-JAN-2014','dd-mon-yyyy') );
rajesh@ORA11G> insert into t values (1, 105, to_date('01-FEB-2014','dd-mon-yyyy'), to_date('31-MAR-2014','dd-mon-yyyy') );
rajesh@ORA11G> insert into t values (1, 102, to_date('01-APR-2014','dd-mon-yyyy'), to_date('30-APR-2014','dd-mon-yyyy') );
rajesh@ORA11G> commit;
rajesh@ORA11G> set feedback on
rajesh@ORA11G>
rajesh@ORA11G> select instance_number,volume, to_date(x,'j') dts
  2  from t
  3  where volume = 100
  4  model return updated rows
  5    partition by (instance_number,volume)
  6    dimension by (1 x)
  7    measures( cast(null as date) new_dt, end_dt,start_dt)
  8    rules
  9    ( new_dt[ for x from to_number( to_char( start_dt[1] ,'j') )
 10                to to_number( to_char( end_dt[1] ,'j') ) increment 1 ] =  sysdate
 11    )
 12  /

INSTANCE_NUMBER     VOLUME DTS
--------------- ---------- -----------------------
              1        100 01-JAN-2014 12:00:00 AM
              1        100 02-JAN-2014 12:00:00 AM
              1        100 03-JAN-2014 12:00:00 AM
              1        100 04-JAN-2014 12:00:00 AM
              1        100 05-JAN-2014 12:00:00 AM
              1        100 06-JAN-2014 12:00:00 AM
              1        100 07-JAN-2014 12:00:00 AM
              1        100 08-JAN-2014 12:00:00 AM
              1        100 09-JAN-2014 12:00:00 AM
              1        100 10-JAN-2014 12:00:00 AM
              1        100 11-JAN-2014 12:00:00 AM
              1        100 12-JAN-2014 12:00:00 AM
              1        100 13-JAN-2014 12:00:00 AM
              1        100 14-JAN-2014 12:00:00 AM
              1        100 15-JAN-2014 12:00:00 AM
              1        100 16-JAN-2014 12:00:00 AM
              1        100 17-JAN-2014 12:00:00 AM
              1        100 18-JAN-2014 12:00:00 AM
              1        100 19-JAN-2014 12:00:00 AM
              1        100 20-JAN-2014 12:00:00 AM
              1        100 21-JAN-2014 12:00:00 AM
              1        100 22-JAN-2014 12:00:00 AM
              1        100 23-JAN-2014 12:00:00 AM
              1        100 24-JAN-2014 12:00:00 AM
              1        100 25-JAN-2014 12:00:00 AM
              1        100 26-JAN-2014 12:00:00 AM
              1        100 27-JAN-2014 12:00:00 AM
              1        100 28-JAN-2014 12:00:00 AM
              1        100 29-JAN-2014 12:00:00 AM
              1        100 30-JAN-2014 12:00:00 AM
              1        100 31-JAN-2014 12:00:00 AM

31 rows selected.

rajesh@ORA11G> set autotrace traceonly explain
rajesh@ORA11G> /

Execution Plan
----------------------------------------------------------
Plan hash value: 4004708133

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    23 |     3   (0)| 00:00:01 |
|   1 |  SQL MODEL ORDERED |      |     1 |    23 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    23 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("VOLUME"=100)

rajesh@ORA11G> set autotrace off
rajesh@ORA11G>


Feel free to comment out the "Where" clause to run it for the entire result sets.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.