-
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
-
Do you have a feel for what sort of date range will commonly be used when querying the table?
-
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.
-
For 127,750,000 rows, I think month wise partition will be OK.
The table will have 84 partitions.
Tamil
-
Thanks for the opinion.
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?
-
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
-
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
-
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.
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|