cancel
Showing results for 
Search instead for 
Did you mean: 

create table

Edgar_8
Regular Advisor

create table

We have a daily partitioned fact temp_table(no indexes or primary keys) that is sql loaded via
the direct method. What we want to do is create a clean copy of the temp_table which does not have
duplicate records. Does anyone know if its possible to create a copy of the temp_table with a primary key
column thereby removing duplicate records and also including indexes? If so, what would be the syntax
of the sql statement?(ie. create copy_of_temp_table ..... as select * from temp_table; )

Thanks in advance!
5 REPLIES
Graham Cameron_1
Honored Contributor

Re: create table

Create table copy_of_temp_table
as select disctinct * from temp table
/

You may want to add a storage clause, and to add a Primary Key after creation, but this will remove duplicates.

-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Edgar_8
Regular Advisor

Re: create table

Hi Graham,

The fact temp_table has approx 2.8 billion records, would the select not take days to get a result?

Thanks in advance!
Graham Cameron_1
Honored Contributor

Re: create table

Edgar

Well, there is no such thing as a free lunch, but given your data size, a better option may to create an empty copy table, add the PK, then populate it using an EXCEPTIONS clause.
More here ...

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/clauses3a.htm#998196

-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Mobeen_1
Esteemed Contributor

Re: create table

Edgar,
I am not sure what RDBMS you are using. But if you are using RDBMS like Sybase, then you have an option to use BCP (bulk copy) instead of the conventional method of copy as.

If you are using Oracle, i bet they would have some tool like the Sybase BCP for carrying this out instead of depending on COPY AS

regards
Mobeen
Yogeeraj_1
Honored Contributor

Re: create table

hi,

how about adding a unique constraint on the columns in question and using "exceptions into".

For example:
==========================================
create table t as select * from temp_table;

create table exceptions(row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));

alter table t add constraint t_unique unique(a,b,c) exceptions into exceptions;

create table dups
as
select *
from t
where rowid in ( select row_id from exceptions )
/

delete from t where rowid in ( select row_id from exceptions );

insert into t select distinct * from dups;
...

hope this helps too!
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)