eliminate duplicate rows
A user found duplicates in a very large table (initially described as 300 billion rows, later clarified as 300 million) after a unique index went missing, and his fix - HPL unload, drop/recreate table plus unique index, then dbload - was running far too slowly. Responders suggested avoiding the reload entirely: find and delete the duplicates in place, create the unique index with a violation/diagnostic table to capture offending rows, copy just the key columns plus rowid into a smaller indexed work table to locate duplicate rowids for deletion, use OLAP window functions (12.x only), or load into a raw table via an external table in express mode with no index. The poster only replied that he was on 11.70.FC8; no resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi
i have a duplicate problem with 300 billion table, i don't know why and when
the unique index was dropped !!
Now i'm using dbload to reload the data avoiding the duplicate
- unload all the data with HPL
- Drop / create the table
- Create the unique index
- load with dbload
but ....it really takes time (only 30 billions loaded in 7 hours)
is there any other way ?
thanks in advance
Can you identify and remove the duplicates from the existing table saving
you the steps of unload/drop/create/reload?
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SMITH
JOHN
Sent: Monday, August 08, 2016 12:28 PM
To: ids@iiug.org
Subject: eliminate duplicate rows [37538]
Hi
i have a duplicate problem with 300 billion table, i don't know why and when
the unique index was dropped !!
Now i'm using dbload to reload the data avoiding the duplicate
- unload all the data with HPL
- Drop / create the table
- Create the unique index
- load with dbload
but ....it really takes time (only 30 billions loaded in 7 hours)
is there any other way ?
thanks in advance
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Which version? In 12 you could take advantage of the OLAP window functions
to get only the first of the duplicate rows.
It would be slower than a simple scan but the load could be load without
index.
Alternatively, you could use a violation table to detect the duplicated
rows.... if there aren't many you could try to delete them and then
recreate the unique index.
Regards.
On Mon, Aug 8, 2016 at 6:27 PM, SMITH JOHN <srafik0358@gmail.com> wrote:
> Hi
> i have a duplicate problem with 300 billion table, i don't know why and
> when
> the unique index was dropped !!
>
> Now i'm using dbload to reload the data avoiding the duplicate
>
> - unload all the data with HPL
> - Drop / create the table
> - Create the unique index
> - load with dbload
>
> but ....it really takes time (only 30 billions loaded in 7 hours)
>
> is there any other way ?
>
> thanks in advance
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--94eb2c0602646e75ff053992e495
I was thinking throw all the data into another table, de-dup it there (keep
track of what rows
were duped), and connive some scheme to delete the dup rows in the original
table.
My personal email has changed to
jjfahey@gmail.com
Please use this email in the future .
Thanks!
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Andrew
Ford
Sent: Monday, August 08, 2016 1:37 PM
To: ids@iiug.org
Subject: RE: eliminate duplicate rows [37539]
Can you identify and remove the duplicates from the existing table saving
you the steps of unload/drop/create/reload?
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SMITH
JOHN
Sent: Monday, August 08, 2016 12:28 PM
To: ids@iiug.org
Subject: eliminate duplicate rows [37538]
Hi
i have a duplicate problem with 300 billion table, i don't know why and when
the unique index was dropped !!
Now i'm using dbload to reload the data avoiding the duplicate
- unload all the data with HPL
- Drop / create the table
- Create the unique index
- load with dbload
but ....it really takes time (only 30 billions loaded in 7 hours)
is there any other way ?
thanks in advance
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Loading the data with the index in place is going to add a lot of time to
the load. I'd explore ways to identify and remove those records rather than
loading with the index created.
If you have already recreated the table and need to reload the data, then
consider making it a raw table and load via an external table in express
mode.
You may be able to use unix utilities to find those duplicates, but that's
going to be slow on such a large number of records, but even methods to
query that data once loaded are going to take a while and you may run into
problems with temp space. Extracting only the key fields into a file or
temp table may help in both cases.
Mike
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SMITH
JOHN
Sent: Monday, August 08, 2016 11:28 AM
To: ids@iiug.org
Subject: eliminate duplicate rows [37538]
Hi
i have a duplicate problem with 300 billion table, i don't know why and when
the unique index was dropped !!
Now i'm using dbload to reload the data avoiding the duplicate
- unload all the data with HPL
- Drop / create the table
- Create the unique index
- load with dbload
but ....it really takes time (only 30 billions loaded in 7 hours)
is there any other way ?
thanks in advance
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
You could use violation tables, and just drop the violation table after the
load
Cheers
Paul
Paul Watson
Oninit www.oninit.com
+1 913 387 7529
On unit® is a Registered Trademark of Oninit LLC
On Aug 8, 2016, at 18:55, Mike Walker <mike@advancedatatools.com> wrote:
> Loading the data with the index in place is going to add a lot of time to
> the load. I'd explore ways to identify and remove those records rather than
> loading with the index created.
>
> If you have already recreated the table and need to reload the data, then
> consider making it a raw table and load via an external table in express
> mode.
>
> You may be able to use unix utilities to find those duplicates, but that's
> going to be slow on such a large number of records, but even methods to
> query that data once loaded are going to take a while and you may run into
> problems with temp space. Extracting only the key fields into a file or
> temp table may help in both cases.
>
> Mike
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SMITH
> JOHN
> Sent: Monday, August 08, 2016 11:28 AM
> To: ids@iiug.org
> Subject: eliminate duplicate rows [37538]
>
> Hi
> i have a duplicate problem with 300 billion table, i don't know why and when
> the unique index was dropped !!
>
> Now i'm using dbload to reload the data avoiding the duplicate
>
> - unload all the data with HPL
> - Drop / create the table
> - Create the unique index
> - load with dbload
>
> but ....it really takes time (only 30 billions loaded in 7 hours)
>
> is there any other way ?
>
> thanks in advance
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
just to confirm that I got this right:
You write about billions, and this is a different number in Europe vs US.
When you using dbload utility loading 30,000,000,000 records into an indexed
table in 7 hrs (= 25,200 seconds), you did more than 2,380,000 write-IOPS
sustained over a long period of time.
Some questions:
- how big is that 300 billion records table?
- how many Bytes is the primary key size?
- how many fragments for this table?
- what is your Operating System and which version of IBM INFORMIX you use?
- what is you hardware?
- how much free RAM for sorts yo have?
- is there plenty of free disk space to do clever parallel sorts and a final
merge?
- how many IOPS is your I/O subsystem able to handle? (read/write) and are
these numbers veryfied in benchmarks?
- what is sequential read speed in GiB/sec?
- what is sequential write speed in GiB/sec?
The latter 2: When it really goes to disks/SSDs and no longer is cached in
memory (seen on systems having 8 TiB RAM or more)
- how long did it take the HPL to unload the table?
- how many records did the HPL write on the output side (as stated in the HPL
log)?
- what is the size of the output file?
- can you estimate the percentage of duplicates? Is is less than 3% (aka 'only
a few')?
tl;dr:
I have seen some big systems on many versions of INFORMIX and I use INFORMIX
since a very long time. Still I think that I did not undertand your numbers
correctly as my English is somewhat limited.
dic_k
SSE / Vienna / Europe
My idea would be to create a new table, which contains only the unique columns
and a orig_rowid column.
Then copy all the data from the original table to this table using a stored
procedure
or with unload/load, maybe split the files in smaller pieces externally,
resulting in a search table which should be significantly smaller than the
original.
On the new table, create an index over the unique columns and perform a query
which throws out min (orig_rowid), max (orig_rowid), count (*) for records
with more than
one occurance.
You may need a lot of temp space for this query.
Then, delete in the original table the duplicate rows by rowid.
There might be not only 2 occurences of the duplicate records but also more,
so you should
query multiple times until you get no duplicates any more.
But maybe it is easier to check the original table with a stored procedure,
setting up a non unique index
for the unique columns, query for duplicates (for same combination of fields
and different rowid)
and saving the rowids in a (non logging) temp table for later unload/delete
operation.
Depending on the server version, try the option to create the index with the
"online" keyword to not
block operation on the table.
Marcus Haarmann
----- Ursprüngliche Mail -----
Von: "Paul Watson" <paul@oninit.com>
An: ids@iiug.org
Gesendet: Dienstag, 9. August 2016 02:20:56
Betreff: Re: eliminate duplicate rows [37546]
You could use violation tables, and just drop the violation table after the
load
Cheers
Paul
Paul Watson
Oninit www.oninit.com
+1 913 387 7529
On unit® is a Registered Trademark of Oninit LLC
On Aug 8, 2016, at 18:55, Mike Walker <mike@advancedatatools.com> wrote:
> Loading the data with the index in place is going to add a lot of time to
> the load. I'd explore ways to identify and remove those records rather than
> loading with the index created.
>
> If you have already recreated the table and need to reload the data, then
> consider making it a raw table and load via an external table in express
> mode.
>
> You may be able to use unix utilities to find those duplicates, but that's
> going to be slow on such a large number of records, but even methods to
> query that data once loaded are going to take a while and you may run into
> problems with temp space. Extracting only the key fields into a file or
> temp table may help in both cases.
>
> Mike
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SMITH
> JOHN
> Sent: Monday, August 08, 2016 11:28 AM
> To: ids@iiug.org
> Subject: eliminate duplicate rows [37538]
>
> Hi
> i have a duplicate problem with 300 billion table, i don't know why and when
> the unique index was dropped !!
>
> Now i'm using dbload to reload the data avoiding the duplicate
>
> - unload all the data with HPL
> - Drop / create the table
> - Create the unique index
> - load with dbload
>
> but ....it really takes time (only 30 billions loaded in 7 hours)
>
> is there any other way ?
>
> thanks in advance
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
the version is 11.70 FC9
11.70 FC8, sorry.
sorry the number is 300,000,000