Home » SQL & PL/SQL » SQL & PL/SQL » partitions (merged)
partitions (merged) [message #385148] Fri, 06 February 2009 11:30 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
I have 2 questions.

1. is it possible to create a list sub parititon to a table that is already LIST PARITIONED. And to create this for only one parititons.

2. the other is can i create a list subpartition to a list partition.


1. My existing table has a list partiton.I want to add a list sub-partiton to only one of the list partitions. Is that possible?

My existing table is

CREATE TABLE sales1
( DIVISION_ID NUMBER,
asof_date DATE )
PARTITION BY LIST (DIVISION_ID)
--SUBPARTITION BY list (asof_date)
(
PARTITION P1 VALUES (1)
LOGGING
NOCOMPRESS,
PARTITION P2 VALUES (7)
LOGGING
NOCOMPRESS,
PARTITION P3 VALUES (120)
LOGGING
NOCOMPRESS,
PARTITION P5 VALUES (71)
LOGGING
NOCOMPRESS
)

Now I want to add multiple sub-partition to p5 ONLY


ALTER TABLE sales1
MODIFY PARTITION p5
ADD SUBPARTITION JAN
VALUES (TO_DATE('2006-02-01', 'YYYY-MM-DD'));

It give me

ORA-14253: table is not partitioned by Composite Range method

SO is it possible to create a list sub parititon to a table that is already LIST PARITIONED.

2. Also, i created a table from scratch.

CREATE TABLE sales1
( DIVISION_ID NUMBER,
asof_date DATE )
PARTITION BY LIST (DIVISION_ID)
SUBPARTITION BY list (asof_date)
(
PARTITION P1 VALUES (1)
LOGGING
NOCOMPRESS,
PARTITION P2 VALUES (7)
LOGGING
NOCOMPRESS,
PARTITION P3 VALUES (120)
LOGGING
NOCOMPRESS,
PARTITION P5 VALUES (71)
LOGGING
NOCOMPRESS
)

It gives me ORA-00922: missing or invalid option

Hope you can help.thanks.
Re: Partitions [message #385150 is a reply to message #385148] Fri, 06 February 2009 11:44 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
As far as I know upto oracle 10g, composite partition only in this form is supported which are range-hash and range-list.

However I will tempted to ask you if you are going for a list partition atleast to me it doesn't make any sense to have a list subpartition inside a list partition. Instead you could create another list partition.

Check this link for more information.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#i460895

Regards

Raj
Re: partitions (merged) [message #385152 is a reply to message #385148] Fri, 06 February 2009 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:
Michel Cadot wrote on Thu, 04 September 2008 20:40
As usual you are not following OraFAQ Forum Guide.
Read it.

Regards
Michel

Re: Partitions [message #385155 is a reply to message #385150] Fri, 06 February 2009 11:58 Go to previous messageGo to next message
bella13
Messages: 90
Registered: July 2005
Member
Thanks.

The thing is list partition is a client specific partition.

And within the client i have dates - like month end dates stored for a client.

Even a list and range works.

I am not sure how i can pull off range-list...
Re: Partitions [message #385171 is a reply to message #385155] Fri, 06 February 2009 17:06 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
A better question is WHAT are you trying to achieve by sub-partitioning this particular partition.

If there is a specific problem you are trying to solve, tell us about that and we may be able to help.

Ross Leishman
Previous Topic: overlap interval
Next Topic: How to compare two tables?
Goto Forum:
  


Current Time: Tue Dec 06 04:40:04 CST 2016

Total time taken to generate the page: 0.11907 seconds