Solved

Oracle Text on Multiple Columns

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 67
Need help converting some sql server sql to Oracle sql 9 72
Processing of multiple cursor 6 35
Oracle 10g - insert string with special characters 8 41
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now