ConText Optimization

ConText Optimization

Post by David Schwart » Fri, 21 Aug 1998 04:00:00



Is is possible, using column indexes, table joins, or other optimization
techniques, to get ConText to search through a subset of the rows of a
text-indexed table instead of the entire table? I have tried several
things and don't see any improvement in performance.
Any hints would be greatly appreciated.

            David Schwartz

 
 
 

ConText Optimization

Post by Rod Stewar » Mon, 24 Aug 1998 04:00:00


I haven't been able to get one to work satisfactorily.  It is most likely
that you will need to redesign your data structures to accommodate your
context policies - depending on how important the context functionality is
to you.  We get results back on over 8 Gig of text documents (stored in long
raw columns in the database) in less than 2.5 seconds on fairly average
hardware.  How much data are you searching through?  Remember that context
does not actually search through the table, but rather looks up the indexes
created or updated on insert and update.  Perhaps you could use a post
insert/update trigger to populate a separate table with just the desired
data in it and apply a context policy to that.

>Is is possible, using column indexes, table joins, or other optimization
>techniques, to get ConText to search through a subset of the rows of a
>text-indexed table instead of the entire table? I have tried several
>things and don't see any improvement in performance.
>Any hints would be greatly appreciated.

>            David Schwartz



 
 
 

1. Help Debugging CURSORS (context areas)

Hi.  I'm helping a friend debug an Oracle application coded in SQL*FORMS v3,
and we've been repeatedly encountering a nasty problem involving cursors
(i.e. context areas).  The application seems to work properly, except that
after dozens of high-level operations (such as adding a customer or an order)
are performed, we inevitably get one of the following errors:

        (most common)  ORA-00604: error occurred at recursive SQL level 1
                       (this often occurs during an INSERT that is performed by forms)

        (also common)  ORA-01000: maximum open cursors exceeded
                       FRM-40735: trigger raised unhandled exception INVALID_CURSOR

Since max_cursors is set to 50 in init.ora, it appears that somewhere cursors are
being opened and are not being properly closed.  However, we are only using explicit
cursors in a small number of places.  One involves a FOR loop to fetch records
(and thus the open and close are implicit), and the others are simply to test if there
are any records that meet a certain criteria, using cursor%FOUND.  In these cases,
we open the cursor, save cursor%FOUND in a boolean variable, and then immediately
close the cursor.

So, the bottom line is that we're puzzled.  We have a call in to Oracle Tech Support,
but so far they haven't been very helpful.  So I suppose my questions are:

        * Has anyone experienced (and hopefully resolved) a similar problem?
        * Does anyone have any debugging tips or techniques that may be useful?
        * Any ideas about where to look for problems involving implicit (SQL) cursors?

We are using Oracle on a Sun4 under SunOS 4.1.1, running RDBMS v6.0.33,
PL/SQL v1.0.32, and SQL*FORMS v3.0.16.

Any information or pointers would be greatly appreciated.  Please send messages as

post any messages received via e-mail.  Thanks for your time and help.

        - Carl Waldspurger

2. CP/M BIOS code wanted

3. SUMMARY: Debugging Cursors (context areas)

4. RCS symbolic names, states, etc. ?

5. about Context technology

6. Windows 2000 Virtual SMTP

7. ORA-6403: cannot allocate context area

8. Opinions about Mitsumi FX-001D and SB-16 MultiCD combo?

9. CONTEXT OPTION problems

10. Oracle's Textserver3 with Context

11. Help! ConText Option

12. Oracle ConText + Embedded SQL?

13. HELP: Automatically Starting Context Server? HOW TO?