Solved

Oracle Text on Multiple Columns

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

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

706 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

20 Experts available now in Live!

Get 1:1 Help Now