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

Hooty and the Blowfish <bob_at_bob.com> wrote in message news:<1ks76uc3c2e36k0jrdsvs2apl2hb7q26n6_at_4ax.com>...  

> So that I can give this group some guidance: How would you go about
> testing uniqueness on 1 Billion 11 character words using oracle?

Create a single table with no indexes. Use partitioning. Pre-allocate space so no extents need to be created. Use direct load optimized for load speed.

Make sure that you follow disk IO contention best practices: stripe your partions over as many physical devices as possible (more physical heads = faster reads and writes), isolate your redos (or better turn logging off).

Use partitions to partially sort the strings somewhat as you insert. If you can split up the data so each partition will fit in your sort_area_size (which you should make as big as possible), you'll be able to sort entirely in memory, with no temp IO. In fact, you might not even want to create an index, but simply use a count with a having clause (storing the index just creates IO and memory demands, and I suspect most of your data will actually be distinct anyway).

For example if your strings are random, then hashing on the first letter will create 26 partions each < 1G and you can bump up your sort_area_size to be safely able to swallow a partion at a time (use more partitions as needed). Monitor temp seqment IO when you search each partition for dups. You really want it to be virtually non-existent.

Check out this for sql*loader optimization: http://oracle.oreilly.com/news/sqlload_0501.html

Using all these techniques, you should absolutely blow away SQL*SERVER, to the point where your managment never speaks of it again. Received on Fri Feb 08 2002 - 21:58:46 CST