Using Oracle ConText
One of the disadvantages
of using LONG or RAW columns in Oracle is that they're difficult to search
using conventional means and you cannot create indexes on them in the usual
manner (using the CREATE INDEX statement).
Oracle's ConText cartridge
solves this problem. Using the ConText Option allows the creation of indexes
on LONG columns which in turn allows them to be searched
Installing
Oracle ConText:
Well, if you're here,
you probably know all that, you just want to know how to install this thing.
The Steps for installing Context are:
- Install oracle ConTEXT
Cartride using Oracle Installer.
- Create a separate
tablespace for ConText minimum 100M.
- Run creation script
for creating context user CTXSYS,drcsyst.sql
in $ORACLE_HOME/ctx/admin. Must run as user SYS.
The script will
prompt you for four values of the user to create:
[username] [password] [default tablespace]
[temp tablespace]
Set the default tablespace to be one created in step 2.
- Create context objects
- run script
$ORACLE_HOME/ctx/admin/dr0inst
as CTXSYS
user.
- Run script drlngset.sql
as CTXSYS to set up linguistics tables.
- Add TEXT_ENABLE=TRUE ininit<SID>.ora file.
- At this point,ConTexT is essentially setup. The next steps
you'll have to do as the schema owner of the tables that you want to runConText
on.
- Grant the ConTexT Admin privilege to the object schema owner. (i.e. GRANT CTXADMIN to <Schemaowner>;)
Start Context Service: on unix shell run:
ctxsrv -user ctxsys/<ctxsys pw> -personality QDML
Note: this process must be running for ConTexT to work.
- As <SCHEMA Owner> create the following policy & index.
EXECUTECTX_DDL.CREATE_POLICY('<policyname>','<tablename>.<column>');
EXECUTE CTX_DDL.CREATE_INDEX('<policyname>');
For example,say you have the following table DDL:
CREATE TABLE
MESSAGE
(
MESSAGE_ID NUMBER,
TEXT LONG
);
And you want to enable ConTexT to search the text column.
You would then run the following(after granting CTXADMIN to this table's schema
owner):
Note that the policy name can by anything you want as long as it is unique.
EXECUTE CTX_DDL.CREATE_POLICY('MY_POLICY','MESSAGE.TEXT');
EXECUTE CTX_DDL.CREATE_INDEX('MY_POLICY');
Note that the ctxsrv process MUST first be running for this to work.
- At this point, you are all set to perform searches on the TEXT
column!
Once you get everything setup, you'll probably want to put
Step 8 into the background, and redirect output to
/dev/null2>&1
EXECUTING SEARCHES:
Once CONTEXT is running, you are set to perform searchs
on LONG Columns. The Syntax is as follows:
SELECT * FROM <TABLE> WHERECONTAINS(<LONG
COLUMN>,'<SEARCHPHRASE>') >0
Using the MESSAGE table above as an example, the syntax
for a search on the TEXT column for the phrase 'DBA Service" would be as follows:
SELECT * FROM MESSAGE WHERE CONTAINS(TEXT,'DBA Service') >
0;
|
