Solved

Oracle Text on Multiple Columns

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Export table into csv file in oracle 10 229
how to trim oracle sql sentence in unix 17 58
Oracle - Query link database loop 8 40
Oracle collections 15 26
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

789 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