Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Skill testing SQL question

Re: Skill testing SQL question

From: Alexander I. Doroshko <aid_at_grant.kharkov.ua>
Date: 6 Nov 1999 09:32:53 GMT
Message-ID: <01bf283a$9b6eb040$190114c1@sister.grant.UUCP>


Arati Vijay <vijayj00_at_singnet.com.sg> wrote in article <7vu9v8$a3d$1_at_coco.singnet.com.sg>...
: TRY THIS
:
: CREATE TABLE NO_DUPE AS (SELECT DISTINCT * FROM DUPE_TABLE)
: DROP TABLE DUPE_TABLE
: ALTER TABLE NO_DUPE RENAME TO DUPE_TABLE
:
: Incidentally, this is a question that I also ask at interviews, and
ain't
: got an answer to date.

The solution using DDL statements is, obviously, the worst one. There is a well-known solution, which, as it appeared, comes to mind to most Oracle freshmen worth of employing, though it takes them some time. People with some experience usually know it.

DELETE DUPE_TABLE D1 WHERE EXISTS
 (SELECT 1 FROM DUPE_TABLE WHERE AA=D1.AA AND BB=D1.BB    AND ROWID>D1.ROWID); Of course, if the DUPE_TABLE is huge and not indexed properly, the above query works eternally, so it is worth to create a non-unique index on (AA, BB) in this case. --
 Alexander I.Doroshko, aid_at_grant.kharkov.ua

Received on Sat Nov 06 1999 - 03:32:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US