Just a couple of notes from studying Oracle Text…

1. What is the default index?
Its CONTEXT. The text column can be of type CLOB, BLOB, BFILE, VARCHAR2, or CHAR.

CREATE INDEX idx_ft_meta_en_name ON poi_app(ft_meta_en_name) INDEXTYPE IS CTXSYS.CONTEXT;

2. When you perform inserts or updates on the base table, you must explicitly synchronize the index with CTX_DDL.SYNC_INDEX.

SQL> EXEC CTX_DDL.SYNC_INDEX(‘idx_docs’, ’2M’);

3. CONTAINS Phrase Queries

If multiple words are contained in a query expression, separated only by blank spaces (no operators), the string of words is considered a phrase and Oracle Text searches for the entire string during a query.

4. Logical Operators
Picture 1.png

5. Some sample SQL queries:

-- Simple Query
SELECT SCORE(1) AS myscore, en_name, en_visname, py_name FROM poi_app WHERE CONTAINS(ft_meta_en_name, 'grammy center', 1) > 0 ORDER BY myscore DESC;
 
SELECT SCORE(1) AS myscore, en_name, en_visname, py_name FROM poi_app WHERE CONTAINS(ft_meta_en_name, 'cybersoft', 1) > 0;
 
-- Query Rewrite
SELECT en_name, en_visname, py_name
FROM poi_app
WHERE CONTAINS (ft_meta_en_name,
'<query>
<textquery lang="ENGLISH" grammar="CONTEXT"> international hotel boya
<progression>
<seq><rewrite>transform((TOKENS, "{", "}", "AND"))</rewrite></seq>
<seq><rewrite>transform((TOKENS, "{", "}", "ACCUM"))</rewrite></seq>
</progression>
</textquery>
<score datatype="INTEGER" algorithm="COUNT"/>
</query>')>0;
 
-- Query 'About'
SELECT en_name, en_visname, py_name, score(1)
FROM poi_app
WHERE CONTAINS(ft_meta_en_name, 'about(italian restaurants)', 1) > 0
ORDER BY SCORE(1) DESC;
 
-- Query logical
SELECT en_name, en_visname, py_name, score(1)
FROM poi_app
WHERE CONTAINS(ft_meta_en_name, 'beijing, international, hotel', 1) > 0
ORDER BY SCORE(1) DESC;