Re: 1 Billion 11 Byte Words... Need to Check Uniqueness Using Oracle

From: Dieter Buecherl <Dieter.Buecherl_at_t-online.de>
Date: Fri, 8 Feb 2002 21:27:52 +0100
Message-ID: <a41cak$hop$06$1@news.t-online.com>

or
1) create an empty table (create TableA (columnA varchar2(11)) without constraint,

2) load the data,
3) create the exeptions table (look for utlexcpt.sql in your ORACLE_HOME),
4) create a unique constraint on the column
       (e.g. alter tableA add constraint
        columnA_unique unique (columnA) EXCEPTIONS INTO EXCEPTIONS),

and check the exceptions table for duplicates.

This might be the fastest way

just my .02€

HTH Dieter

"Adrian Carlson-Hedges" <adrian.ch_at_btinternet.com> schrieb im Newsbeitrag news:a41402$89m$1_at_knossos.btinternet.com...
> Create an empty table (with a Unique key constraint) and SQL*Load them
into
> it?
>
> "Hooty and the Blowfish" <bob_at_bob.com> wrote in message
> news:1ks76uc3c2e36k0jrdsvs2apl2hb7q26n6_at_4ax.com...
> >
> > I have a group that is currently using Oracle but is considering
> > moving to SQL Server to save some money. One of the business cases
> > they're working with is the testing of 1 billion 11 character words
> > for uniqueness. Apparently they've been sold on the idea that SQL
> > Server will rock their monkey.
> >
> > I tend to disagree and believe that Oracle will handle this task much
> > more elegantly.
> >
> > So that I can give this group some guidance: How would you go about
> > testing uniqueness on 1 Billion 11 character words using oracle?
> >
> > Ideas to get you started:
> > 1) Write small clients to do database inserts and distribute them
> > across the network on small desktops. Run Oracle with one table, one
> > column and one constraint (that it be a primary and unique key).
> > Start the inserts and wait for an exception. You could play with the
> > number of inserts per commit and number of boxes submitting
> > connections to the database.
> >
> > 2) Install Oracle on 10 boxes and split the 1 Billion words into 10
> > segments. Insert the numbers into the database and check for
> > uniqueness. This won't prove uniqueness across the entire set so
> > you'd then have to bulk insert or import all data from each Oracle
> > database into one master database that checks uniqueness. Maybe this
> > would be faster than checking uniqueness on every insert.
> >
> > 3) ??? Any other ideas?
>
>
Received on Fri Feb 08 2002 - 14:27:52 CST