Re: Opinion on table structure and accessibility

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.