Discussions
Categories
- 385.1K All Categories
- 2.6K Data
- 618 Big Data Appliance
- 2K Data Science
- 452.3K Databases
- 222.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 32 Multilingual Engine
- 576 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 576 SQLcl
- 4K SQL Developer Data Modeler
- 187.8K SQL & PL/SQL
- 21.5K SQL Developer
- 13 Data Integration
- 13 GoldenGate
- 297.7K Development
- 3 Application Development
- 18 Developer Projects
- 140 Programming Languages
- 294.4K Development Tools
- 118 DevOps
- 3.1K QA/Testing
- 646.5K Java
- 30 Java Learning Subscription
- 37K Database Connectivity
- 186 Java Community Process
- 107 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 175 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 206 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 588 LiveLabs
- 41 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 197 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 240 Portuguese
Query Optimization - Text Search
Hi,
I'm working on a project with some tables which will contains a lot of records.
I'm looking for a solution to avoid a full-table-scan on those table when the end-users will do some search.
This is a simplify version of my DB Model (in blue the big tables)
- One Post can have one or more products
- Each product can be linked to one or more tags
When the user will search, he will enter some keyword, for example: "red big car"
My actual strategy is to tokenize the search string into words (space delimited) and build the query dynamically.
For each word, I'll find the corresponding tags into a comma separated list and use it in the main query. This is ok, the tags tables are small.
The issue is on the text attributes (NOTES / OTHER_DESC1 / OTHER_DESC2 / PROD_CODE_ORGINAL / PROD_TITLE_ORIGINAL)
The instr(...) will results to a FULL-TABLE-SCAN
select ...
from my_posts t1
where
(
instr(upper(t1.notes),upper('...word1...')) > 0 or ---> FULL TABLE SCAN !!!
instr(upper(t2.other_desc1),upper('...word1...')) > 0 or ---> FULL TABLE SCAN !!!
instr(upper(t2.other_desc2),upper('...word1...')) > 0 or ---> FULL TABLE SCAN !!!
exists (select null
from my_posts_products t2, my_post_products_tags t3
where t2.post_id = t1.post_id
and t2.post_prod_id = t3.post_prod_id(+)
and (
t3.tag_id in (' ...tags found for word1... ') or
instr(upper(t2.prod_code_original),upper('...word1...')) > 0 or ---> FULL TABLE SCAN !!!
instr(upper(t2.prod_title_original),upper('...word1...')) > 0 ---> FULL TABLE SCAN !!!
)
)
)
and (
instr(upper(t1.notes),upper('...word2...')) > 0 or ---> FULL TABLE SCAN !!!
instr(upper(t2.other_desc1),upper('...word2...')) > 0 or ---> FULL TABLE SCAN !!!
instr(upper(t2.other_desc2),upper('...word2...')) > 0 or ---> FULL TABLE SCAN !!!
exists (select null
from my_posts_products t2, my_post_products_tags t3
where t2.post_id = t1.post_id
and t2.post_prod_id = t3.post_prod_id(+)
and (
t3.tag_id in (' ...tags found for word2... ') or
instr(upper(t2.prod_code_original),upper('...word2...')) > 0 or ---> FULL TABLE SCAN !!!
instr(upper(t2.prod_title_original),upper('...word2...')) > 0 ---> FULL TABLE SCAN !!!
)
)
)
and ... word3 ...
I saw that ORACLE TEXT solves this issue by indexing all words etc...
But I've never used it and the documentation is quite complex and in my case I'm not sure which options should I use ? CTXCAT / CONTEXT ?
Ideally the search should have theses options
- stemming
- case insensitive
- accent insensitive
- optional : multi-language (the main language will be English)
I would be very grateful if someone can help me on this.
With Thanks & Regards
Nic
Answers
-
The Oracle Text Application Developers guide is only 280 pages with examples of applications. Additional examples can be found at livesql.oracle.com: https://livesql.oracle.com/apex/livesql/file/tutorial_IHF3DMUBR01852DBZFYKUZF3Q.html
-
Thanks for this.
The Oracle Text Application Developers guide is nice, but there are very few examples.
The additional example on livesql.oracle.com is very useful. Thanks for this.
But as per the documentation, I understand that for my case, I should use the CTXCAT index.
"Use this index for better mixed query performance of small documents and text fragments. To improve mixed query performance, include other columns in the base table, such as item names, prices, and descriptions."
But I'm not sure about that ?
And there are very few example available online.
That's why I would like some advice on this...
Thank you
-
There are many examples in the Oracle community and in my blog (tried to collate all my answers from OTN). Some of the examples are given below. If you need some specific solution, then please put the CREATE TABLE statement, with some sample data and the result / output expected. Also, your database version.
http://saubbane.blogspot.com/2011/02/searching-text-from-pdf-file-blob-from.html
-
There are many examples in the Oracle community and in my blog (tried to collate all my answers from OTN). Some of the examples are given below. If you need some specific solution, then please put the CREATE TABLE statement, with some sample data and the result / output expected. Also, your database version.
-
Thanks Saubhik,
I'll have a look.
But the first think I want to know is the type of index that I should use : CTXCAT / CONTEXT ?
All the text attributes will rarely exceed 100 characters...
-
Regarding CTXCAT Vs. CTXSYS.CONTEXT here are some example, also you can check postings of Barbara Boehme on Oracle Text Forum. There are multiple situations as I told documentation is the best place and if you are looking for a particular use case then please post that. I will try to answer.
-
Thanks Saubhik, I'll check these examples...
