Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle 8i question. URGENT PLS !!!

uform
Frequent Advisor

Oracle 8i question. URGENT PLS !!!

Hi,

In the following query , RET_PM_PROD , RET_RLNSHP , RET_CLNT tables are defined in context policy table and enabled.

If i run the query by changing FIRST line of where clause to
Where rb.s_num = 21 , it takes only 2-3 mins to run. But when i put back the where clause
as " Where rb.s_num >= 20 and rb.s_num < 40 "
query hangs.

If i disable context policy...my query runs normally even with where clause (Where rb.s_num >= 20 and rb.s_num < 40 ).

Question 1: What is this Context policy for or how is it affecting my query ?

Question 2: I dont think i need to modify the query as it is working properly when context policy is disabled. OR am i wrong and query needs to re-written ??


SELECT count(*)
FROM RET_PROD rp, RET_PM_PROD rpp, RET_RLNSHP rr, RET_CLNT clnt, RET_BAL rb
Where rb.s_num >= 20 and rb.s_num < 40
and rb.clnt_hrcy = clnt.clnt_hrcy
and clnt.c_key = rr.c_key
and rr.a_key = rpp.a_key
and rb.t_key = 0
and rp.p_key = rpp.p_key
and not (a_naic='02' and (a_lid='TUH' or a_lid='WSD'))
4 REPLIES
spex
Honored Contributor

Re: Oracle 8i question. URGENT PLS !!!

Hi,

1) Oracle ConText (now called InterMedia) allows for integration of rich media into tables. Such media (images, documents, video, etc.) are usually very large, which explains why disabling the context policy speeds things up. See the Oracle InterMedia FAQ available here for more information:
http://orafaq.com/faqctx.htm

2) The query looks fine to me. If things are slow, make sure you have appropriate indexes in place.

PCS
Indira Aramandla
Honored Contributor

Re: Oracle 8i question. URGENT PLS !!!

Hi,

ConText is an Oracle server option which enables text queries to be performed through SQL and PL/SQL from most Oracle interfaces. ConText provides advanced indexing, analysis, retrieval, and viewing functionality that can be integrated into any text applications that use the Oracle Server.

A policy is a logical grouping of six indexing preferences (one preference for each of the supported categories), assigned to a column in the database. A policy specifies the options used by ConText to create the index for the text in the column. It is also used to generate linguistic information for use in ConText applications.

In other words a policy is a synthetic object like a preference. You can think of it as a context index minus the index data -- that is, it's just a collection of index objects and attribute settings.

Policies can be created by any ConText user with the CTXAPP role. Policies are stored in the ConText data dictionary. In addition to the preferences for a policy, users specify a name for the policy and the text column for the policy, and a number of other policy attributes. The policies created by a user must be unique for the user. As such, the same policy for a user cannot be assigned to more than one column.


IA
Never give up, Keep Trying
uform
Frequent Advisor

Re: Oracle 8i question. URGENT PLS !!!

I'm not sure whether we are talking about same context here ... i'm quering the following table to see the context policy

select * from all_policies

RINGS ANY BELL ?
uform
Frequent Advisor

Re: Oracle 8i question. URGENT PLS !!!

Any help appreciated. !!