OraFAQ Forum: Text & interMedia » CONTEXT index and Tune with context (2 cross-posts merged by bb)

Home » Server Options » Text & interMedia » CONTEXT index and Tune with context (2 cross-posts merged by bb)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CONTEXT index and Tune with context (2 cross-posts merged by bb) [message #350679]

Thu, 25 September 2008 21:46

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

Hello Sir,

I'm using CONTEXT index for one table column, now in my query i'm using statement shown below

Where Contains(pdue_policy_no,:prm_policy_no) > 0

but in fact i'have to use

Where pdue_policy_no =
nvl(:prm_policy_no,nvl(pdue_policy_no,'ZZ'))

since if :prm_policy_no is NULL it shold get value in pdue_policy_no but if i use Contains i cannot use nvl.But Contains is giving performance improvement

Thanks in advance for your guidance

Report message to a moderator

CONTEXT index and Tune with context (2 cross-posts merged by bb) [message #351194 is a reply to message #350679]

Mon, 29 September 2008 15:58

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Your duplicate cross-post in the tuning forum was deleted. Please read and comply with the forum guidelines by posting each question only once in one forum and posting a proper question by providing create table and insert statements for sample data and the corresponding results that you want based on that data, given different search parameters.

Report message to a moderator

Re: CONTEXT index and Tune with context (2 cross-posts merged by bb) [message #351195 is a reply to message #351194]

Mon, 29 September 2008 16:04

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Given what little you have provided, the condition

Where pdue_policy_no =
nvl(:prm_policy_no,nvl(pdue_policy_no,'ZZ'))

returns the same as

WHERE pdue_policy_no IS NOT NULL

as shown below. So, if that is what you want, then that is what you should use and should be the most efficient. If that is not what you want, then you need to clarify with examples and/or rethink you scenario.

SCOTT@orcl_11g> CREATE TABLE policy_dues
  2    (pdue_policy_no	VARCHAR2(30))
  3  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO policy_dues VALUES ('same')
  3  INTO policy_dues VALUES ('notsame')
  4  INTO policy_dues VALUES (null)
  5  INTO policy_dues VALUES ('ZZ')
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl_11g> CREATE INDEX I_TEXT_PDUE_POLICY_NO
  2  ON POLICY_DUES (PDUE_POLICY_NO)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

SCOTT@orcl_11g> VARIABLE prm_policy_no VARCHAR2(30)
SCOTT@orcl_11g> EXEC :prom_policy_no := 'same'
SP2-0552: Bind variable "PROM_POLICY_NO" not declared.
SCOTT@orcl_11g> SELECT * FROM policy_dues
  2  Where  pdue_policy_no = nvl (:prm_policy_no, nvl (pdue_policy_no, 'ZZ'))
  3  /

PDUE_POLICY_NO
------------------------------
same
notsame
ZZ

SCOTT@orcl_11g> SELECT * FROM policy_dues
  2  Where  pdue_policy_no IS NOT NULL
  3  /

PDUE_POLICY_NO
------------------------------
same
notsame
ZZ

SCOTT@orcl_11g> EXEC :prom_policy_no := ''
SP2-0552: Bind variable "PROM_POLICY_NO" not declared.
SCOTT@orcl_11g> SELECT * FROM policy_dues
  2  Where  pdue_policy_no = nvl (:prm_policy_no, nvl (pdue_policy_no, 'ZZ'))
  3  /

PDUE_POLICY_NO
------------------------------
same
notsame
ZZ

SCOTT@orcl_11g> SELECT * FROM policy_dues
  2  Where  pdue_policy_no IS NOT NULL
  3  /

PDUE_POLICY_NO
------------------------------
same
notsame
ZZ

SCOTT@orcl_11g> EXEC :prom_policy_no := 'ZZ'
SP2-0552: Bind variable "PROM_POLICY_NO" not declared.
SCOTT@orcl_11g> SELECT * FROM policy_dues
  2  Where  pdue_policy_no = nvl (:prm_policy_no, nvl (pdue_policy_no, 'ZZ'))
  3  /

PDUE_POLICY_NO
------------------------------
same
notsame
ZZ

SCOTT@orcl_11g> SELECT * FROM policy_dues
  2  Where  pdue_policy_no IS NOT NULL
  3  /

PDUE_POLICY_NO
------------------------------
same
notsame
ZZ

SCOTT@orcl_11g>

Report message to a moderator

Tune with context index [message #351245 is a reply to message #351194]

Tue, 30 September 2008 01:32

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

Hello Sir,
Sorry for posting duplicate messges.

Sir i want your guidance in one problem, my scenario is shown below

I'm tuning a query for better performance. I created a table named POLICY_DUES AS SHOWN

CREATE TABLE POLICY_DUES
(
PDUE_POLICY_NO VARCHAR2(20 BYTE),
PDUE_POLICY_RENEW_NO VARCHAR2(2 BYTE),
PDUE_CASH_TYPE VARCHAR2(2 BYTE) NOT NULL,
PDUE_REC_CODE VARCHAR2(2 BYTE) NOT NULL,
PDUE_SYSTEM_ACTIVITY_NO VARCHAR2(14 BYTE) NOT NULL,
PDUE_SYSTEM_ACTIVITY_CODE VARCHAR2(7 BYTE) NOT NULL,
PDUE_GROSS_DUE NUMBER NOT NULL,
PDUE_CURRENT_BALANCE NUMBER NOT NULL)

I created CONTEXT index as a part of my tunng activity as shown below

CREATE INDEX I_TEXT_PDUE_POLICY_NO ON POLICY_DUES
(PDUE_POLICY_NO)
INDEXTYPE IS CTXSYS.CONTEXT;

I'm using CONTEXT index for pdue_policy_no, now in my query i'm using statement shown below

Where Contains(pdue_policy_no,:prm_policy_no) > 0

but existing statement in same query(which is used in production system) was

Where pdue_policy_no =
nvl(:prm_policy_no,nvl(pdue_policy_no,'ZZ'))

since requirement is if :prm_policy_no is NULL it should get value in pdue_policy_no but if i use Contains i cannot use nvl.But Contains is giving performance improvement so suggest me something with which : prm_policy_no gets value of pdue_policy_no at run time using Contains.

Thanks in advance for your guidance

Report message to a moderator

Re: Tune with context index [message #351427 is a reply to message #351245]

Tue, 30 September 2008 15:43

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Where are your insert statements for sample data and the corresponding results that you want based on that data, given different search parameters? As previously stated, the where clause that you have posted returns the same results as "WHERE pdue_policy_no IS NOT NULL", so you should use that instead. If that is all that you want, then this is not a context problem or a complex tuning problem, just a beginner's sql problem.

Report message to a moderator

Re: Tune with context index [message #351596 is a reply to message #351245]

Wed, 01 October 2008 12:12

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

Hello Sir,

my scenario is shown below

I created a table named POLICY_DUES AS SHOWN

CREATE TABLE POLICY_DUES
(
PDUE_POLICY_NO VARCHAR2(20 BYTE),
PDUE_POLICY_RENEW_NO VARCHAR2(2 BYTE),
PDUE_CASH_TYPE VARCHAR2(2 BYTE) NOT NULL,
PDUE_REC_CODE VARCHAR2(2 BYTE) NOT NULL,
PDUE_SYSTEM_ACTIVITY_NO VARCHAR2(14 BYTE) NOT NULL,
PDUE_SYSTEM_ACTIVITY_CODE VARCHAR2(7 BYTE) NOT NULL,
PDUE_GROSS_DUE NUMBER NOT NULL,
PDUE_CURRENT_BALANCE NUMBER NOT NULL)

Insert into POLICY_DUES
Values(12,1,'ER','T','43443','434',54545,4545)

Insert into POLICY_DUES
Values(13,2,'RT','T','43443','44534',545,45)

I created CONTEXT index as a part of my tunng activity as shown below

CREATE INDEX I_TEXT_PDUE_POLICY_NO ON POLICY_DUES
(PDUE_POLICY_NO)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('SYNC (ON COMMIT)');

Now my query is shown below

SQL>VARIABLE prm_policy_no Varchar2;

SQL>exec :prm_policy_no := Null;

SQL>Select PDUE_SYSTEM_ACTIVITY_NO
From POLICY_DUES
Where Contains(pdue_policy_no,:prm_policy_no) > 0;

On executing it gives error since :prm_policy_no is NULL

But please suggest me a method to pass value of pdue_policy_no to
:prm_policy_no like nvl(:prm_policy_no,pdue_policy_no) but Contains does not support nvl

Thanks in advance

Thanks in advance for your guidance

Report message to a moderator

Re: Tune with context index [message #351641 is a reply to message #351596]

Wed, 01 October 2008 18:20

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Once again, you have failed to provide what results you want based on the data provided, given various search criteria. Supposedly, the optimizer evaluates the where conditions in order and if one is met, then it does not evaluate the others, so you can use a query like the last one in the demo below to obtain the same results as the first query below. The one that produces your error is displayed inbetween. However, once again, until we have a complete example, so that we know what results you want given various null and non-null search parameters, whether you are just looking for equivalence or matching tokens, there is no way that we can advise you what would be the best query and/or indexes. It is like telling somebody that the fastest way to get somewhere is by airplane, then finding out they were just trying to get across the street.


SCOTT@orcl_11g> CREATE TABLE POLICY_DUES
  2  (
  3  PDUE_POLICY_NO VARCHAR2(20 BYTE),
  4  PDUE_POLICY_RENEW_NO VARCHAR2(2 BYTE),
  5  PDUE_CASH_TYPE VARCHAR2(2 BYTE) NOT NULL,
  6  PDUE_REC_CODE VARCHAR2(2 BYTE) NOT NULL,
  7  PDUE_SYSTEM_ACTIVITY_NO VARCHAR2(14 BYTE) NOT NULL,
  8  PDUE_SYSTEM_ACTIVITY_CODE VARCHAR2(7 BYTE) NOT NULL,
  9  PDUE_GROSS_DUE NUMBER NOT NULL,
 10  PDUE_CURRENT_BALANCE NUMBER NOT NULL)
 11  /

Table created.

SCOTT@orcl_11g> Insert into POLICY_DUES
  2  Values(12,1,'ER','T','43443','434',54545,4545)
  3  /

1 row created.

SCOTT@orcl_11g> Insert into POLICY_DUES
  2  Values(13,2,'RT','T','43443','44534',545,45)
  3  /

1 row created.

SCOTT@orcl_11g> CREATE INDEX I_TEXT_PDUE_POLICY_NO ON POLICY_DUES
  2  (PDUE_POLICY_NO)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS ('SYNC (ON COMMIT)')
  5  /

Index created.

SCOTT@orcl_11g> VARIABLE prm_policy_no Varchar2(30)
SCOTT@orcl_11g> exec   :prm_policy_no := Null;

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> Select PDUE_SYSTEM_ACTIVITY_NO
  2  From   POLICY_DUES
  3  Where  pdue_policy_no like nvl (:prm_policy_no, pdue_policy_no)
  4  /

PDUE_SYSTEM_AC
--------------
43443
43443

SCOTT@orcl_11g> Select PDUE_SYSTEM_ACTIVITY_NO
  2  From   POLICY_DUES
  3  Where  Contains (pdue_policy_no, :prm_policy_no) > 0
  4  /
Select PDUE_SYSTEM_ACTIVITY_NO
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 1


SCOTT@orcl_11g> Select PDUE_SYSTEM_ACTIVITY_NO
  2  From   POLICY_DUES
  3  Where  :prm_policy_no is null
  4  or     Contains (pdue_policy_no, :prm_policy_no) > 0
  5  /

PDUE_SYSTEM_AC
--------------
43443
43443

SCOTT@orcl_11g>

Report message to a moderator

Re: Tune with context index [message #351651 is a reply to message #351641]

Thu, 02 October 2008 00:01

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

Hello Sir,

I was not asking you the proper question, that's my fault

Here as shown below since :prm_policy_no is NULL that is why i got error, clear

SCOTT@orcl_11g> Select PDUE_SYSTEM_ACTIVITY_NO
2 From POLICY_DUES
3 Where Contains (pdue_policy_no, :prm_policy_no) > 0
4 /
Select PDUE_SYSTEM_ACTIVITY_NO
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 1

I can write the query in this manner as shown below

SCOTT@orcl_11g> Select PDUE_SYSTEM_ACTIVITY_NO
2 From POLICY_DUES
3 Where :prm_policy_no is null
4 or Contains (pdue_policy_no, :prm_policy_no) > 0
5 /

PDUE_SYSTEM_AC
--------------
43443
43443

agreed but executing the query in this manner is degrading my performance, my POLICY_DUES
table contains millions of records hence suggest syntax or feature or method to write CONTAINS in one statement.
*******************************************************
These are two statements, right?

Where :prm_policy_no is null
Or Contains (pdue_policy_no, :prm_policy_no) > 0

Can i write

Where Contains (pdue_policy_no, Nvl(:prm_policy_no,pdue_policy_no)) > 0

If no suggest something similar to this

*******************************************************

Thanks in advance

Report message to a moderator

Re: Tune with context index [message #351653 is a reply to message #351651]

Thu, 02 October 2008 00:36

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

I have already asked you three times to provide a complete example with the results that you want based on the data given various search criteria and you have still failed to do that. How do you expect anybody to help you without that? How can somebody write a query to return the unknown, much less make it faster? If you do not provide what has been asked for, I will ignore your future posts. If a query returns an error, then obviously no you can't use it. As previously stated, if you want the results that your other query provides, given what little data you have provided, then you might as well use

WHERE pdue_policy_no IS NOT NULL

The above returns the same as the original where clause that you posted and is the most efficient. We are not even at a stage where we could go into how you think you have determined what might be most efficient.

Report message to a moderator

Re: Tune with context index [message #351670 is a reply to message #351653]

Thu, 02 October 2008 04:41

getritesh
Messages: 235
Registered: July 2006
Location: INDIA

Senior Member

Hello Sir,

i will make myself simple, i just want to ask a simple question

Can we use NVL with CONTAINS like as shown below

Where Contains (pdue_policy_no,
NVL(:prm_policy_no,pdue_policy_no)) > 0

if no then please provide me some solution, since i dont to use following statements in my query

AND (:prm_policy_no IS NULL
OR Contains (pdue_policy_no,:prm_policy_no) > 0)

Thanks in advance for your guidance

[Updated on: Thu, 02 October 2008 05:43]

Report message to a moderator

Re: Tune with context index [message #351696 is a reply to message #351670]

Thu, 02 October 2008 09:17

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Quote:

Can we use NVL with CONTAINS like as shown below

Where Contains (pdue_policy_no,
NVL(:prm_policy_no,pdue_policy_no)) > 0

No, obviously not, since as you already know it produces an error.

Quote:

if no then please provide me some solution, since i dont to use following statements in my query

AND (:prm_policy_no IS NULL
OR Contains (pdue_policy_no,:prm_policy_no) > 0)

That is a solution and there is nothing wrong or inefficient about it, if you actually need a query using contains. Any performance problems may be due to other things, such as fragmented index or a contains query not being the best method for what you want.

WITHOUT KNOWING WHAT RESULTS YOU WANT, THERE IS NO WAY TO DETERMINE THE BEST SOLUTION TO YOUR PROBLEM. THE BEST SOLUTION MIGHT NOT EVEN INVOLVE USES CONTAINS.

Why do you keep failing to provide what has been asked for and repeating the same questions that have already been answered and asking for solutions that have already been provided? Are you not reading what I am writing or do you not understand English well enough to understand it or what?

Report message to a moderator

Previous Topic:	Near operator with Ctxcat index (split from CTXCAT / CONTEXT index by bb)
Next Topic:	select query optimization

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 13:18:27 CST 2025