Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2013

    Design question: Use partition tables or 1 wide table with indecies?

    I am working on a report DB schema and, as I am not a DBA (a backend Java developer) this is a bit outside my knowledge zone.

    I need to be able to fetch records frequently by date; fixed date ranges too (Like, Q4, 2012, etc.) It had occurred to me that I could keep the the query times way down by creating new tables on the fly as moves on and essentially naming them by YYYY_MM -- that sharding certainly reduces the size of a given table.

    Then it occurred to me: This is the logical equivalent of creating a inverse index on a particular column ("2012_03" --> records{3,9,123,431...}) so why not just add extra columns to a single table, making it wider, to account for the fields you need. Then you can easily do things like: compare Q4 this year to Q4 last year simply by
    "select * from XXX where quarter = 4 and year in (2012,2013)"
    it also makes counting occurrences easier.

    If I partition this data into tables then putting it all back together becomes painful.

    Hoping for a discussion on both approaches.

  2. #2
    Join Date
    Jun 2003
    No sharding.
    Just keep it all in one table, properly indexed.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

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