Solved

Creating a partion table from a non-partitioned table

Posted on 2008-10-16
8
577 Views
Last Modified: 2013-12-19
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
Comment
Question by:HRMorton
  • 4
  • 2
  • 2
8 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility

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
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
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
 

Author Comment

by:HRMorton
Comment Utility
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
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:HRMorton
Comment Utility
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
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility

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
 
LVL 27

Accepted Solution

by:
sujith80 earned 250 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now