Oracle Text on Multiple Columns
I have a product table that I want to have a multi-column (oracle text CTXCAT) index on.
Product_Table
ItemID,
Company_id,
Product_name varchar(255),
Product_brand varchar(50),
Product_description varchar(2000)
If a user executes a request for Apple iPod Nano. I want the database to look at the product_name which will contain iPod Nano, then the Brand which will contain Apple, then the description. Ideally, the description would be less important than the title and brand.
Second question – If I want to narrow the search by company_id, is that better to be a straight where companyID=123. Since I neeto narrow down by company_id, am I better to convert all query data into a clob format and use MDATA.
Does anyone have a suggestion or resource to point me to?
Eric
Product_Table
ItemID,
Company_id,
Product_name varchar(255),
Product_brand varchar(50),
Product_description varchar(2000)
If a user executes a request for Apple iPod Nano. I want the database to look at the product_name which will contain iPod Nano, then the Brand which will contain Apple, then the description. Ideally, the description would be less important than the title and brand.
Second question – If I want to narrow the search by company_id, is that better to be a straight where companyID=123. Since I neeto narrow down by company_id, am I better to convert all query data into a clob format and use MDATA.
Does anyone have a suggestion or resource to point me to?
Eric
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I downloaded and installed the new Oracle Secure Enterprise Search, this is a replacement for UltraSearch. SES is a much better application. It is a little slower than I expected when generating the index. I am having it pull 750,000 records, so I didn't expect it to be instant. It ran for many hours, the document pull was fast, but indexing was the part that took a long time.
Another approach would be to add a CLOB column that contains the data in a XML format and use the Oracle text features of search XML.