Solved

Creating a partion table from a non-partitioned table

Posted on 2008-10-16
8
579 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
ID: 22737392

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
ID: 22738097
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
ID: 22738729
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
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.

 
LVL 27

Expert Comment

by:sujith80
ID: 22738813
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
 

Author Comment

by:HRMorton
ID: 22739042
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
ID: 22739123
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
ID: 22740139

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
ID: 22775387
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

776 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