Home » SQL & PL/SQL » SQL & PL/SQL » CONTAINS purpose
CONTAINS purpose [message #445334] Sun, 28 February 2010 22:44 Go to next message
Messages: 4
Registered: February 2010
Location: Chennai
Junior Member
Can anyone explain about CONTAINS briefly and how its differ from LIKE operator? Need to create any index for it ?
Re: CONTAINS purpose [message #445335 is a reply to message #445334] Sun, 28 February 2010 22:47 Go to previous messageGo to next message
Messages: 25524
Registered: January 2009
Location: SoCal
Senior Member
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/



Use the CONTAINS operator in the WHERE clause of a SELECT statement to specify the query expression for a Text query.

CONTAINS returns a relevance score for every row selected. You obtain this score with the SCORE operator.

LIKE used for normal VARCHAR2 fields

[Updated on: Sun, 28 February 2010 22:49]

Report message to a moderator

Re: CONTAINS purpose [message #445537 is a reply to message #445334] Tue, 02 March 2010 06:56 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
As BlackSwan shows via his link, oracle has many ADD-ON product features. One such feature is called ORACLE TEXT OPTION (though it has gone by many other names in the past).

This advanced feature of Oracle is one attempt by Oracle to provide your database with an ability similar to GOOGLE. The idea is to provide a SEARCH ENGINE that integrates with Oracle's SQL. With ORACLE TEXT OPTION installed (might come standard these days who knows), you can create special kinds of indexes on columns with lots of data. I believe you can also create indexes on WORD docs, EXCEL docs, PDF docs, and pretty much anything else. Then you can use the CONTAINS expression to search for data and these indexes are supposed to kick in and find stuff fast for you.

Naturally there are multiple levels of difficulty depending upon how deep you want to get into it. For examples you can index simple varchar2 columns easily enough and search them without doing lots of work. Going further is more difficult.

Additionally you should consider that CONTAINS and ORACLE TEXT in general was meant to be a real powerful search engine and as such it has lots of features and lots of it own quirks.

Lastly, last I knew, this was in fact not really and Oracle product but Oracle licensed the technology from a third party and rebranded it as their own (who knows... by now Oracle has probabely bought what ever company made this thing and so it would be an Oracle product (hehe)). But what this means is you can't change lots of the things it does. If it works in a specific way and you don't like it, too bad. My company found this out and dropped its use. It did things that my customers wanted not to do but there was no way to turn off the behavior and modify it to our liking.

So make sure you read about it and play with it for a while to understand if it is really what you want to do. It works as advertised, but like all Oracle Advanced features, they do not advertise everything.

Good luck, Kevin
Previous Topic: How to consume exposed services of sharepoint in PL/SQL?
Next Topic: Join Tables
Goto Forum:

Current Time: Fri Jul 21 02:18:16 CDT 2017

Total time taken to generate the page: 1.17570 seconds