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.

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.

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.

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.

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.

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

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.

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?