Re: ORACLE* Text Retrieval

From: Leo Mannhart <mannhart_at_zuv.unizh.ch>
Date: Wed, 4 May 1994 08:27:09 GMT
Message-ID: <mannhart-040594092709_at_zuvmaclm.unizh.ch>


In article <767911821snz_at_willpowr.demon.co.uk>, LWill_at_willpowr.demon.co.uk (Leonard Will) wrote:

> In article <2q2kt2$7ps_at_worak.kaist.ac.kr>
> jungkc_at_dbserver.etri.re.kr "Jung Kwang Chul" asks for information
>
> about the ORACLE* Text Retrieval system and its applications.
> ^^^^^^^^^^^^^^^^^^^^^^
> I am also interested in this, and would like to hear from anyone who has
> investigated it or used it. I am not at present an Oracle user, but Oracle
> version 7 is one system we shall be looking at for a project I am working on.
>
> In particular:
>
> 1. How well integrated is the text retrieval system with the
> relational database management system?
The text retrieval system consists of a few user exits for forms and a few programs for setting up text retrieval users, loading and dumping text tables, indexing text strings ... There is a special user "systext" owning a few special text retrieval tables, granting access to text retrieval users. This setting you also find with ORACLE CASE (with user SYSCASE owning...) This administrating tasks (as well as a simple retrieving form) are mixed up in a forms called "sqltr". This is something similar as SQL*DBA is for the whole DB.

>
> 2. At a demonstration I saw a year or two ago, it was necessary to
> exit the database system and invoke the text retrieval system as
> a separate function in order to use it. This is very cumbersome;
> has it been improved?
What do you mean with "exiting the database system"? If you are in SQL*Plus and now want use SQL*Loader you usually also are exiting the database system. Now in a real application you best use SQL*Forms with the appropriate user exits for doing your text retrieval work. In fact, for using SQL*TextRetrieval with forms (and menu) you have a program "rtiap30mnu" wich means you have linked runform (iap30) with menu (mnu) and text retrieval (rt) together. So the disadvantage is, you can't test your forms directly in SQL*Forms (generate and execute) as the normal SQL*Forms program lacks the user exits for the text retrieval system.

>
> 3. Is there a limit to the number of fields on which text retrieval can
> be used, or can it be used on any fields in the database?
I'm not sure. Usually one is using text retrieval on long fields and you can have only 1 long field per table but I'm not sure there is a limit for the number of tables with long field in a database. Now in SQL*TR you define one column of a table (called a text table) as the text column and I have not tried to define a column of say VARCHAR2(30) as text column (why should I?). On the other hand I don't know if there is a limit of such text columns in a DB (other than system dependent)

>
> 4. Is it possible to combine structured and text fields (if they have to
> be thought of as distinct) in one search statement, i.e. can you
> ask something like:
>
> Select from persons where name is "Mandela*" and date from
> 1994/05/01 to 1994/05/31 and biography contains "Africa" and
> "National Party" or "ANC".
>
> assuming that 'name' and 'date' are relational database fields and
> 'biography' is a text field.
Yes this is exactly what I did. I defined a text table as a table of 25 "normal" columns and one long column (you can define your table in the manner you like. I do it in SQL*Plus with a SQL-Script. In SQL*TR I then define these table as text table and SQL*TR is clever enough to extract the column definitions from the dictionary. I then have to say wich column is the text column and wich column is the text key). In SQL*Forms the designer of the forms can now use the power of querying with the extended search features for long columns and displaying a SQL*TR generated "hitlist" or use the normal query style. There is a quiet good example in the manual, but double check the user exits with the definition of the user exits and wich user exit must be followed by wich user exit.

>
> 5. Are full text-editing facilities provided in input and update screens,
> allowing both text and structured fields to be edited with cut and
> paste, insertion and deletion of words and characters, word wrap and
> scrolling within boxes? If it is necessary to switch to a separate
> word-processing package to perform these functions, how transparent is
> this to the user, or are they conscious of having to switch between
> Oracle and word processor?
For entering or updating data (and then reindexing the words) in the text column you use a special user exit. This invokes the system editor. But you can define your prefered text editor in SQL*TR (system wide or on a per user basis). So on unix one can use vi, others are using emacs and a few can using WordPerfect (or whatever you like) for editing text columns. SQL*TR therefore needs temporary files. If you redefine the key edit_text in forms for the text column, is this transparent for the user (on other fields invoking the forms editor)?

>
> 6. What would be nice would be to be able to get away from the fixed-
> length field restriction which Oracle, and most other relational
> systems, seem to require. That is, I would like to see a system
> which had the relational features of tables, joins and so on, but
> which did not impose artificial lengths on data, causing restrictions
> on input if they are chosen too short and waste of storage if they
> are chosen too long. How far does Oracle text retrieval go towards
> providing this?
You mean defining a column as "character" and the dbms is determining on the fly how much space it must allocate for the actual input or update? And an equal performance...
Oracle doesn't waste storage if you define a field as VARCHAR2(30) but only use say two characters (opposed to the definition of a field of CHAR(30) in Oracle7).
Sure with text retrieval you have extended search capabilites but the dbms has other restrictions.

- There is only one long column per table possible
- substr functions are not allowed for long columns
- ...

Text retrieval is built for free text searching, you can have a thesaurus and keywords and the like but it is not ment for new column definition usage.

>
> Any other points or views on Oracle text-retrieval would be of great interest.
> You can either post them to this group, or if you prefer to email me
> privately I shall summarise for the group.
We have just started a project where we are using SQL*TextRetrieval so my expirience is a little bit limited.
I have no idea if and how one can integrate this in forms 4. Support here in Switzerland is good and the people are willing to help you. But then we have access to OSIS (Oracle support information system) where one can have a look at the status of his problem(s).

>
> Thanks

You're welcome
LM

>
> Leonard Will
>
> --
> Dr Leonard D Will Tel: +44 81 366 7386
> Information Management Consultant Fax: +44 81 366 0916
> 27 Calshot Way, ENFIELD, Middlesex Email: LWill_at_willpowr.demon.co.uk
> EN2 7BQ, United Kingdom
 

-- 
Leo Mannhart
Planning Office
University of Zurich               phone: ++41 1 257 23 34
Kuenstlergasse 15                    fax: ++41 1 257 22 12
CH-8001 Zurich, Switzerland        eMail: mannhart_at_zuv.unizh.ch
Received on Wed May 04 1994 - 10:27:09 CEST

Original text of this message