Re: performance question

I don't know how your 7k data structure looks like and the cardinality of their data but one suggestion could be to compute a hash value, either from the entire datablock or some portion of it, and store the hash-value as a function-based index.

SELECT * FROM MyHugeTable WHERE your_hash_function(ColumnName) = your_hash_function('MyBinaryColumn')

Oracle would only need to compute the searched hash_value and do an indexscan on a numeric index (the precomputed hashvalues). But depending on how your hashvalue-algorithm is written the values could be non-unique and therefore the above query could return >1 row. But it's probably faster to search for your binary column in the returned resultset than doing a full table-scan on 150GB... (not TB as stated above) :§

/Kristian

"Douglas Hawthorne" <douglashawthorne_at_yahoo.com.au> wrote in message news:<6Cg8c.122868$Wa.96529_at_news-server.bigpond.net.au>...
> "Alex" <alexyang_at_eudoramail.com> wrote in message
> news:1061q2como12p22_at_corp.supernews.com...
> > Hi,
> >
> > I have a situation where any design or other suggestions are appreciated.
> >
> > I have more than half a million data files with 300K each. Each file is
> one
> > record with a binary data section of around 7K that I am interested in.
> I'll
> > need to do query on that particular binary data section in that huge table
> > of data record, such as 'SELECT * FROM MyHugeTable WHERE
> > ColumnName='MyBinaryColumn'. Are there suggestions (table design, query
> and
> > others) that may help to improve the performance?
> >
> > Thanks a lot,
> >
> > Alex
> >
> >
>
> Alex,
>
> My gut reaction is that you will need to do some more data analysis because,
> at this stage, the logical table consists of two (2) columns: search key and
> the rest of the data. The main problem is that you will not be able to
> index COLUMNNAME because it has to a BLOB data type (or LONG) and these data
> types cannot be indexed. As the design stands now, you are left with full
> table scans. My suggestion is to dig deeper to see if you can break that
> column up into smaller chunks that extract more information, if that is
> possible.
>
> What is in these binary files: audio, video? Can the 9i InterMedia
> Annotator help in classifying the data for you?
>
> Douglas Hawthorne
Received on Thu Mar 25 2004 - 03:19:27 CST