Intermedia text table design

From: Ranganath K <ranganathk_at_subexgroup.com>
Date: Fri, 06 Apr 2001 06:10:26 -0700
Message-ID: <F001.002E3DFC.20010406054524@fatcity.com>

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.

1. 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: ranganathk_at_subexgroup.com

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: ListGuru_at_fatcity.com (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). Received on Fri Apr 06 2001 - 08:10:26 CDT