OraFAQ Forum: SQL & PL/SQL » Dynamic Table Partitioning

Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Table Partitioning (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Dynamic Table Partitioning [message #598037]

Thu, 10 October 2013 01:33

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi All ,

As we know that Interval partitioning is new feature which is available from 11g on words.
Then how we need to handle that situation in previous versions ?

Example scenario : My table gets the transactions on day basis.Each&every days it will contains lacs of records.
I just want to partition table per each day .

When ever a record was inserted with new date then I need to create the partition for that date (just like Interval partition in 11G) .

How to achieve this partitioning dynamically in 10g
or
is there any other efficient solution for this scenario ?

Thanks
Sai Pradyumn

[Updated on: Thu, 10 October 2013 01:51] by Moderator

Report message to a moderator

Re: Dynamic Table Partitioning [message #598039 is a reply to message #598037]

Thu, 10 October 2013 01:40

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

saipradyumn wrote on Thu, 10 October 2013 12:03

My table gets the transactions on day basis.Each&every days it will contains lacs of records.
I just want to partition table per each day .

lacs of records? That's just 100,000 records. Tiny in fact. Why do you want to keep just 100,000 records in each partition?

Report message to a moderator

Re: Dynamic Table Partitioning [message #598040 is a reply to message #598037]

Thu, 10 October 2013 01:51

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Use a job to do it, look up the Scheduler in the Administrator's Guide.

Report message to a moderator

Re: Dynamic Table Partitioning [message #598041 is a reply to message #598037]

Thu, 10 October 2013 01:53

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can precreate all the needed partitions.
You can, depending on how you load the new records, trap the error when inserting, create the partition and reexecute the insert.

Report message to a moderator

Re: Dynamic Table Partitioning [message #598055 is a reply to message #598041]

Thu, 10 October 2013 03:10

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Lalit

Here my problem is not with count (100000).
For example if we are getting 10 lacs records for each day into table through the migration (Ex CSV Files)

When we need add the partition
How to add the partition to existing table

I just tried to add new partition to the existing partitioned table.

 

SQL>  CREATE TABLE t1 (
  2      id INT,
  3      dt  date)
  4  partition by range (dt)
  5     (
  6        partition SYS_10 values less than (to_date('2013-10-10','yyyy-mm-dd')),
  7        partition SYS_11 values less than (to_date('2013-10-11','yyyy-mm-dd')),
  8        partition SYS_12 values less than (to_date('2013-10-12','yyyy-mm-dd') )
  9        );
 
Table created
 
SQL> ALTER TABLE t1 ADD PARTITION (PARTITION SYS_13   VALUES LESS THAN  (to_date('2013-10-13','yyyy-mm-dd') ) );
 
ALTER TABLE t1 ADD PARTITION (PARTITION SYS_13   VALUES LESS THAN  (to_date('2013-10-13','yyyy-mm-dd') ) )
 
ORA-00902: invalid datatype
 
SQL>

Please help me

Thanks
SaiPradyumn

Report message to a moderator

Re: Dynamic Table Partitioning [message #598057 is a reply to message #598055]

Thu, 10 October 2013 03:23

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

You need to look up the syntax for ALTER TABLE ... ADD PARTITION ...

http://docs.oracle.com/cd/E16655_01/server.121/e17613/index.htm
--
update: added URL (sorry about that, finger problem)

[Updated on: Thu, 10 October 2013 03:25]

Report message to a moderator

Re: Dynamic Table Partitioning [message #598058 is a reply to message #598055]

Thu, 10 October 2013 03:30

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Your syntax is incorrect.

SQL> CREATE TABLE t1 (
  2          id INT,
  3          dt  date)
  4      partition by range (dt)
  5         (
  6            partition SYS_10 values less than (to_date('2013-10-10','yyyy-mm-dd')),
  7            partition SYS_11 values less than (to_date('2013-10-11','yyyy-mm-dd')),
  8            partition SYS_12 values less than (to_date('2013-10-12','yyyy-mm-dd') )
  9            );
 
Table created
SQL> ALTER TABLE t1 ADD PARTITION VALUES LESS THAN  (to_date('2013-10-13','yyyy-mm-dd'));
 
Table altered

[EDIT : Realized after submitting that John already pointed to a link to docs by the time I was composing]

[Updated on: Thu, 10 October 2013 03:31]

Report message to a moderator

Re: Dynamic Table Partitioning [message #598060 is a reply to message #598058]

Thu, 10 October 2013 03:53

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Thanks Lalit

but When this has to be happened ?

I can't go for the Comparison with new date value with existing dates values as it contains very very huge data

Thanks
Sai Pradyumn

Report message to a moderator

Re: Dynamic Table Partitioning [message #598061 is a reply to message #598060]

Thu, 10 October 2013 04:00

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

saipradyumn wrote on Thu, 10 October 2013 14:23

but When this has to be happened ?

Not sure what you are asking. Do you mean when to create new partitions? You can create them anytime, well before you insert the data.

Quote:

I can't go for the Comparison with new date value with existing dates values as it contains very very huge data

Sorry, I did not understand your question. Why cannot you compare? Are you talking about performance issue or something else. Please elaborate.

Regards,
Lalit

Report message to a moderator

Re: Dynamic Table Partitioning [message #598070 is a reply to message #598061]

Thu, 10 October 2013 05:13

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Lalit,

Initially I had created the table with some partitions. When ever a records was inserted with new date (for which partition was not available ), then dynamically I need to add the partition for that date range.

For this I thought of writing a trigger to make sure that :new_date_value is not there in existing partitions range.
If it is not there add the one more partition with that :new_date_value range .
If it already there automatically it will goes to that partition.

But as that table contains 500GB data obviously there was performance problem .

So Please let me efficient solution to add the new partitions

Thanks
SaiPRadyumn

Report message to a moderator

Re: Dynamic Table Partitioning [message #598075 is a reply to message #598070]

Thu, 10 October 2013 05:42

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 10 October 2013 08:53

You can precreate all the needed partitions.
You can, depending on how you load the new records, trap the error when inserting, create the partition and reexecute the insert.

Report message to a moderator

Re: Dynamic Table Partitioning [message #598082 is a reply to message #598070]

Thu, 10 October 2013 06:47

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Michel ,

Each year contains 365 days . My tables contains 20 years.Each day contains lacs of records .
How can I go for pre creation all the needed partitions for each day ?
I think it should be Dynamic

Thanks
SaiPRadyumn

Report message to a moderator

Re: Dynamic Table Partitioning [message #598083 is a reply to message #598082]

Thu, 10 October 2013 07:04

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

And that is what Michel has suggested you. Trap the error, create the required partition and rerun the create staements for those records.

Report message to a moderator

Re: Dynamic Table Partitioning [message #598084 is a reply to message #598082]

Thu, 10 October 2013 07:04

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I told you what to do five hours ago: use the Scheduler.
Your problem is nothing unusual, you know. I did this a while back for a Cell phone company: automated creating a range partition per hour with 16 hash subpartitions.
--
update: and, by the way, "lac" is not an SI unit.

[Updated on: Thu, 10 October 2013 07:05]

Report message to a moderator

Re: Dynamic Table Partitioning [message #598087 is a reply to message #598082]

Thu, 10 October 2013 07:16

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Each year contains 365 days . My tables contains 20 years.Each day contains lacs of records .
How can I go for pre creation all the needed partitions for each day ?

What is the problem?
If you know how to add a partition you know how to add 365 or more.

Report message to a moderator

Previous Topic:	Use ANSI standard JOIN syntax
Next Topic:	How to get this SQL output (2):-

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 05:21:15 CDT 2024