virtual column partion on date
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: virtual column partion on date

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    virtual column partion on date

    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

  2. #2
    Join Date
    Dec 2010
    Posts
    12
    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).

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by BeefStu View Post
    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"?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Jul 2006
    Posts
    195
    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.

  5. #5
    Join Date
    Dec 2010
    Posts
    12
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width