Solved

Oracle Text on Multiple Columns

Posted on 2006-10-27
3
1,148 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 250 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

Industry Leaders: 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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

691 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