Results 1 to 4 of 4

Thread: Table partition

  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: Table partition

    I have a existing table CSX_UPDATE_LIST. It has been decided that we need to partition this table on a date field PROC_RQST_DT.
    PHP Code:
    SQLdesc csx_update_list
     Name                                                              Null
    ?    Type
     
    ----------------------------------------------------------------- -------- 
     
    CID                                                               NOT NULL NUMBER(12)
     
    SOURCE_CD                                                         NOT NULL VARCHAR2(10)
     
    PROC_STS_CD                                                       NOT NULL VARCHAR2(10)
     
    BUILD_DT                                                          NOT NULL DATE
     QUEUE_DT                                                                   DATE
     PROC_PRIORITY_CD                                                  NOT NULL NUMBER
    (1)
     
    PROC_RQST_DT                                                      NOT NULL DATE
     CURR_CARD_NO                                                               VARCHAR2
    (20)
     
    MEMB_STAT                                                                  VARCHAR2(1)
     
    UPDATE_MSG                                                                 VARCHAR2(3000
    We can partition on the month basis.The actual requirement is to housekeep this table and I m thinking of partitioning this job monthwise and dropping the last partition at the start of each month.

    The data in this table dates back to Jul 2003. Each month has almost same amount of data.

    How should I go about partitionng this table keeping in mind I have to keep the last 6 months data and drop all the previous data.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Instead of creating and dropping partitions (which would, at least I think so, require your effort every month), how about creating a job (using DBMS_SCHEDULER or DBMS_JOB) and let Oracle delete records older than 6 months from the original table? This would override your decision about partitions, but ... well, might work.

    How many records do you create on monthly basis? Why do you think partitioning would help in your case? Just being curious ...

  3. #3
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    reuse of freed space

    Hi

    The concern I have is about the re-use of the space freed up as Mass deleting like this isn't ideal.

    As of now the number of records which meet the deletion criteria (proc_rqst_dt more than 6 months old) are 26359435.

    On a monthly basis approx 1000000 records are created.

    The data in this table dates back to Jul 2003. Each month has almost same amount of data. I want to create partitions on existing table.

    How should I go about partitionng this table keeping in mind I have to keep the last 6 months data and drop all the previous data.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Partitioning would seem sensible as you can truncate those partitions you no longer need, which is much quicker than delete.

    To actually partition your table you could use 'create table .... as select * from old_table where ...' and then rename the table (use nologging aswell and backup subsequently). This assumes your application has been stopped.
    Alternatively if you cant stop your app lookup dbms_redefinition in google.

    Alan

Posting Permissions

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