Thank you for reading and willingness to help me. Am basically a java developer and less knowledge on oracle analytics side.
There exists a materialized view that generates daily holdings of customer units which as days goes by slowed down a lot and certain days failed. When analysed, I found it could be better and easy way re-written.
My environment in windows 2003 running inside Hyper-V which allotted 3000 MB. Oracle Version :
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
"CORE 9.2.0.1.0 Production"
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
Problem statement: Am working on previously written Materialized view that ran every day night. As years goes by found it fails often and taken long time. So searching for ways to simplify as based on date range just creating rows for in between rows could help and its our requirement. Don't want to use all_objects as felt it could scan for each test_hold rows 30410 rows... "select count(*) from all_objects = 30410".
Following are our steps. 1:
create table test_hold(act_id varchar2(15), fm varchar2(10),
fund varchar2(10),start_dt date,end_date date,holding number(15,4));
2.
select * from test_hold;
ACT_ID FM FUND START_DATE END_DATE UNITS HOLDINGS
A0001 FM1 ABER001 10/03/2004 11/10/2015 100 100
A0001 FM1 ABER001 12/10/2015 20/10/2015 -100 0
A0002 FM2 FSTA001 14/05/2012 03/03/2013 250 250
A0002 FM2 FSTA001 04/03/2013 19/03/2014 300 550
A0002 FM2 FSTA001 20/03/2014 19/10/2015 -550 0
3. Expected output.
ACT_ID FM FUND TRAN_DATE HOLDNG
A0001 FM1 ABER001 10/03/2004 100
A0001 FM1 ABER001 11/03/2004 100
A0001 FM1 ABER001 12/03/2004 100
A0001 FM1 ABER001 …
A0001 FM1 ABER001 …
A0001 FM1 ABER001 11/10/2015 100
A0001 FM1 ABER001 12/10/2015 0
A0002 FM2 FSTA001 14/05/2012 250
I Tried Level - 1 ...Connect By and pipelined function. Found Level - 1 not suited for me. Felt Pipeline suited but when full customer set generated ended up with error like below. Also noticed when I try to create as a table with AS SELECT * from piped function, the oracle.exe memory shown at windows task manager keep on growing from 200000 k to > 1000000 k and never cleared unless restarted oracle service.
SQL>set timing on;
SQL> set autotrace traceonly statistics;
SQL> /
ERROR:
`
ORA-00600: internal error code, arguments: [kohdtf048], [], [], [], [], [], [],
[]
17536980 rows selected.
Elapsed: 00:17:34.06
Statistics
----------------------------------------------------------
20 recursive calls
0 db block gets
64632 consistent gets
2295 physical reads
0 redo size
389043748 bytes sent via SQL*Net to client
12860951 bytes received via SQL*Net from client
1169134 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
17536980 rows processed
Kindly help whether am proceeding correctly or any other simple alternative ways available. Thank you for your help.
