Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: Partition question

    Hello

    I have got a table CSX_UPDATE_LIST having date column PROC_RQST_DT.
    I want to retain only that data which is less than 6 months old based on indicator PROC_STS_CD. ( All 'P' status records older than 6 months old should be deleted).

    Can I use oracle partitioning for this purpose. I know I can partition by date range monthly and retain only last 6 partitions. But keeping in mind that only P status records older than 6 months need to be deleted.

    Can i achieve what I want via Oracle partitioning.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you just answered your own question.
    can you use partitioning to drop partitions older than 6 months? yes

    How much extra you keep is dependent upon the frequency of your partitions.
    monthly = you'll have to keep roughly a months worth older than 60 days (depending on the day of the month)
    weekly = only keeping 1-6 days older than 6 months
    daily = speaks for itself
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It sounds like a good use for a composite partition (range-list). You would partition by PROC_RQST_DT (date) and then subpartition by PROC_STS_CD (your status column).

    For example
    Code:
    SQL>
    SQL> create table CSX_UPDATE_LIST (
      2     PROC_RQST_DT    date,
      3     PROC_STS_CD     varchar2(1),
      4     check   ( PROC_STS_CD in ( 'P', 'A' ) )
      5  ) partition by range ( PROC_RQST_DT )
      6    subpartition by list ( PROC_STS_CD ) (
      7     partition m01_PROC_RQST_DT values less than ( to_date( '01-02-2006', 'dd-mm-yyyy' ) )
      8     ( subpartition m01_P_PROC_STS_CD values ( 'P' ),
      9       subpartition m01_A_PROC_STS_CD values ( 'A' ) ),
     10     partition m02_PROC_RQST_DT values less than ( to_date( '01-03-2006', 'dd-mm-yyyy' ) )
     11     ( subpartition m02_P_PROC_STS_CD values ( 'P' ),
     12       subpartition m02_A_PROC_STS_CD values ( 'A' ) )
     13  )
     14  /
    
    Table created.
    
    SQL>
    will store values for month 01 and 02 and status P and A. If you want to drop/truncate, you would just alter table CSX_UPDATE_LIST drop/truncate partition <partition name>.

Posting Permissions

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