Re: large multi-byte char column strategies

Todd wrote:

> I've got a situation that must be common. In Oracle 8i, I have a column that
> has to support multi-byte character data over 4000 chars. I also need to search
> it occasionally. So:
>
> - Can LONG be used for multi-byte character set data? Is there any way to
> search a LONG column for a substring? Or do I need to use NCLOB?
>
> - Since most values will be under 4000 characters, should I have an
> NVARCHAR2(4000) for the data that doesn't exceed 4000 characters, or should I
> put all data in a LONG/NCLOB column regardless of its length? This table will
> get large, but most operations will be on indexed numeric fields, with searches
> on the large char field being less frequent. But those searches can't be too
> slow.
>
> - Is it correct that there is no way to do a search on this column without
> writing a procedure that uses the dbms_lob functions? If it turns out more
> efficient overall to store the data in two columns, what's the best approach to
> a search procedure?
>
> Generally, I'm looking for the best strategy for supporting a >4000
> multi-byte char column while optimizing for the majority of values being
> smaller. Advice or pointers to literature would be appreciated.
>
> Thanks! - Todd (gdulli_at_zdnetmail.edu - change edu to com)

Forget LONGs. They are going away in the next version of Oracle. Use a CLOB and search with InterMedia.

Daniel A. Morgan Received on Wed May 30 2001 - 00:55:44 CDT