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?
 
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
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.