CREATE TABLE “PERSION”.“ceshi”
(
“id” INT NOT NULL,
“01” VARCHAR(255),
“02” CLOB,
“03” VARCHAR(100),
NOT CLUSTER PRIMARY KEY(“id”) USING INDEX TABLESPACE “PERSION02_INDEX” ) STORAGE(ON “PERSION01_DATA”, CLUSTERBTR);
COMMENT ON TABLE “PERSION”.“TEST_txt” IS ‘全文索引测试’;

SELECT “id”, “01”, “02”, “03”
FROM “PERSION”.“ceshi”;

–创建全文索引1
CREATE CONTEXT INDEX idx_ceshi_01 ON “ceshi” (“01”)
LEXER CHINESE_FP_LEXER
SYNC TRANSACTION;
–更新全文索引
ALTER CONTEXT INDEX idx_ceshi_01 ON “ceshi” Rebuild;
–增量更新
ALTER CONTEXT INDEX idx_ceshi_01 ON “ceshi” Increment;

–创建全文索引2
CREATE CONTEXT INDEX idx_ceshi_02 ON “ceshi” (“02”)
LEXER CHINESE_FP_LEXER
SYNC TRANSACTION;
–更新全文索引
ALTER CONTEXT INDEX idx_ceshi_02 ON “ceshi” Rebuild;
–增量更新
ALTER CONTEXT INDEX idx_ceshi_02 ON “ceshi” Increment;

–创建全文索引3
CREATE CONTEXT INDEX idx_ceshi_03 ON “ceshi” (“03”)
LEXER CHINESE_FP_LEXER
SYNC TRANSACTION;
–更新全文索引
ALTER CONTEXT INDEX idx_ceshi_03 ON “ceshi” Rebuild;
–增量更新
ALTER CONTEXT INDEX idx_ceshi_03 ON “ceshi” Increment;

–使用全文索引进行检索
– 检索 01 字段中包含’金昌’的记录
SELECT * FROM “ceshi” WHERE CONTAINS(“01”, ‘金昌’);

– 检索 01 字段中同时包含’宁夏’和’科技’的记录
SELECT * FROM “ceshi” WHERE CONTAINS(“01”, ‘宁夏’ AND ‘科技’);

– 检索 01 字段中包含’回族’且 02字段为’人力资源部’的记录
SELECT * FROM “ceshi” WHERE CONTAINS(“01”, ‘回族’) AND “02” = ‘人力资源部’;
ceshi_20250919104059.sql