Dear DBA Gurus,
I have given below the procedure as how we are creating the tables,
preference and indexing for the same. Could you please check if there is any
other preference need to be set for CLOB Datatypes which I missed?
1) To create category table.
CREATE TABLE CATEGORY
PK_CATEGORY_ID NUMBER NOT NULL,
PARENT_CATEGORY NUMBER NOT NULL,
NAME VARCHAR2 (1000) NOT NULL,
DEPTH VARCHAR2 (4000) NOT NULL,
STATUS NUMBER NOT NULL,
UPDATED_DATETIME DATE,
PRIMARY KEY ( PK_CATEGORY_ID ) );
2. To create site table.
CREATE TABLE SITE
PK_SITE_ID NUMBER NOT NULL,
FK_CATEGORY NUMBER NOT NULL,
TITLE CLOB,
URL VARCHAR2 (4000) NOT NULL,
DESCRIPTION CLOB,
STATUS NUMBER NOT NULL,
PAGE_HITS NUMBER NOT NULL,
EDITOR_CHOICE VARCHAR2 (10),
PRIMARY KEY ( PK_SITE_ID ));
ALTER TABLE SITE ADD CONSTRAINT FKSITE
FOREIGN KEY (FK_CATEGORY)
REFERENCES VCPLNEW.CATEGORY (PK_CATEGORY_ID) ;
3) Creating an index after inserting the data(Datebase Updation) in both
the tables.
a) Execute this script to create a preference.
begin
ctx_ddl.create_preference('sitelexer', 'BASIC_LEXER');
ctx_ddl.set_attribute('sitelexer', 'printjoins', '_-');
ctx_ddl.set_attribute('sitelexer', 'endjoins', '%');
ctx_ddl.set_attribute ( 'sitelexer', 'index_text', 'YES');
ctx_ddl.set_attribute ( 'sitelexer', 'mixed_case', 'NO');
end;
b) Execute this script to create an indexes.
CREATE INDEX site1descidx ON
site(description)
indextype is ctxsys.context
parameters ( 'LEXER sitelexer' );
CREATE INDEX site2titleidex ON
site(title)
indextype is ctxsys.context
parameters ( 'LEXER sitelexer' );
TIA and Regards,
Ranganath
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ranganath K
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
