topic Re: create table in Operating System - HP-UX

create table

Edgar_8 — Wed, 03 Mar 2004 02:09:25 GMT

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!

Re: create table

Graham Cameron_1 — Wed, 03 Mar 2004 03:23:36 GMT

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

Re: create table

Edgar_8 — Wed, 03 Mar 2004 04:28:40 GMT

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!

Re: create table

Graham Cameron_1 — Wed, 03 Mar 2004 04:41:34 GMT

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

Re: create table

Mobeen_1 — Wed, 03 Mar 2004 04:51:33 GMT

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

Re: create table

Yogeeraj_1 — Wed, 03 Mar 2004 05:42:35 GMT

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