• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

Creating a partion table from a non-partitioned table

I have a very large tables that I need to delete rows from.  Doing it via code takes 20-30 hours so I've been looking at alternate ways of doing this.  I considered creating a partitioned table and truncating the partition I don't want, it's this I'm having problems with.  Code I'm using is :-

CREATE TABLE ci_nt_up_part
(
  NT_UP_ID         NUMBER(12)                   NOT NULL,
  NT_XID_CD        CHAR(30 BYTE)                DEFAULT ' '                   NOT NULL,
  NT_UP_XTYPE_CD   CHAR(30 BYTE)                DEFAULT ' '                   NOT NULL,
  NT_UP_STAT_FLG   CHAR(2 BYTE)                 DEFAULT ' '                   NOT NULL,
  NT_UP_EXTSN_FLG  CHAR(4 BYTE)                 DEFAULT ' '                   NOT NULL,
  CRE_DTTM         DATE,
  VERSION          NUMBER(5)                    DEFAULT 1                     NOT NULL
)
PARTITION BY RANGE (cre_dttm)
(
  PARTITION remove_p1 VALUES LESS THAN (TO_DATE('16/10/2007:12:00:00','DD/MM/YYYY:HH24:MI:SS')),
  PARTITION keep_p2 VALUES LESS THAN (TO_DATE('16/10/2009:12:00:00','DD/MM/YYYY:HH24:MI:SS'))
)

CREATE TABLE dummy_y as SELECT * FROM ci_nt_up WHERE cre_dttm < ADD_MONTHS(sysdate, - 12);

CREATE TABLE dummy_z as SELECT * FROM ci_nt_up WHERE cre_dttm >= ADD_MONTHS(sysdate, -12);

ALTER TABLE ci_nt_up_part EXCHANGE PARTITION remove_p1 WITH TABLE dummy_y;

ALTER TABLE ci_nt_up_part EXCHANGE PARTITION keep_p2 WITH TABLE dummy_z;

ALTER TABLE ci_nt_up_part
TRUNCATE PARTITION remove_p1;

When I try to run the 1st ALTER TABLE statement I get the error
"ORA-14099: all rows in table do not qualify for specified partition".

The cre_dttm field is date and time. I've tried to add the time into the partition, truncate the dates but nothing seems to work.
Any suggestions as to where I'm going wrong ??

Thanks
p.s. the 2nd ALTER TABLE statement works fine.
0
HRMorton
Asked:
HRMorton
  • 4
  • 2
  • 2
1 Solution
 
MikeOM_DBACommented:

Create dummy table with SAME conditions as partition:


CREATE TABLE dummy_y as 
   SELECT * FROM ci_nt_up
    WHERE cre_dttm < TO_DATE('16/10/2007:12:00:00','DD/MM/YYYY:HH24:MI:SS'); 
 
CREATE TABLE dummy_z as 
   SELECT * FROM ci_nt_up
    WHERE cre_dttm >= TO_DATE('16/10/2007:12:00:00','DD/MM/YYYY:HH24:MI:SS');

Open in new window

0
 
sujith80Commented:
As Mike indicated, the problem is with the usage of sysdate, you could hard code the dates(safer) or use trunc(sysdate)

But,
I didnt understand why you need dummy_y at all?
You first exchange it with partition remove_p1 and then truncate the partition remove_p1.
Isn't it the same as doing nothing with remove_p1?


I would say that the whole thing can be done with this single statement.

insert /*+ APPEND */ into ci_nt_up_part SELECT * FROM ci_nt_up WHERE cre_dttm >= (TO_DATE('16/10/2009:12:00:00','DD/MM/YYYY:HH24:MI:SS'));
0
 
HRMortonAuthor Commented:
Hi,

I've been experimenting with speed, I have a table that has 92 million records, spread over a period of 2.5 years and I have to remove those records that are more than 1 year old.
I have tried the method of creating a temporary table with only the records I want, dropping the original and then re-naming the temporary to the original.  

I was trying to see if using partitions so I could do a TRUNCATE would be quicker.

Thanks for the input
Heather
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sujith80Commented:
Yes, for maintaining historical data, partitioning is the best method. Archiving, truncating all are DDL commands which complete very fast compared to manipulating non-partitioned tables using DML commands.
0
 
HRMortonAuthor Commented:
Hi,
So based on the idea that what I was trying to do is keep data less than one year old and remove date older than a year, to do this I have to move the data from a non-partitioned table to a partioned table with 2 partions, allowing me to drop one partition.

What would you suggest is the best way of achieving this, creating the partitioned table and then replacing the partitions with the dummy tables is how I was trying to do this.

Thanks
Heather

0
 
sujith80Commented:
From what I understand, you want to get rid of this non-partitioned table and use partitioning going forward.

why dont you try this:
1. create partitioned table structure with necessary patitions.
2.
insert /*+ APPEND */ into <partitioned table>
select only required data
from your non-partitioned table.
3. rename old table.
4. rename the partitioned table to the required name.

The point here is:
You dont have to copy the unwanted data and then drop the partition.
0
 
MikeOM_DBACommented:

A CTAS is faster than an INSERT.  Follow sujith's suggestion as to just creating with the data you need:

CREATE TABLE dummy_z
       NOLOGGING PARALLEL 4
AS  
   SELECT * FROM ci_nt_up
    WHERE cre_dttm >= TO_DATE('16/10/2007:12:00:00','DD/MM/YYYY:HH24:MI:SS');
 
ALTER TABLE ci_nt_up_part 
   EXCHANGE PARTITION keep_p2 WITH TABLE dummy_z; 

Open in new window

0
 
sujith80Commented:
Mike,
I have to disagree there. CTAS does the same work as INSERT /*+ APPEND */ does. So the timings are more or less the same.
See the following test case on a 3M rows table.
SQL> select count(*) from tbl1;
 
  COUNT(*)
----------
   3397280
 
Elapsed: 00:00:05.42
SQL> drop table tbl2;
 
Table dropped.
 
Elapsed: 00:00:01.12
SQL> create table tbl2 as select * from tbl1;
 
Table created.
 
Elapsed: 00:00:15.68
SQL> drop table tbl2;
 
Table dropped.
 
Elapsed: 00:00:01.18
SQL> create table tbl2 as select * from tbl1 where 1 = 2;
 
Table created.
 
Elapsed: 00:00:00.96
SQL> insert /*+ APPEND */ into tbl2 select * from tbl1;
 
3397280 rows created.
 
Elapsed: 00:00:14.98
SQL> commit;
 
Commit complete.

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now