How to create an effective Index with this query?

Oracle Context Index on multicolumns

• I have an oracle database (8.1.7) ... it has two columns that i want to context index title and subtitle I want to run a query like this when i'm finished select isbn,title,subtitle from MASTER where contains (concat, 'Cheeses',1) > 0 and get results. (the above query returns 0 records) Here's what i've tried so far to create the DATASTORE http://www.completebook.com/newcontext.txt This does not work... It makes me do this select isbn,title,subtitle from MASTER where contains (concat, 'Cheese within {the_title}',1) > 0; (the above record returns 500 records) But i want both title and subtitle... FYI, Select COUNT(*) from MASTER where title like '%cheese%' returns 500 select count(*) from MASTER where subtitle like '%cheese%' returns 13. Any oracle masters out there willing to take this on? Brian Gannon

• Answer:

  Hi Brian, From your question, there appears to be multiple ways to solve your problem. Essentially what you need to do is consider both the title and the subtitle columns together in order to obtain the desired result. METHOD #1: OR Condition --Begin Query SELECT isbn,title,subtitle FROM MASTER WHERE title like '%cheese%' OR subtitle like '%cheese%' --End Query This query will include rows that have the string cheese somewhere within the title and/or subtitle. In other words, only a single conditino needs to be met for the row to be included in the result. METHOD #2: UNION Condition --Begin Query SELECT isbn, title, subtitle FROM MASTER WHERE title like '%cheese%' UNION SELECT isbn, title, subtitle FROM MASTER WHERE subtitle like '%cheese%' --End Query This method is slightly different as far as the actual operation being conducted. There are actually two queries being executed here where each one considers one condition. The 'UNION' operator will take these two resulting tables and combine them into a single result. For rows that contain 'cheese' in both the title and subtitle, the duplicate will be removed. If you want to keep the duplicate, simply replace 'UNION' with 'UNION ALL'. Either of the above queries should do what you require, but feel free to post a clarification if any of the above information is unclear :) Cheers! answerguru-ga Google Answers Researcher