Re: ORACLE* Text Retrieval
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.chReceived on Wed May 04 1994 - 10:27:09 CEST