Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical Rollup Question
Hierarchical Rollup Question [message #184199] Tue, 25 July 2006 12:41 Go to next message
ericr
Messages: 22
Registered: January 2006
Junior Member
Hello,

I have a table with 18K+ rows in a hierarchical structure.

I am using Oracle 10g XE, Windows XP Pro

CREATE TABLE BDN_HOLD_C6_NEW
(
CODE CHAR(200 BYTE),
CODEDESC CHAR(250 BYTE),
BDN_LEVEL NUMBER,
TAG CHAR(250 BYTE),
CA CHAR(150 BYTE),
WP CHAR(250 BYTE),
PARENT CHAR(150 BYTE),
BAC NUMBER,
EAC NUMBER,
VAC NUMBER,
P_CMP NUMBER,
P_SPN NUMBER,
BCWS_CUM NUMBER,
BCWP_CUM NUMBER,
ACWP_CUM NUMBER,
CV_CUM NUMBER,
SV_CUM NUMBER,
BCWS_CUR NUMBER,
BCWP_CUR NUMBER,
ACWP_CUR NUMBER,
CV_CUR NUMBER,
SV_CUR NUMBER,
WEIGHT CHAR(1 BYTE),
FLAG CHAR(1 BYTE),
SEQNO NUMBER
)

INSERT INTO COBRA.BDN_HOLD_C6_NEW (CODE, CODEDESC, TAG, CA, WP, BAC, EAC, VAC, P_CMP, P_SPN, BCWS_CUM, BCWP_CUM, ACWP_CUM, CV_CUM, SV_CUM, BCWS_CUR, BCWP_CUR, ACWP_CUR, CV_CUR, SV_CUR)
VALUES (CODE, CODEDESC, BDN_LEVEL, A, 111111111, 111111111,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)

INSERT INTO COBRA.BDN_HOLD_C6_NEW (CODE, CODEDESC, TAG, CA, WP, BAC, EAC, VAC, P_CMP, P_SPN, BCWS_CUM, BCWP_CUM, ACWP_CUM, CV_CUM, SV_CUM, BCWS_CUR, BCWP_CUR, ACWP_CUR, CV_CUR, SV_CUR)
VALUES (CODE, CODEDESC, BDN_LEVEL, AA, Cost Account 1, 111111111,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)

INSERT INTO COBRA.BDN_HOLD_C6_NEW (CODE, CODEDESC, TAG, CA, WP, BAC, EAC, VAC, P_CMP, P_SPN, BCWS_CUM, BCWP_CUM, ACWP_CUM, CV_CUM, SV_CUM, BCWS_CUR, BCWP_CUR, ACWP_CUR, CV_CUR, SV_CUR)
VALUES (CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 1,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9)

INSERT INTO COBRA.BDN_HOLD_C6_NEW (CODE, CODEDESC, TAG, CA, WP, BAC, EAC, VAC, P_CMP, P_SPN, BCWS_CUM, BCWP_CUM, ACWP_CUM, CV_CUM, SV_CUM, BCWS_CUR, BCWP_CUR, ACWP_CUR, CV_CUR, SV_CUR)
VALUES (CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 2,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9)

INSERT INTO COBRA.BDN_HOLD_C6_NEW (CODE, CODEDESC, TAG, CA, WP, BAC, EAC, VAC, P_CMP, P_SPN, BCWS_CUM, BCWP_CUM, ACWP_CUM, CV_CUM, SV_CUM, BCWS_CUR, BCWP_CUR, ACWP_CUR, CV_CUR, SV_CUR)
VALUES (CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 3,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9)

INSERT INTO COBRA.BDN_HOLD_C6_NEW (CODE, CODEDESC, TAG, CA, WP, BAC, EAC, VAC, P_CMP, P_SPN, BCWS_CUM, BCWP_CUM, ACWP_CUM, CV_CUM, SV_CUM, BCWS_CUR, BCWP_CUR, ACWP_CUR, CV_CUR, SV_CUR)
VALUES (CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 4,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9)

What I am trying to to is rollup the totals all the way up. The table size is around 18K records.

Here is what I have tried so far:

    <<final_updates_loop>>
    for y in (SELECT SEQNO,TAG FROM bdn_hold_c6_new ORDER BY tag)
    loop
        select sum(bac),sum(eac),sum(vac),sum(bcws_cum),sum(bcwp_cum),sum(acwp_cum),sum(cv_cum),sum(sv_cum),sum(bcws_cur),sum(bcwp_cur),sum(acwp_cur),sum(cv_cur),sum(sv_cur)
        into sumBAC,sumEAC,sumVAC,sumBCWSc,sumBCWPc,sumACWPc,sumCVc,sumSVc,sumBCWS,sumBCWP,sumACWP,sumCV,sumSV
        from bdn_hold_c6_new where trim(tag) like (trim(y.tag) || '%');

        update bdn_hold_c6_new set BAC=sumBAC,EAC=sumEAC,VAC=sumVAC,BCWS_CUM=sumBCWSc,BCWP_CUM=sumBCWPc,ACWP_CUM=sumACWPc,CV_CUM=sumCVc,SV_CUM=sumSVc,BCWS_CUR=sumBCWS,BCWP_CUR=sumBCWP,ACWP_CUR=sumACWP,CV_CUR=sumCV,SV_CUR=sumSV where seqno = y.seqno;

    end loop final_updates_loop;


This MAY be working, but after 20 minutes I canceled the execution. Any pointers on how I can do this rollup? I wanted to update the existing table with the rollup data for later presentation.

The columns that needed totaling/rollup are (BAC,EAC,VAC,P_CMP,P_SPN,BCWS_CUM,BCWP_CUM,ACWP_CUM,CV_CUM,SV_CUM, BCWS_CUR, BCWP_CUR,ACWP_CUR,CV_CUR,SV_CUR)

This is the way the table needs to look after the rollup:
CODE, CODEDESC, BDN_LEVEL, A, 111111111, 111111111,9.36,12,16,20,24,28,32,32,32,32,24,28,32,36
CODE, CODEDESC, BDN_LEVEL, AA, Cost Account 1, 111111111,9.36,12,16,20,24,28,32,32,32,32,24,28,32,36
CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 1,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9
CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 2,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9
CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 3,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9
CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 4,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9

Thanks...
Re: Hierarchical Rollup Question [message #184246 is a reply to message #184199] Tue, 25 July 2006 21:01 Go to previous messageGo to next message
ericr
Messages: 22
Registered: January 2006
Junior Member
Any ideas?
Re: Hierarchical Rollup Question [message #184247 is a reply to message #184199] Tue, 25 July 2006 21:10 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
I suspect that the SELECT inside the loop is doing a FTS.
Then for each row in the table, a FTS will occur.
This will take a while to complete the way it is coded.
You can verify what actually is happening by
ALTER SESSION SET SQL_TRACE=TRUE;
& anaylzing the results with TKPROF.
Re: Hierarchical Rollup Question [message #184251 is a reply to message #184247] Tue, 25 July 2006 22:14 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The TRIM() function will be stopping you from using an index. Replace all of your CHAR() datatypes with VARCHAR2 datatypes, then you wont need to TRIM() any more, and it will use an index on TAG.

Ross Leishman
Re: Hierarchical Rollup Question [message #184295 is a reply to message #184199] Wed, 26 July 2006 01:18 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

First of all ,

Your question on not clear . (Atleast to me )

Insert script doesn't fit to its positions.

You didn't mention the SEQNo anywhere except in your code.


Not sure How and why only the Null values are updated in your desired output ..

Post it with more explanation.

Thumbs Up
Rajuvan.

[Updated on: Wed, 26 July 2006 01:18]

Report message to a moderator

Re: Hierarchical Rollup Question [message #184443 is a reply to message #184295] Wed, 26 July 2006 10:00 Go to previous message
ericr
Messages: 22
Registered: January 2006
Junior Member
rajavu1 wrote on Wed, 26 July 2006 01:18

First of all ,

Your question on not clear . (Atleast to me )

Insert script doesn't fit to its positions.

You didn't mention the SEQNo anywhere except in your code.


Not sure How and why only the Null values are updated in your desired output ..

Post it with more explanation.

Thumbs Up
Rajuvan.



You're right, my question is unclear. I apologize. Let me try to clear it up.

I have a table with 18K+ rows, these rows are organized in a hierarchical structure according to a column called "tag" at the lowest level of the hierarchy numerical totals exist. I want to roll those totals up the hierarchy.

Here is an example of that data structure:

Code-1 AAA Need Totals Rollup
  Code-2 AAAA Need Totals Rollup
    Code-3 AAAAA Need Totals Rollup
      Cost Account-1 AAAAAA Need Totals Rollup
        WorkPackage-1 AAAAAAA Totals Exist Here
        WorkPackage-2 AAAAAAB Totals Exist Here
        WorkPackage-3 AAAAAAC Totals Exist Here
      Cost Account-2 AAAAAB Need Totals Rollup
        WorkPackage-1 AAAAABA Totals Exist Here
        WorkPackage-2 AAAAABB Totals Exist Here
        WorkPackage-3 AAAAABC Totals Exist Here



What I need is for the totals at the work package level to rollup all the way up the hierarchy according to their tag. The second column in the example is the hierarchy column "tag".

The structure of the data is immutable.

I hope this is more clear.

Thanks!

[Updated on: Wed, 26 July 2006 10:04]

Report message to a moderator

Previous Topic: IN OUT BOOLEAN IN A PROCEDURE
Next Topic: Which Oracle Release Do You Use
Goto Forum:
  


Current Time: Mon Dec 05 08:50:29 CST 2016

Total time taken to generate the page: 0.09254 seconds