partitioning oracle table

Hi, I need to create a table in oracle with a dayly partition, I mean that table partition should be dynamic.
My scenario requires to have last 7 days data on line; considering that one day process produces around 15 million of records, I would like to implement a mechanism that allows a fast and efficent access to current day data.
I have few questions about it:
1) is it possible to partition table data on day rolling?
2) is there something I should be aware of with this approch?
3) is there any kind of issue I have to expect while deleting "one day" data (I mean deleting data of one day - 7 (+ 1)?
Thanks in advance
hanoirulesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
In 11g there is a new feature called Interval Partitioning that will automatically create new partitions on the fly.

http://www.dba-oracle.com/t_interval_partitioning.htm

I can't think of any issues doing this as long as the system/data commands it.  What else are you thinking about that would make you question this approach?

A delete is a delete.  Nothing new about deleting form partitions.  I will point out that if you are going to delete ALL the data in a single partition, just drop the partition.  It will be a lot quicker.  The thing to remember here is: DDL cannot be rolled back.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Walter RitzelSenior Software EngineerCommented:
Please check this script:
CREATE TABLE TEST
(TIME_ID NUMBER,
REGION_ID NUMBER,
ORDER_ID NUMBER,
ORDER_DATE DATE
)
PARTITION BY RANGE (ORDER_DATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(PARTITION p_first VALUES LESS THAN ('8-APR-2011'));

Then try to insert on different dates... new partitions will be created.
If you change this instruction (NUMTODSINTERVAL(1,'day')) you can change the granularity of the partition.

To delete data, drop the partition, as advised by slightwv.
0
sventhanCommented:
1) is it possible to partition table data on day rolling?
Yes it is.
2) is there something I should be aware of with this approch?
Do you like to truncate the partitions or archive(swap) the partitions?
3) is there any kind of issue I have to expect while deleting "one day" data (I mean deleting data of one day - 7 (+ 1)?
when you truncate the partitions the (local)index become invalid and you've to rebuild them.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

hanoirulesAuthor Commented:
Wow! What a team!
slightwv: do you know if this feature is available in oracle 10 as well?

wpcortes: when you say:  drop the partition, do you mean to perform a delete against a certain date dataset, or there is a special way to drop a partition?

sventhan: "when you truncate the partitions the (local)index become invalid and you've to rebuild them"; considering that all these operations should be done on the fly, which is the best way to perform:
save (d1) / delete (d7+1) with minimum effort in term of database maintenance?
Thanks in advance
0
hanoirulesAuthor Commented:
Thanks a lot sventhan. As far as I understand, (I am not a DBA - unfortunately I would say), those are the operations required in order to maintain/use partitions.
In my scenario – all data required to be on line – should be accessible on request (I mean WEB request) - with a time filter -  and I might have concurrent request on same dataset or dataset intersection.
Do you think that is it reasonable to have a job scheduled once per day that takes care of  data truncation, without stopping the WEB requests flow?
And what about accessing data cross partitions? I mean dataset(d0) – dataset(d1)-etc..
What about if I perfom a delete instead?
Thanks a lot
0
slightwv (䄆 Netminder) Commented:
I don't believe interval partitioning is available in 10g.

>>when you say:  drop the partition

Actually, I mentioned it as well in my first post.  No, drop partition is the same as drop table.  Delete from partition/table is different.  One is considered DML, the other DDL.  The TRUNCATE command mentioned above is similar to dropping the partition except the partition remains part of the table.

>>What about if I perfom a delete instead?

deletes create redo and takes more time.  drop/truncate is almost immediate and generates no redo.  Therefore, like I mentioned before: is pretty much permanent (you might be able to flash back the table or preform database recovery but that is a different question).


Deleting 15 million records will take a while.  If you know you want it all gone:  drop/truncate.
0
hanoirulesAuthor Commented:
Hi, thanks for the answers and the great help, I've enough to think about :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.