问题描述:

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.

相关阅读:
Top