Create Year and week partition automatically [message #663090] |
Mon, 22 May 2017 06:09 |
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 #663093 is a reply to message #663090] |
Mon, 22 May 2017 06:14 |
John Watson
Messages: 8951 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 #663137 is a reply to message #663135] |
Tue, 23 May 2017 06:12 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
John Watson wrote on Tue, 23 May 2017 06:52You 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 |
Solomon Yakobson
Messages: 3285 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 #663146 is a reply to message #663143] |
Tue, 23 May 2017 08:24 |
Solomon Yakobson
Messages: 3285 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 #663165 is a reply to message #663155] |
Wed, 24 May 2017 08:25 |
Bill B
Messages: 1971 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
)
);
|
|
|