OraFAQ Forum: SQL & PL/SQL » Table patition and aautomate sub partition.

Home » SQL & PL/SQL » SQL & PL/SQL » Table patition and aautomate sub partition.

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

Table patition and aautomate sub partition. [message #642634]

Tue, 15 September 2015 18:34

raopatwariyahoocom
Messages: 39
Registered: October 2011
Location: GA

Member

I need to create a table with partitionon and subpartition s

I have tow columns in the table on which I should do partitionon
cycle_code number,
cycle_month number( has both year and month 'YYYYMM')

cycle_code is day of the month which is supposed to be (1 thru 31) can be list or range partition,
under that partition I want cycle_month as sub partition which should be automated.

I am adding the script of what I have done so far , I could not get this working. Appreciate your help in advance.
I am adding as a sample two partitions p_cyc_1 and p_cyc_2 as samples under each partiton I should have 12 months automated subpartitions. and I will have p_cyc_1 until p_cyc_31

CREATE TABLE abcd
( fileid NUMBER CONSTRAINT mrc_credits_ad_id_nn NOT NULL ENABLE,
cycle_date DATE CONSTRAINT mrc_credits_cycle_date_nn NOT NULL ENABLE,
time_key NUMBER CONSTRAINT mrc_credits_time_key_nn NOT NULL ENABLE,
rslr_partner_id NUMBER CONSTRAINT mrc_credits_rslr_nn NOT NULL ENABLE,
reseller_id VARCHAR2(2 BYTE) CONSTRAINT mrc_credits_rslr_id_nn NOT NULL ENABLE,
soc VARCHAR2(25 BYTE) CONSTRAINT mrc_credits_soc_nn NOT NULL ENABLE,
amount NUMBER,
company VARCHAR2(3 BYTE),
status_ind VARCHAR2(25 BYTE),
cycle_code number,
cycle_month number)
PARTITION BY LIST (cycle_code)
SUBPARTITION BY RANGE (cycle_month) to_number(to_char(INTERVAL(NUMTOYMINTERVAL(1,'YEAR*MONTH')),'YYYYMM'))
( PARTITION p_cyc_1 VALUES (1)
( SUBPARTITION before_01 VALUES LESS THAN to_number(to_char((TO_DATE('01-JAN-2000','dd-MON-yyyy')),'YYYYMM'))
)
,PARTITION p_cyc_2 VALUES (2)
( SUBPARTITION before_02 VALUES LESS THAN to_number(to_char((TO_DATE('01-JAN-2000','dd-MON-yyyy')),'YYYYMM'))
)
) ;

Report message to a moderator

Re: Table patition and aautomate sub partition. [message #642635 is a reply to message #642634]

Tue, 15 September 2015 18:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I need to create a table with partitionon and subpartition s
>I have tow columns in the table on which I should do partitionon
>cycle_code number,
>cycle_month number( has both year and month 'YYYYMM')

what problem will this "solution" actually solve?
How was it decided that above is optimal solution?

Report message to a moderator

Re: Table patition and aautomate sub partition. [message #642636 is a reply to message #642635]

Tue, 15 September 2015 21:04

raopatwariyahoocom
Messages: 39
Registered: October 2011
Location: GA

Member

This could have gone into cycle_date and got that partitioned however that has changed now there could be a cycle_code of 31 that can come in the month of 2016feb or 31 cycle_code can come in the month of SEP-2015 , if we make that as 31-SEP-2015, which date data type column will not accept. That is the reason why we had split into cycle_code and monthyear. There is even a chance that cycle_code can go up to 40. SO now each cycle_code partition should have 12 month sub partitions that should be automated

Report message to a moderator

Re: Table patition and aautomate sub partition. [message #642641 is a reply to message #642636]

Wed, 16 September 2015 02:05

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

Your code doesn't work:

orclz>
orclz> CREATE TABLE abcd
  2  ( fileid NUMBER CONSTRAINT mrc_credits_ad_id_nn NOT NULL ENABLE,
  3  cycle_date DATE CONSTRAINT mrc_credits_cycle_date_nn NOT NULL ENABLE,
  4  time_key NUMBER CONSTRAINT mrc_credits_time_key_nn NOT NULL ENABLE,
  5  rslr_partner_id NUMBER CONSTRAINT mrc_credits_rslr_nn NOT NULL ENABLE,
  6  reseller_id VARCHAR2(2 BYTE) CONSTRAINT mrc_credits_rslr_id_nn NOT NULL ENABLE,
  7  soc VARCHAR2(25 BYTE) CONSTRAINT mrc_credits_soc_nn NOT NULL ENABLE,
  8  amount NUMBER,
  9  company VARCHAR2(3 BYTE),
 10  status_ind VARCHAR2(25 BYTE),
 11  cycle_code number,
 12  cycle_month number)
 13  PARTITION BY LIST (cycle_code)
 14  SUBPARTITION BY RANGE (cycle_month) to_number(to_char(INTERVAL(NUMTOYMINTERVAL(1,'YEAR*MONTH')),'YYYYMM'))
 15  ( PARTITION p_cyc_1 VALUES (1)
 16  ( SUBPARTITION before_01 VALUES LESS THAN to_number(to_char((TO_DATE('01-JAN-2000','dd-MON-yyyy')),'YYYYMM'))
 17  )
 18  ,PARTITION p_cyc_2 VALUES (2)
 19  ( SUBPARTITION before_02 VALUES LESS THAN to_number(to_char((TO_DATE('01-JAN-2000','dd-MON-yyyy')),'YYYYMM'))
 20  )
 21  ) ;
SUBPARTITION BY RANGE (cycle_month) to_number(to_char(INTERVAL(NUMTOYMINTERVAL(1,'YEAR*MONTH')),'YYYYMM'))
                                    *
ERROR at line 14:
ORA-00922: missing or invalid option


orclz>

What are you trying to achieve? What is the the nature of the queries and DDLs that you expect to run against this table? Prtitioning is often a disaster if you have not designed the strategy to fit the problem.

And of course you need to correct your syntax.

Report message to a moderator

Re: Table patition and aautomate sub partition. [message #642643 is a reply to message #642641]

Wed, 16 September 2015 02:31

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Your code doesn't work

Starting from the most internal expression:

SQL> select NUMTOYMINTERVAL(1,'YEAR*MONTH') from dual;
select NUMTOYMINTERVAL(1,'YEAR*MONTH') from dual
                         *
ERROR at line 1:
ORA-01760: illegal argument for function

Report message to a moderator

Re: Table patition and aautomate sub partition. [message #642656 is a reply to message #642641]

Wed, 16 September 2015 07:20

raopatwariyahoocom
Messages: 39
Registered: October 2011
Location: GA

Member

when I am selecting the data I use in the where clause
where cycle_month = 'YYYYMM' -- this has both year and month
and cycle_code = 31;

if I am trying to retreive the data where cycle_code is 31(numberof cycles)
and cycle_month = 'YYYYMM'('201602')

the whole value cannot be kept as date because there is no data value like '31-FEB-2016'. Thats why I had to go this route.
else I could have automated ad date making that as cycle_date date datatype

Report message to a moderator

Re: Table patition and aautomate sub partition. [message #642657 is a reply to message #642643]

Wed, 16 September 2015 07:22

raopatwariyahoocom
Messages: 39
Registered: October 2011
Location: GA

Member

Yes sir. Thats why I am asking help from Gurus

Report message to a moderator

Re: Table patition and aautomate sub partition. [message #642658 is a reply to message #642656]

Wed, 16 September 2015 07:37

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

raopatwariyahoocom wrote on Wed, 16 September 2015 13:20

when I am selecting the data I use in the where clause
where cycle_month = 'YYYYMM' -- this has both year and month
and cycle_code = 31;

if I am trying to retreive the data where cycle_code is 31(numberof cycles)
and cycle_month = 'YYYYMM'('201602')

the whole value cannot be kept as date because there is no data value like '31-FEB-2016'. Thats why I had to go this route.
else I could have automated ad date making that as cycle_date date datatype

As you are using equality predicates, hash partitioning would probably be better than range. However, are you sure that you should partition at all? Unless each query is retrieving a significant proportion of the rows in the table, index access will be better than table scans with partition pruning. How many rows in the table? How many rows have cycle_code=31 and cycle_month = 'YYYYMM'('201602') ?

Report message to a moderator

Re: Table patition and aautomate sub partition. Req changed [message #642663 is a reply to message #642634]

Wed, 16 September 2015 11:07

raopatwariyahoocom
Messages: 39
Registered: October 2011
Location: GA

Member

Requirements are changed bit
I need to create a table with partition and subpartition s

I have two columns in the table on which I should do partitionon
cycle_code number,
month_year DATE

cycle_code is day of the month which is supposed to be (1 thru 31) can be list or range partition,
under that partition I want month_year as sub partition which should be automated.

I am adding the script of what I have done so far , I could not get this working. Appreciate your help in advance.
I am adding as a sample two partitions p_cyc_1 and p_cyc_2 as samples under each partiton I should have 12 months automated subpartitions.
and I will have p_cyc_1 until p_cyc_31

p_cyc_1 list partition should have 12 months sub partitions

CREATE TABLE ds_mrc_credits_cyc
( fileid NUMBER CONSTRAINT mrc_credits_ad_id_nn1 NOT NULL ENABLE,
time_key NUMBER CONSTRAINT mrc_credits_time_key_nn1 NOT NULL ENABLE,
rslr_partner_id NUMBER CONSTRAINT mrc_credits_rslr_nn1 NOT NULL ENABLE,
reseller_id VARCHAR2(2 BYTE) CONSTRAINT mrc_credits_rslr_id_nn1 NOT NULL ENABLE,
soc VARCHAR2(25 BYTE) CONSTRAINT mrc_credits_soc_nn1 NOT NULL ENABLE,
amount NUMBER,
company VARCHAR2(3 BYTE),
status_ind VARCHAR2(25 BYTE),
cycle_code number,
month_year DATE)
PARTITION BY LIST ( cycle_code )
SUBPARTITION BY RANGE( month_year )
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
( PARTITION p_cyc_1 VALUES (1)
(SUBPARTITION before_01 VALUES LESS THAN (TO_DATE('20140201', 'YYYYMMDD'))),
PARTITION p_cyc_2 VALUES (2)
(SUBPARTITION before_01 VALUES LESS THAN (TO_DATE('20140201', 'YYYYMMDD')))
);

Report message to a moderator

Re: Table patition and aautomate sub partition. Req changed [message #642664 is a reply to message #642663]

Wed, 16 September 2015 11:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

http://stackoverflow.com/questions/26808000/using-interval-numtoyminterval-1-month-in-subpartition

[Updated on: Wed, 16 September 2015 12:25]

Report message to a moderator

Previous Topic:	Options for definitions of INDEXES used by CONSTRAINTS
Next Topic:	Query running fine in TOAD and SQL developer but very slow in Server ( oracle DB) [merged 2]

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 11:40:47 CDT 2024