Partitioning a large financial table

0 pts.
Tags:
Database
DB2
Oracle
i hv a large table (GL details that is abt 30-50mb) that contains financial data. i want to partition it by these fields 1) company code 2) Quarter (based on the financial periods) 3) Year Eg, data Select GLCO, GLYEAR, (TO_CHAR(GLYEAR) ||'/'||TO_CHAR(GLMNTH)) As Yrmth, GLMNTH, GLAMT FROM GLPGL; GLCO GLYEAR Yrmth GKMNTH GLAMT 110 2004 2004/01 01 100000.00 110 2004 2004/02 02 150715.75 130 2004 2004/06 06 75588.00 120 2004 2004/07 07 88120.45 110 2005 2005/08 08 187548.99 ..... Partitioning by company code allows users to compare results across a financial period within a company (via a report which can retrieve details from this partitioned table, rather than its huge underlying table). For eg, users typically wanted to compare, 1) 1st quarter 2005 against 1st quarter 2004 2) this financial mth (eg, 06) against last financial mth (eg, 05) within the same year and same company code 3) 2nd quarter 2005 against 1st quarter 2005 4) 1st 6mths 2005 against 1st 6 mths 2004 etc... The quarter must be defined using the financial period (as there's no quarter column in the table). These are the financial quarters (make up of financial yr + mth) [Note: Financial period is defined as financial yr + financial mth] 200501 to 200503 200504 to 200506 200507 to 200509 200510 to 200512 How do i achieve this sort of partitioning to hv best results?
ASKED: January 13, 2006  3:20 AM
UPDATED: January 16, 2006  9:33 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

RDBMS used? version?

if that’s using Oracle financials there are partitioning schemes in the Apps that Oracle recommend using because of the way the applications access the data

cheers

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DonMonroe
    It depends on how you use it most often. I would first separate it by year. You can separate it by accounting year if that is how most of your reporting is done. After that I would break it on company and only put the companies together in reports if necessary. In any case, be sure you have indexes on Date and company so you can easily report on them.
    0 pointsBadges:
    report
  • Mayleong
    SORRY EVERYBODY dbms=ORACLE 9.2.2 Hopefully can provide some answers as to the sql i shld used?
    0 pointsBadges:
    report
  • Rabs80
    Why not just use views? You could create a view which subsets the data as you want it. Might be taling out a hole in my head as I don't have any Oracle experience but 30-50mb is not a large table. On our iSeries we have tables at over a gig!
    0 pointsBadges:
    report
  • Setirobert
    here one DDL to partition it even though 30-50 MB is still a small table create table glpgl (glco number(4),glyear number(4),glmnth number(2),glamnt number) partition by range (glyear,glmnth) (partition gl2004q4 values less than (2004,13) ,partition gl2005q1 values less than (2005,04) ,partition gl2005q2 values less than (2005,07) ,partition gl2005q3 values less than (2005,10) ,partition gl2005q4 values less than (2005,13) ,partition gl2006q1 values less than (2006,04) ,partition gl2006q2 values less than (2006,07) ,partition gl2006q3 values less than (2006,10) ,partition gl2006q4 values less than (2006,13) ,partition gloverflow values less than (MAXVALUE,MAXVALUE) ); you probably should just index the columns used by your queries
    30 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following