virtual column partion on date

[BeefStu]

I have a table with approx 60 millions rows and 900 distinct dates. I want
to convert this table to a interval partition by day but some people are
balking because this will use a system generated partition name.

Is there a way I can use a virtual column on a date columne (say create_date) and have that be the partition key. This way I will have no
empty partition names and I can make the name of the partition the same
as the date value, which will work with my other scripts.

I am looking to do something like this:

create table t ( create_date date, data varchar2(30) )
ALTER TABLE T ADD (the_date AS to_char(created_date,'yyymmdd'));
partition on the_date

I have two questions:

1) Will this work?

2) If so can somebody show me the syntax

[onedbguru]

No, that will not work. Interval partitioning is based on a NUMBER column or a DATE column only - your example uses a VARCHAR data type.

http://lmgtfy.com/?q=oracle+interval...ning+data+type

In your case, you really do not need a virtual column to interval by day, just a date column you may already be using. But you could create one based on sysdate if necessary.

create_date DATE GENERATED ALWAYS AS (trunc(sysdate)) VIRTUAL VISIBLE ,
OR just
create_date DATE GENERATED ALWAYS AS (sysdate) VIRTUAL VISIBLE.

just in case you may want to create the interval as something less than a day (like interval'1'hour)


The database is smart enough to do it for you. And when querying the table, as long as this date column (even insert_ts=trunc(sysdate-?)), the database will only access the partition where that date is contained. Using this will test your skills as a dba to be able to parallelize your queries and general knowledge of many different parts of the database structures - like how do you get rid of "old" partitions. Just remember - TEST TEST TEST TEST and TEST some more.

CREATE TABLE MySCHEMA.MyTable
(
ID NUMBER,
F1 NUMBER,
F2 NUMBER,
INSERT_TIMESTAMP date,
create_date DATE GENERATED ALWAYS AS (sysdate()) VIRTUAL VISIBLE.
--...
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
TABLESPACE MyTablespace PARTITION BY RANGE
(
INSERT_TIMESTAMP -- or create_date from above your choice.
)
INTERVAL
(
INTERVAL'1'DAY
)
(
PARTITION P1 VALUES LESS THAN
(TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 )
TABLESPACE Mytablespace NOCOMPRESS
) ;

/*** NOTE: The P1 partition should never contain data AND should never be deleted. **********/

I also created a function to determine the partition name containing a particular date based on information found in {user|dba}_tab_partitions and will leave that as an exercise for the poster.

I will also leave it to the OP to determine how they are going to move the data to the new table (hint: search for table redefinition - or there is always data pump).

Just an fyi, you can get very granular using this approach based on the date column and it will only create partitions for the interval - no wasted partitions. I have used a virtual date column generated from a unix_timestamp column (converts unix_seconds to a date - NOT a varchar).

[PAVB]

I have a table with approx 60 millions rows and 900 distinct dates. I want to convert this table to a interval partition by day...

That would create partitions with an average of 67K rows, isn't it? 900 of them.

May I ask how this table is archived/purged? is it done by "day" as in single days are archived/purged?

May I ask what's the most common predicate in queries hitting this table? are most common queries asking for a single "day"?

[BeefStu]

Right now there is no purge/archive process in place.

Therefore, I was thinking about partitioning this table by day and than compressing older paritions through a cron script, which will be run daily.

Prior to interval partitioning, scripts were created to add/drop paritions on
certain tables. The caveat here is that the partition name must be named
in a format that the script can understand. Since these scripts appear to
be working without any issues, management does not want them changed

Therefore, I was looking for a simple way to create all these partitions. I
can certainly create a start partition less than 01-01-2010, which will
take care of a great chunk of my data, but that leaves me with an issue
of creating a partition name for each day after...

As mentioned, interval partitioning would be great for me but this will not
work with my maint scripts since they were developed for range partition
only.

Yes, most of the queries against this table are done on a daily basis thru the
app.

[onedbguru]

BeefStu,

your script can be easily adapted to use a procedure that will give you the name of the partition to be purged based on the high_value column in the {dba|user}_tab_partitions view. I will leave it to you to come up with that procedure - it is a trivial task that will save you a lot of headaches down the road.

I have the procedure, however, I think it will be more beneficial for you if I just give you the hint and let you learn trial by fire.

Also, I have typically created the first range partition based on a date that will never contain data such as 01-01-1900.