I'm working on a Recipe app (personal/home with APEX front end) which has the the ability to add multiple TAGs to a recipe.
Right now, the "list of tags" is stored as-is from an APEX Multi-row Select Item. That is: it is a colon separated list of TAG_IDs
(I have no problem changing the model)
Example Data:
create table recipes as
select 1 as recipe_id, 'eggs, sausage, pancakes' as recipe_name, '1:2:5' as tags from dual union all
select 2, 'Green Eggs and Ham', '1:5' from dual union all
select 3, 'Coffee', '1:6' from dual union all
select 4, 'Bacon and eggs', '5:1' from dual
The primary query of the application (that involves the TAGs) is a "match all tags to be searched" query.
So, the query for tags '1:5' should match '1:5', '5:1', and '1:2:5' (but not '1:6' )
I have that working. As expected, it does an FTS.
Then, I start thinking about "what I need in order to efficiently query a table with >10M rows":
The idea came up: Index the collection of tags (via Domain Index).
So, my question for the forum:
Does a Domain Index already exist that can index a collection of tags?
I'm only asking because I don't feel like duplicating someone else's effort.
(I have no fear about trying to build a rudimentary Domain Index)
I'm willing to take a look at "better" options. But, the tag search query has to be as easy to implement in APEX as I currently have:
select *
from recipes
where matchAllTags( tags, :P1_TAGS ) = 1 -- always TRUE if :P1_TAGS IS NULL
Thanks
MK
EDIT - Adding a collection of key posts
Custom Domain Index for non-3NF table (Odie63) : https://community.oracle.com/message/15614009#15614009
Query for 3NF table design (Tubby) : https://community.oracle.com/message/15613817#15613817
Attempted setup using Oracle Text (me): https://community.oracle.com/message/15613270#15613270
Benchmark comparing all 3 method: https://community.oracle.com/message/15614183#15614183
I can't split "correct answer" across 2 posts. The original request was for a Domain Index. So, the ODCI solution is "correct".
Please note - Proper Schema Design would result in Tubby's solution.