OraFAQ Forum: SQL & PL/SQL » Add Range Subpartition to a hash Parition table

Home » SQL & PL/SQL » SQL & PL/SQL » Add Range Subpartition to a hash Parition table (11g)

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

Add Range Subpartition to a hash Parition table [message #573737]

Wed, 02 January 2013 20:03

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

Report message to a moderator

Re: Add Range Subpartition to a hash Parition table [message #573745 is a reply to message #573737]

Wed, 02 January 2013 23:20

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.

Report message to a moderator

Re: Add Range Subpartition to a hash Parition table [message #573853 is a reply to message #573745]

Thu, 03 January 2013 11:06

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

Report message to a moderator

Re: Add Range Subpartition to a hash Parition table [message #573855 is a reply to message #573853]

Thu, 03 January 2013 11:15

Michel Cadot
Messages: 54681
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

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"

Report message to a moderator

Re: Add Range Subpartition to a hash Parition table [message #573972 is a reply to message #573969]

Fri, 04 January 2013 12:17

Michel Cadot
Messages: 54681
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

Report message to a moderator

Re: Add Range Subpartition to a hash Parition table [message #573973 is a reply to message #573972]

Fri, 04 January 2013 12:18

Michel Cadot
Messages: 54681
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

Report message to a moderator

Re: Add Range Subpartition to a hash Parition table [message #573977 is a reply to message #573973]

Fri, 04 January 2013 12:34

Michel Cadot
Messages: 54681
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

Report message to a moderator

Re: Add Range Subpartition to a hash Parition table [message #573985 is a reply to message #573977]

Fri, 04 January 2013 13:08

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

Report message to a moderator

Previous Topic:	Number conversion
Next Topic:	Insert into target table and delete from source

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 19 00:48:55 CDT 2013

Total time taken to generate the page: 0.12052 seconds