RE: sql question
'ministry of education' wrote:
> I have a table with multiple exact duplicate rows.No indexes. How do I
> delete all duplicate rows to get a single occurrence for each row?
>
> Thanx.
The answer depends upon the version of Informix you have, to some degree. =
It also depends upon how big the table is, and how much clear dbspace and =
file system space you have.
The first question is: why does it matter if you don't have an index? Do =
applications fail? Surely if there is a requirement for uniqueness, then =
an index is called for.
Given that there isn't one, however, you can achieve this a number of =
ways.
One simple answer is to create a duplicate index on the 'real' key, if =
there is one. Then the optimiser will use this index to isolate the rows =
which are duplicate when you select them (very quickly). Similarly the =
index will make the DELETEs very quick as well. Then you can drop the =
index if you must.
You can do this more elegantly with version 7.1+. There is an option to =
create an index in FILTERING mode. eg:
CREATE UNIQUE INDEX ON tabname (col1, col2 ...) DISABLED;START VIOLATIONS TABLE FOR tabname;
SET INDEXES FOR tabname FILTERING;
The index creation will fail, but along the way it will create two tables =
'tabname_vio', and 'tabname_dia'. These can be used to identify the rows =
which cause the index creation to fail. See TFM for more info on this.
Alternatively, if you have both the temporary and permanent space, you can =
create a new table, and insert into it the unique rows from your original. =
This has the added bonus of allowing you to get rid of interleaved =
extents (if they have occurred).
Lastly, if you have file system space but not dbspace, you can unload the =
data, run it through 'sort -u' and load it back up again. You can also =
perform the sort within Informix if you only have the temporary space.
That's probably enough alternatives for now! The right answer depends on =
how much time and space you have to fix the problem. Of course, Einstein =
might suggest those resources are interchangeable. But if you're in a =
vehicle travelling at the speed of light, what happens when you turn the =
headlights on?!
hth,
RET
+------------------------------------------+
| Richard Thomas |
| DBA - Marketing Information Systems |
| Optus Communications |
| email: richard_thomas@yes.optus.com.au |
| Ph: +61 2 9342 7188 |
| "My opinions are my opinions" |
+------------------------------------------+
___________________________________________________________________________=
____
From: ministry of education on Mon, 8 Dec 1997 12:53 PM
Subject: sql question
To: informix-list@rmy.emory.edu
------------------ RFC822 Header Follows ------------------
>Received: by yes.optus.com.au with SMTP;8 Dec 1997 11:47:38 +1000
>Received: from news.optus.com.au ([203.13.126.11])
> by prairiedog.optus.com.au (Netscape Messaging Server 3.1b)
> with ESMTP id AAA1A19; Mon, 8 Dec 1997 09:58:16 +1100
>Received: from happy.optus.com.au (firewall-user@happy.optus.com.au =
>[203.13.126.9]) by news.optus.com.au (8.8.3/8.8.3) with ESMTP id JAA10234; =
>Mon, 8 Dec 1997 09:58:15 +1100
>Received: by happy.optus.com.au; id GAA16845; Mon, 8 Dec 1997 06:07:19 =
>+1100 (EST)
>Received: from rmy.rmy.emory.edu(170.140.97.4) by happy.optus.com.au via =
>smap (3.2)
> id xma016842; Mon, 8 Dec 97 06:07:06 +1100
>Received: (from ilist@localhost) by rmy.rmy.emory.edu (8.7.1/8.7.1) id =
>MAA24216 for informix-list-out; Sun, 7 Dec 1997 12:00:02 -0500 (EST)
>From: ministry of education <moe003@netvision.net.il>
>Message-Id: <348B572B.1D63@netvision.net.il>
>Subject: sql question
>Date: Sun, 07 Dec 1997 18:10:51 -0800
>Reply-To: ministry of education <moe003@netvision.net.il>
>Organization: -
>Sender: informix-list-owner@rmy.emory.edu
>To: informix-list@rmy.emory.edu
>X-Informix-List-Id: <news.46345>