Long/Skinny table options
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Long/Skinny table options

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

    Long/Skinny table options

    I'm building a table that will have 4 columns and a bazzzillion rows.

    PK, date, varchar, clob (varray)

    I'm considering Index organized and partitioning on date range.

    The data will be added to (5k-50k rows/day), but not removed for 7 years or more. It will be queried by date.

    What's the best way to store this data?

    If I use IOT, will I need to rebuild periodically?

    Is it unreasonable to have a partition per day?

    What's the best way to create partitions (cron job or pre-created for 10 years)?

    Thanks,

    Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Do you have a feel for what sort of date range will commonly be used when querying the table?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Quote Originally Posted by slimdave
    Do you have a feel for what sort of date range will commonly be used when querying the table?
    Generally 1 day, but occasionally any date range.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    For 127,750,000 rows, I think month wise partition will be OK.
    The table will have 84 partitions.

    Tamil

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Thanks for the opinion.

    Quote Originally Posted by KenEwald
    What's the best way to create partitions (cron job or pre-created for 10 years)?
    What's your take on partition maintenance?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Jun 2005
    Posts
    31
    Quote Originally Posted by KenEwald
    Thanks for the opinion.
    What's your take on partition maintenance?
    A few ideas about partition maintenance:

    1) Reduced Daily backup volume (and time to restore) if only current partition is in “read/write” tablespace, all other in “read-only” tablespaces (but think about "delayed inserts/updates...")

    2) Partition Maintenance – Statistics

    It makes definitely no sense to analyze the empty partition.
    Analyzing after 1 hour and 1 day of usage might be a simple idea.
    The most effective approach requires some little scripting:
    • Analyze one representative parttion and their indexes.
    • Export those statistics once.
    • Import those statistics into each new created partition.

    3) Sorting after partition is full ?
    ==> Depends how often which type of SELECT hits your table.

    4) Partition Maintenance – Index-Rebuild
    The main question is: Is index-rebuild necessary at all ?

    More detailed evaluation of those topics about Partition Maintenance you find on pages 11 - 13 in http://www.mercury-consulting-ltd.co...artitions.html

  7. #7
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Thanks for the good advice. The read-only idea is especially nice.
    .
    Is there a (dis)advantage to pre-creating 24 partitions even though they'll be empty for a while?
    .
    Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  8. #8
    Join Date
    Jun 2005
    Posts
    31
    Quote Originally Posted by KenEwald
    Is there a (dis)advantage to pre-creating 24 partitions even though they'll be empty for a while?
    Ken
    As long as you don't make your initial extent of each partition such big that the backup volume is an issue I don't remember any disadvantage.

    When using locally managed tablespaces the number of extents isn't that big topic any more - that means you can keep your initial extent small.

    A full-table-scan without date-based where-clause would read in this partitions only up to the segment-high-water-mark, and that shouldn't be more than 5 empty blocks, in each partition, therefore this impact shouldn't be measurable.

    (But as the full initial extent of each partition is allocated when creating the partition, the tablespace-high-water mark will be at the end of the last partition's extent.)

    And don't forget to immediately set up a daily check + email (warning) - or integration into your monitoring-system how many partitions are left ... (Takes less time then filling out an incident-report in 24 months and 1 day )

    Put it at least on your monthly checklist.

  9. #9
    Join Date
    Jun 2006
    Posts
    259
    Ken,

    Also instead of using CRON for this, I'd suggest building a PL/SQL package and then enabling this as a daily/weekly (your pick) job scheduled by the dbms_jobs ...

    I've found this is much better than cron since I've had issues with CRON and Sys admins / security dept etc revoking cron jobs. especially when your doing a daily partitioning.

    regarding IOT, what is the size of the Varchar field and size of your clob field on average? Clobs are a little different. So you might just want to utilze a regular table. and an index that is locally partitioned.



    Regards,
    ken

  10. #10
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Thanks for your suggestions. I'll pursue pl/sql running under a dbms_job. Sounds like a perfect place for something like this.

    The varchar2 is 4k and the clob is for anything over 4k.

    I've seen that you can compress the PK of an IOT, but can I compress the whole IOT?

    I'm really curious if pre-creating 100 or so partitions is a problem.

    Thank you,

    Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

Posting Permissions

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


Click Here to Expand Forum to Full Width