On Mar 16, 5:20 pm, "joel garry" <joel-ga..._at_home.com> wrote:
> On Mar 16, 7:45 am, "gl" <stin..._at_pd.jaring.my> wrote:
>
> > Hey guys, need expect opinion on table design and access. For a loan
> > record which going to store 300 months of principal outstanding and
> > interest outstanding. Which is a better choice?
>
> > 1) Create a table with data structure as
> > loan_no, month_no, prin_os, int_os
>
> > 2) create a table with
> > loan_no, p_mth1, p_mth2, p_mth3 ... p_mth300.
>
> > The first method will have million of records which cause I/O
> > contention, the second method will have issue on selecting data
> > randomly. Is there any other way for this design? Thank you.
>
> There is an option in Oracle called partitioning. When you access the
> data, the optimizer can do "partition pruning" to cull out the data
> you are not interested in. Also, you can use it for rapid data loads
> or truncation if you are using a 300 month window. That is one reason
> to go with the first method.
>
> The only reason to go with the second method is to have people laugh
> at you.
>
> There may be other ways, but there is insufficient problem definition
> to say much else.
>
> jg
> --
> @home.com is bogus.
> "The board's greatest contribution in 2006 was introducing the word
> 'pretexting' into the American lexicon."http://www.signonsandiego.com/uniontrib/20070315/news_1b15hp.html
> Yeah, we used to call it Social Engineering.
Also you need to consider whether it is for OLTP (transaction
processing system) or for data warehouse (DW). In DW scenario,
somethimes the de-normalizes structures win due to querying needs. See
http://OracleBIWA.org for some related webcasts.
Thanks
Shyam
Received on Sat Mar 17 2007 - 18:42:18 CDT
