Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Question Unanswered: Tables with large number of rows

    Hello

    I need to develop an application to record occurrences in a database. This database will be Oracle 10g Enterprise. The problem is that may be registered a large number of occurrences (rows) per day. I think it would not be ideal to store everything in a single table. I was thinking of a way to divide into multiple tables as needed. For example, one table per year and when the year is a new, a new table is created automatically. Is it possible?
    But if I make a query, it will search in all available tables.
    Who can give me a hint, it will be helpful.
    Thanks very much!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    PARTITIONING is an extra cost option, but it does what you desire
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by AnaPaula View Post
    The problem is that may be registered a large number of occurrences (rows) per day
    Define "large".

    For example, one table per year and when the year is a new, a new table is created automatically. Is it possible?
    Is it possible? Yes.
    Do you really want to do that? No!

    Just image a query that shows you the average number of e.g. sales amount for the years 2006 to 2009 where start and end year are entered by an end user.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    I would also suggest "No" at first. Build a single table, and if performance over time becomes a serious issue, then consider options like Partitioning. Depending upon the size of your shop, partitioning may be expensive (it's sold at a particular cost *.5 * number of cores).

    You never know, queries across all of the data could be performed off-hours, and then performance woudn't be an issue. Or, you could set up materialized views for common queries which would otherwise scan all of the data. Or maybe the right multi-column index could provide you with quick results.

    It's a balance between being pro-active, and fixing a problem which may not exist in the future.

    --=Chuck
    Last edited by chuck_forbes; 11-23-09 at 17:27.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    It all boils down to what AnaPauly considers "large".

    I have seen people considering a table with 100.000 rows "large" and I have seen people that considered a table with 25 million rows "medium-sized".

    It all depends where you come from.

    With decent hardware (good raid-10 system, and many disks) even 100 million rows shouldn't be a problem in a single table.

    The problem arises when you want to get rid off parts of the data. That's where partitioning is really handy.

    A "DELETE FROM large_table" that deletes 50% of 250 million rows can take some time.
    Dropping the corresponding partition is a matter of seconds.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    We have single tables with almost 100 million rows and they respond quicker then you can snap your fingers.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by beilstwh View Post
    We have single tables with almost 100 million rows and they respond quicker then you can snap your fingers.
    That's exactly what I meant

  8. #8
    Join Date
    Nov 2009
    Posts
    2
    Thank you all for the tips. Responding to questions, the base will store about 100,000 lines per day. The system will run every day of the year, entering in the base that amount every day. Most queries will be just the last 10,000 records and will be done every 500 milliseconds.. Is it really a good idea to keep it all in a single table? I wonder why. Thank you.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by AnaPaula View Post
    Is it really a good idea to keep it all in a single table?
    Yes

    I wonder why
    Because it's a clean database model.
    The question still boils down what parts of the data you access and how.

    How and when do you access "old data"?
    How long do you access/keep "old" data?
    Do you have queries spanning more than one year (say 2000 to 2009)
    Do you have queries that query data from an arbitrary number of years (say 2001, 2005, 2007)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •