Home » SQL & PL/SQL » SQL & PL/SQL » Create Year and week partition automatically (11.2.0.4)
Create Year and week partition automatically [message #663090] Mon, 22 May 2017 06:09 Go to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Hi,

I have written one procedure to create a partition with the combination of year and week (for example (2017,1)...(2017,52)). This needs to be run manually every time. Please let me know is there any way to create the partition with the above combination automatically (without manual intervention). Please suggest.


create or replace PROCEDURE PRC_PARTITIONWEEK(p_tablename varchar2,start_date date,end_date date , p_partitionname varchar2) AS 
v_sql varchar2(1000);
v_partname varchar2(100);
v_months number;
v_date date;
wknum number;
vyear number;
v_sdate date:=TO_CHAR(TO_DATE(start_date,'DD-MON-YY'),'DD-MON-YYYY');

BEGIN
	select MONTHS_BETWEEN 
   ((TO_CHAR(TO_DATE(end_date,'DD-MON-YY'),'DD-MON-YYYY')),
    TO_CHAR(TO_DATE(start_date,'DD-MON-YY'),'DD-MON-YYYY') ) into v_months from dual;
	
	for i in 1..v_months LOOP
	
	SELECT to_char(LAST_DAY(v_sdate), 'iw' )+1 ,
         (CASE to_number(to_char(LAST_DAY(v_sdate),'iw'))
         WHEN 52 THEN to_char( LAST_DAY(v_sdate), 'yyyy' ) 
         WHEN 01 THEN to_char( LAST_DAY(v_sdate)+6, 'yyyy' )
         ELSE to_char( v_sdate, 'yyyy' )
         END) vyear
  INTO wknum,vyear from dual;
 
		select TO_CHAR(TO_DATE(v_sdate,'DD-MON-YY'),'DD-MON-YYYY') into v_date from dual;
    
		select  p_partitionname||'_'||to_char(trunc(trunc(TO_DATE(v_date,'DD-MON-YY'), 'MM'), 'MM'),'MMYYYY') --"Month and year of given date"
		into v_partname
		from dual;
    		  
		v_sql:='ALTER TABLE '||p_tablename ||'  ADD PARTITION '||v_partname ||' VALUES LESS THAN ('||vyear||','||wknum||') TABLESPACE PEGADATA ';
  
    dbms_output.put_line('partname '||v_partname  || 'sql  '|| V_SQL); 
  
		EXECUTE IMMEDIATE V_SQL;
	
		v_sdate:=add_months(v_sdate,1);
	
	END LOOP;
	COMMIT;
END PRC_PARTITIONWEEK;

Re: Create Year and week partition automatically [message #663092 is a reply to message #663090] Mon, 22 May 2017 06:12 Go to previous messageGo to next message
Bill B
Messages: 1700
Registered: December 2004
Senior Member
If you are on at least Oracle 11 it is easy. The following link shows you how

http://stackoverflow.com/questions/41443756/oracle-automatic-partitioning-name-pattern-for-partitions
Re: Create Year and week partition automatically [message #663093 is a reply to message #663090] Mon, 22 May 2017 06:14 Go to previous messageGo to next message
John Watson
Messages: 7181
Registered: January 2010
Location: Global Village
Senior Member
Your whole strategy is wrong. You should be interval partitioning on a DATE column.
Then the detail is wrong, too. You must use correct data types You are applying TO_DATE to DATE variables, which is a bug waiting to bite. Think this though:
to_char(trunc(trunc(TO_DATE(v_date,'DD-MON-YY'), 'MM'), 'MM'),'MMYYYY')
what are you doing there?

[Updated on: Mon, 22 May 2017 06:14]

Report message to a moderator

Re: Create Year and week partition automatically [message #663097 is a reply to message #663093] Mon, 22 May 2017 07:29 Go to previous messageGo to next message
Bill B
Messages: 1700
Registered: December 2004
Senior Member
I agree with John. Your interval should be on month. that will produce an automatic partition for each month which is the same thing you are doing with your YYYY,MM logic
Re: Create Year and week partition automatically [message #663100 is a reply to message #663097] Mon, 22 May 2017 07:36 Go to previous messageGo to next message
John Watson
Messages: 7181
Registered: January 2010
Location: Global Village
Senior Member
I think he wants weeks, all that month stuff is ludicrous. An interval of seven days should do it.
Re: Create Year and week partition automatically [message #663103 is a reply to message #663100] Mon, 22 May 2017 07:39 Go to previous messageGo to next message
Bill B
Messages: 1700
Registered: December 2004
Senior Member
sorry, I just noticed that his number after year was 1-52. Weeks it is
Re: Create Year and week partition automatically [message #663133 is a reply to message #663103] Tue, 23 May 2017 02:12 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Yes...We can create year and month partition by using interval partition but need to create year and week partition.
Re: Create Year and week partition automatically [message #663135 is a reply to message #663133] Tue, 23 May 2017 05:52 Go to previous messageGo to next message
John Watson
Messages: 7181
Registered: January 2010
Location: Global Village
Senior Member
You do know that a week is seven days?
Re: Create Year and week partition automatically [message #663137 is a reply to message #663135] Tue, 23 May 2017 06:12 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
John Watson wrote on Tue, 23 May 2017 06:52
You do know that a week is seven days?
Not in terms of WW format.

select date '2016-12-31' + level dt,
       to_char(date '2016-12-31' + level,'ww') week_number
  from dual
  connect by level <= 370
/
...
29-DEC-17 52
30-DEC-17 52
31-DEC-17 53 -- one day week
01-JAN-18 01
02-JAN-18 01
03-JAN-18 01
04-JAN-18 01
05-JAN-18 01

SY.
Re: Create Year and week partition automatically [message #663143 is a reply to message #663090] Tue, 23 May 2017 08:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
You could interval partition by year and subpartition by week (week starts January 1):

create table tbl(
                 dt date,
                 year number generated always as (extract(year from dt)),
                 week number generated always as (to_number(to_char(dt,'ww')))
                )
  partition by range(year)
    interval(1)
    subpartition by range(week)
      subpartition template(
                            subpartition w1 values less than(2),
                            ...
                            subpartition w53 values less than(54)
                           )
  (
   partition y2000_and_before values less than(2001)
  )
/

SY.
Re: Create Year and week partition automatically [message #663144 is a reply to message #663143] Tue, 23 May 2017 08:20 Go to previous messageGo to next message
Bill B
Messages: 1700
Registered: December 2004
Senior Member
To bad the user wasn't running Oracle 12c. They added automatic partition generation for partition by list. Would have worked great for the user
Re: Create Year and week partition automatically [message #663146 is a reply to message #663143] Tue, 23 May 2017 08:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
Actually, no need for subpartitioning:

create table tbl(
                 dt date,
                 week number generated always as (53 * to_number(to_char(dt,'syyyy')) + to_number(to_char(dt,'ww')) - 1)
                )
  partition by range(week)
    interval(1)
  (
   partition y2000_and_before values less than(106053)
  )
/

SY.
Re: Create Year and week partition automatically [message #663155 is a reply to message #663146] Tue, 23 May 2017 22:48 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you very much all.

I will try with below option.
Re: Create Year and week partition automatically [message #663165 is a reply to message #663155] Wed, 24 May 2017 08:25 Go to previous message
Bill B
Messages: 1700
Registered: December 2004
Senior Member
IF YOU ARE RUNNING ORACLE 12.2 you can do the following and everything is taken care of automatically

CREATE TABLE MY_PART_TABLE
(
  MY_DATE       DATE                            NOT NULL,
  MY_PART_DATA  VARCHAR2(7 BYTE) INVISIBLE GENERATED ALWAYS AS (TO_CHAR("MY_DATE",'YYYY-WW'))
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            BUFFER_POOL      DEFAULT
           )
PARTITION BY LIST (MY_PART_DATA) AUTOMATIC
(  
  PARTITION FIRST_PART VALUES ('2000-01')
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                MAXEXTENTS       UNLIMITED
                BUFFER_POOL      DEFAULT
               )
);
Previous Topic: Improve Spool Performance
Next Topic: Help in Query to achieve output?
Goto Forum:
  


Current Time: Tue Dec 12 10:52:05 CST 2017

Total time taken to generate the page: 0.06523 seconds