OraFAQ Forum: Server Administration » Subpartition on Date column

Home » RDBMS Server » Server Administration » Subpartition on Date column

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

Subpartition on Date column [message #133619]

Sat, 20 August 2005 03:34

shashidharrm
Messages: 3
Registered: August 2005
Location: Bangalore, India

Junior Member

Hi All,

I have a requirement..table to be created and partition to be made on Date field, main Partition is on YEAR and subpartion to be on same date field MONTH wise.

How populating data in to these partitions. Here is the syntax, Can anybody correct the syntax for me.

Attachment: subpartition.sql
(Size: 1.77KB, Downloaded 1045 times)

Report message to a moderator

Re: Subpartition on Date column [message #133636 is a reply to message #133619]

Sat, 20 August 2005 08:43

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Is there a reason you are making this more complicated than it has to be? Do you have terrabytes and terrabytes of data? Does your date column span 1000 years?

Why not just use regular partitions, with no subpartitions, on the combination of year and month?

Report message to a moderator

Re: Subpartition on Date column [message #133649 is a reply to message #133619]

Sat, 20 August 2005 13:28

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

You had a bunch of extra right parentheses that did not belong there. In the following demonstration, I changed the tablespace name, but notice that there are no right parentheses after each inividual SUBPARTITION ... TABLESPACE USERS. The number of left and right parentheses in any statement must always match.

scott@ORA92> CREATE TABLE table_test
  2  (
  3    PROD_CODE	    VARCHAR2(5),
  4    PRODUCT_DESCRIPTION  VARCHAR2(60),
  5    PROD_1		    VARCHAR2(5),
  6    PROD_2		    VARCHAR2(5),
  7    PROD_3		    VARCHAR2(5),
  8    PROD_4		    VARCHAR2(5),
  9    PROD_5		    VARCHAR2(5),
 10    PROD_6		    VARCHAR2(5),
 11    PROD_1_DESC	    VARCHAR2(60),
 12    PROD_2_DESC	    VARCHAR2(60),
 13    PROD_3_DESC	    VARCHAR2(60),
 14    PROD_4_DESC	    VARCHAR2(60),
 15    PROD_5_DESC	    VARCHAR2(60),
 16    PROD_6_DESC	    VARCHAR2(60),
 17    CREATED_BY	    VARCHAR2(30) NOT NULL,
 18    CREATION_DATE	    DATE NOT NULL,
 19    LAST_UPDATED_BY	    VARCHAR2(30) NOT NULL,
 20    LAST_UPDATED_DATE    DATE NOT NULL)
 21    PARTITION BY RANGE (CREATION_DATE)
 22    SUBPARTITION BY HASH (CREATION_DATE)
 23    SUBPARTITION TEMPLATE(
 24    SUBPARTITION JAN tablespace USERS,
 25    SUBPARTITION FEB tablespace USERS,
 26    SUBPARTITION MAR tablespace USERS,
 27    SUBPARTITION APR tablespace USERS,
 28    SUBPARTITION MAY tablespace USERS,
 29    SUBPARTITION JUN tablespace USERS,
 30    SUBPARTITION JUL tablespace USERS,
 31    SUBPARTITION AUG tablespace USERS,
 32    SUBPARTITION SEP tablespace USERS,
 33    SUBPARTITION OCT tablespace USERS,
 34    SUBPARTITION NOV tablespace USERS,
 35    SUBPARTITION DEC tablespace USERS)
 36    (PARTITION year2000
 37    VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
 38    PARTITION year2001
 39    VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
 40    PARTITION year2002
 41    VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY')),
 42    PARTITION year2003
 43    VALUES LESS THAN (TO_DATE('01-JAN-2003','DD-MON-YYYY')),
 44    PARTITION year2004
 45    VALUES LESS THAN (TO_DATE('01-JAN-2004','DD-MON-YYYY')),
 46    PARTITION year2005
 47    VALUES LESS THAN (TO_DATE('01-JAN-2005','DD-MON-YYYY')),
 48    PARTITION others
 49    VALUES LESS THAN (MAXVALUE))
 50  /

Table created.

Report message to a moderator

Re: Subpartition on Date column [message #133685 is a reply to message #133649]

Sun, 21 August 2005 12:15

shashidharrm
Messages: 3
Registered: August 2005
Location: Bangalore, India

Junior Member

Thanks Martin,Barbara for the prompt reply.

am able to create the table with required partitions. But when i pull the data into table, not getting inserted into the subpartions, e.g., when data for Aug,2005 inserted into the table it shuld be in Aug_year2005 partition. It is not happening so.

Can you please advise me, what changes i need to take care.

TIA
Shashi

Report message to a moderator

Re: Subpartition on Date column [message #133690 is a reply to message #133685]

Sun, 21 August 2005 15:55

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

It looks like your partition years are all off by one year. For example, year2000 should be values less than 01-JAN-2001 and year2005 should be values less than 01-JAN-2006.

Report message to a moderator

Re: Subpartition on Date column [message #133692 is a reply to message #133685]

Sun, 21 August 2005 16:18

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

Also, if you want to put certain values in certain subpartitions, then I think you need to partition by range or list, not hash. I think you may need a separate column for the month, in order to do this, as demonstrated below. Alternatively, you could just use partitions, one for each month, without subpartitions, as previously suggested by smartin.

scott@ORA92> CREATE TABLE table_test
  2  (
  3    PROD_CODE	    VARCHAR2(5),
  4    PRODUCT_DESCRIPTION  VARCHAR2(60),
  5    PROD_1		    VARCHAR2(5),
  6    PROD_2		    VARCHAR2(5),
  7    PROD_3		    VARCHAR2(5),
  8    PROD_4		    VARCHAR2(5),
  9    PROD_5		    VARCHAR2(5),
 10    PROD_6		    VARCHAR2(5),
 11    PROD_1_DESC	    VARCHAR2(60),
 12    PROD_2_DESC	    VARCHAR2(60),
 13    PROD_3_DESC	    VARCHAR2(60),
 14    PROD_4_DESC	    VARCHAR2(60),
 15    PROD_5_DESC	    VARCHAR2(60),
 16    PROD_6_DESC	    VARCHAR2(60),
 17    CREATED_BY	    VARCHAR2(30) NOT NULL,
 18    CREATION_DATE	    DATE	 NOT NULL,
 19    CREATION_MONTH	    VARCHAR2(3),
 20    LAST_UPDATED_BY	    VARCHAR2(30) NOT NULL,
 21    LAST_UPDATED_DATE    DATE	 NOT NULL)
 22    PARTITION BY RANGE (CREATION_DATE)
 23    SUBPARTITION BY LIST (CREATION_MONTH)
 24    SUBPARTITION TEMPLATE(
 25    SUBPARTITION JAN VALUES ('JAN') tablespace USERS,
 26    SUBPARTITION FEB VALUES ('FEB') tablespace USERS,
 27    SUBPARTITION MAR VALUES ('MAR') tablespace USERS,
 28    SUBPARTITION APR VALUES ('APR') tablespace USERS,
 29    SUBPARTITION MAY VALUES ('MAY') tablespace USERS,
 30    SUBPARTITION JUN VALUES ('JUN') tablespace USERS,
 31    SUBPARTITION JUL VALUES ('JUL') tablespace USERS,
 32    SUBPARTITION AUG VALUES ('AUG') tablespace USERS,
 33    SUBPARTITION SEP VALUES ('SEP') tablespace USERS,
 34    SUBPARTITION OCT VALUES ('OCT') tablespace USERS,
 35    SUBPARTITION NOV VALUES ('NOV') tablespace USERS,
 36    SUBPARTITION DEC VALUES ('DEC') tablespace USERS)
 37    (PARTITION year2000
 38    VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
 39    PARTITION year2001
 40    VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY')),
 41    PARTITION year2002
 42    VALUES LESS THAN (TO_DATE('01-JAN-2003','DD-MON-YYYY')),
 43    PARTITION year2003
 44    VALUES LESS THAN (TO_DATE('01-JAN-2004','DD-MON-YYYY')),
 45    PARTITION year2004
 46    VALUES LESS THAN (TO_DATE('01-JAN-2005','DD-MON-YYYY')),
 47    PARTITION year2005
 48    VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY')),
 49    PARTITION others
 50    VALUES LESS THAN (MAXVALUE))
 51  /

Table created.

scott@ORA92> INSERT INTO table_test
  2    (created_by, creation_date, last_updated_by, last_updated_date, creation_month)
  3  SELECT USER, created, created, created, TO_CHAR (created, 'MON')
  4  FROM   user_objects
  5  /

442 rows created.

scott@ORA92> ANALYZE TABLE table_test COMPUTE STATISTICS
  2  /

Table analyzed.

scott@ORA92> SELECT partition_name,
  2  	    subpartitIon_name,
  3  	    num_rows
  4  FROM   user_tab_subpartitions
  5  WHERE  table_name = 'TABLE_TEST'
  6  AND    num_rows > 0
  7  /

PARTITION_NAME                 SUBPARTITION_NAME                NUM_ROWS
------------------------------ ------------------------------ ----------
YEAR2004                       YEAR2004_SEP                           17
YEAR2004                       YEAR2004_OCT                           20
YEAR2004                       YEAR2004_NOV                            4
YEAR2004                       YEAR2004_DEC                           23
YEAR2005                       YEAR2005_JAN                           47
YEAR2005                       YEAR2005_FEB                           21
YEAR2005                       YEAR2005_MAR                           11
YEAR2005                       YEAR2005_APR                           29
YEAR2005                       YEAR2005_MAY                           43
YEAR2005                       YEAR2005_JUN                           72
YEAR2005                       YEAR2005_JUL                           41
YEAR2005                       YEAR2005_AUG                          114

12 rows selected.

scott@ORA92> SELECT creation_date
  2  FROM   table_test SUBPARTITION (YEAR2004_NOV)
  3  /

CREATION_
---------
26-NOV-04
22-NOV-04
09-NOV-04
17-NOV-04

scott@ORA92> drop table table_test
  2  /

Table dropped.

Report message to a moderator

Re: Subpartition on Date column [message #134517 is a reply to message #133692]

Thu, 25 August 2005 06:46

shashidharrm
Messages: 3
Registered: August 2005
Location: Bangalore, India

Junior Member

Thank u very much Barbara.

While creating table I have added another column which stores only month of the date for the purpose of Subpartition.

Shashidhar

Report message to a moderator

Previous Topic:	Export to create a test schema
Next Topic:	create materialized view

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 11:37:42 CST 2025