We help IT Professionals succeed at work.

How can I create range sub partition?

srikumar_p
srikumar_p asked
on
Hi,

I am using Oracle 8.1.7. Is it possible to use range partitions inside another range partition. In other words, can I create range sub partitions inside another range partition? If yes, what is the syntax?

Thanks
Srikumar
Comment
Watch Question

Commented:
No, based on the CREATE TABLE syntax flow chart, but you could subpartition with the "BY HASH" option only.

A good example is

CREATE TABLE MyOrders(
     ordid NUMBER,
     orderdate DATE,
     productid NUMBER,
     quantity NUMBER)
  PARTITION BY RANGE(orderdate)
  SUBPARTITION BY HASH(productid) SUBPARTITIONS 4
  STORE IN(ts1,ts2,ts3,ts4 )
   ( PARTITION q1 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
     PARTITION q2 VALUES LESS THAN(TO_DATE('01-JUL-1998','DD-MON-YYYY')),
     PARTITION q3 VALUES LESS THAN(TO_DATE('01-OCT-1998','DD-MON-YYYY')),
     PARTITION q4 VALUES LESS THAN(MAXVALUE)
   );

KongAWS Certified Solutions Architect - Professional
BRONZE EXPERT

Commented:
No you cannot have range-range partitioned tables.
Only partitions available are range, hash, and composite (range-hash).

It seems pretty pointless having range-range partitions since you can partition by range on more than one field:

CREATE TABLE emp
(empno NUMBER(9)
,ename VARCHAR2(20)
,sal   NUMBER(9)
,deptno NUMBER(9))
PARTITION BY RANGE (empno, deptno)
(PARTITION p1 VALUES LESS THAN (2000, 5000)
 TABLESPACE ts1
,PARTITION p2 VALUES LESS THAN (3000, 10000)
 TABLESPACE ts2
,PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
 TABLESPACE ts3
);

Explore More ContentExplore courses, solutions, and other research materials related to this topic.