Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Package for manipulating LONG fields
On Fri, 25 Sep 1998 15:34:59 +0200, Flemming Jans <fj_at_belle.dk> wrote:
>> >In Oracle8 there is a package dbms_lob for manipulating lobs,
>> >reading/writing piecewise etc.
>> >
>> >Is there such a package for ordinary LONG fields also, or do I have to
>> >use OCI ????
>>
>> In v7.3 and up dbms_sql will let you piecewise READ a long in pl/sql (but not
>> insert/update). The following is an example of how to do this. Other then
>> that, yes -- you will have to use OCI or PRO*C or some other 3gl like language
>> to manipulate longs.
>>
>
>Unfortunataly I also have to write the fields back into another table.
>
>But maybe there is another solution to my problem:
>I have to context search in long fields from about 50 different tables. Using a
>trigger I would copy all changes on the long fields to a single table that I could
>search in.
>
>Could this be done simply by creating a view over the 50 tables and creating the
>context index on that view ???
>
I think this would become the definiton of "DBA Nightmare" very quickly. :)
Even without ConText involved, if you created a VIEW which was actually based on a query against 50 (!) different tables, this would tend to be not efficient to say the least.
Secondly, with ConText, you can specify only one policy per column...you couldn't say "this ConText policy applies to column1, column2,..., and column50. So...unless you concatenate the columns, you are going to end up with 50 different ConText policies and potentially 50 different ConText queries executing concurrently.
Your best way to do this is as you suggest (and is what I've done before). It takes up more space, but it is very easy to set via an INSERT or UPDATE trigger to copy the data to a common table. That way, you have all 50 tables feeding one common table, and it is this table that you create your ConText policy and index on.
>Regards,
>
>Flemming
>
Thanks!
Joel
Joel R. Kallman Oracle Government, Education, & Health
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com