Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Like Operator with CLOB datatype

Re: Like Operator with CLOB datatype

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: 21 Nov 2006 05:11:52 -0800
Message-ID: <1164114712.004743.210930@b28g2000cwb.googlegroups.com>

Mladen Gogala wrote:
> On Mon, 20 Nov 2006 17:26:41 -0800, bala wrote:
>
> > The best thing I thought would be to replace the Datatype from CLOB to
> > Varchar2(4000) since I checked the existing Data of that particular
> > column and the maximum length was only 500 characters.
>
> The best thing would be to use OracleIntermedia and build text indexes. If
> my memory serves me well, in Oracle8i, it used to be called Oracle ConText.
> Of course, the best thing would be to update to 10g. Oracle 8i is an
> unsupported dinosaur, unknown to practically everybody except Jonathan
> Lewis.

When you attempt to move an existing application from a Microsoft backend such as access to oracle it's a good idea to take the time to look in a systematic approach at the whole application.

That includes the database design and implementation details ( including column type choices), the application design, relevant SQL etc. Tom Kyte in his most recent book notes many of the problems that can be encountered if the approach that you are following is just to port an application from one RDBMS to another without being aware of many things that these RDBMS systems do differently.

In general when implementing on oracle you want to take advantages of the capabilities of the system not try to emulate things in a generic fashion.

Going to specific points that are contained in this thread now, if the SQL that is being generated is doing LIKE searches ( hope you are using bind variables ) if you are always searching from the beginning of a string LIKE 'WASHINGTON%' then changing the CLOB to a varchar2 and indexing it might be ok.

If it includes searches such as LIKE '%WASHINGTON%' that is a search within a string and having a regular index on a varchar2 won't be very effective. ( You will tend to end up with index scans of one sort or another, or perhaps full table scans ). In that type of case, exploring text indexes may be where you want to spend some research, however the querying syntax against text indexes is quite different ( and oracle specific since text indexes are an oracle extension ). Received on Tue Nov 21 2006 - 07:11:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US