create table

[Edgar_8]

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!

[Graham Cameron_1]

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

[Edgar_8]

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]

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

[Mobeen_1]

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]

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