large multi-byte char column strategies

   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) Received on Tue May 29 2001 - 19:24:18 CDT