Home » SQL & PL/SQL » SQL & PL/SQL » Add Range Subpartition to a hash Parition table (11g)
Add Range Subpartition to a hash Parition table [message #573737] Wed, 02 January 2013 20:03 Go to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
Can I add range subpartition to a hash partition table.

Example like this.


CREATE TABLE test
(
test_id VARCHAR2(10 ) ,
test_TYPE VARCHAR2(5) ,
CREATE_DATE date
)
partition by hash (test_id, test_type)
Partitions 3
SUBPARTITION BY RANGE (CREATE_DATE);


When Tried, I am getting syntax error as invalid option.
Can someone please advise.
Thx
Re: Add Range Subpartition to a hash Parition table [message #573745 is a reply to message #573737] Wed, 02 January 2013 23:20 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
1/ dont you think so that first Range and then sub partition should be done by Hash ?
2/ Composit partition
3/ This will help you to manage you data in effective way.
Re: Add Range Subpartition to a hash Parition table [message #573853 is a reply to message #573745] Thu, 03 January 2013 11:06 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
yes, I agree. in general and most preferable way it should be how you have described. However, our business requirement and dba maintenance forces me to think differently. Hence was looking alternate option. Does Oracle allow hash partition and subpartiiton on date range?

Thanks
Re: Add Range Subpartition to a hash Parition table [message #573855 is a reply to message #573853] Thu, 03 January 2013 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you can see in the documentation, yes it allows it but you have to FIRST create the table with subpartition.
You cannot subpartition only one partition.

Regards
Michel

[Updated on: Thu, 03 January 2013 11:16]

Report message to a moderator

Re: Add Range Subpartition to a hash Parition table [message #573969 is a reply to message #573855] Fri, 04 January 2013 11:58 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
Thank you Michel.

Am I doing anything wrong here..


CREATE TABLE test
(
slno VARCHAR2(10),
CREATE_DATE TIMESTAMP (6)
)
partition by hash (slno)
Partitions 1
SUBPARTITION BY RANGE
(
CREATEDATE
)
INTERVAL (NUMTOYMINTERVAL(1,'month'))
(
SUBPARTITION s1 VALUES LESS THAN (TO_DATE('20100801', 'YYYYMMDD'))
) ;

It gives me error as
SQL Error: ORA-00922: missing or invalid option
00922. 00000 - "missing or invalid option"
Re: Add Range Subpartition to a hash Parition table [message #573972 is a reply to message #573969] Fri, 04 January 2013 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session, the WHOLE session.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Add Range Subpartition to a hash Parition table [message #573973 is a reply to message #573972] Fri, 04 January 2013 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Like that;
SQL> CREATE TABLE test
  2    (
  3      slno VARCHAR2(10),
  4      CREATE_DATE TIMESTAMP (6)
  5    )
  6    partition by hash (slno)
  7    Partitions 1 
  8    SUBPARTITION BY RANGE
  9    (
 10      CREATEDATE
 11    )
 12    INTERVAL (NUMTOYMINTERVAL(1,'month'))
 13   ( 
 14   SUBPARTITION s1 VALUES LESS THAN (TO_DATE('20100801', 'YYYYMMDD'))
 15   ) ;
  SUBPARTITION BY RANGE
  *
ERROR at line 8:
ORA-00922: missing or invalid option

Regards
Michel
Re: Add Range Subpartition to a hash Parition table [message #573977 is a reply to message #573973] Fri, 04 January 2013 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that in 11g you can subpartition by range but not by interval.

Regards
Michel
Re: Add Range Subpartition to a hash Parition table [message #573985 is a reply to message #573977] Fri, 04 January 2013 13:08 Go to previous message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
Thanks Michel. I was able to do subpartition by range but was getting error when added interval option. Appreciate your help and will follow the guidelines Smile
Previous Topic: Number conversion
Next Topic: Insert into target table and delete from source
Goto Forum:
  


Current Time: Fri Aug 22 12:55:12 CDT 2014

Total time taken to generate the page: 0.11634 seconds