?
Solved

Oracle Text on Multiple Columns

Posted on 2006-10-27
3
Medium Priority
?
1,164 Views
Last Modified: 2010-01-27
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
0
Comment
Question by:egarrison
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 6

Accepted Solution

by:
Ritesh_Garg earned 750 total points
ID: 17826873
To get the functionality you need is bit tricky and lot of it depends on kind of data entered. For e.g. the user may enter "iPod NANO Apple" - note that the company name is after the product and Nano is in upper case.  You may need to make the query case insensitive and rely on INSTR function if you wish to use only PL/SQL or SQL features.

However, Oracle is now providing Ultra Search Feature in the database.  Please check otn.oracle.com   -- database documentation.  This may be setup in the environment and with certain java api's it can be integrated with other applications.

Let me know if you need help in either of these approach.
Thanks,
Ritesh
0
 
LVL 35

Expert Comment

by:johnsone
ID: 17834391
Oracle Text does provide case insensitive searching.

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.
0
 
LVL 3

Author Comment

by:egarrison
ID: 17839335
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question