Oracle may or may not pursue a full table scan with an "OR." However, you
can could use a function based index differently to force the issue.

Try a function based index using SIGN(NVL(PRD_POLICY_ID,0)), or if you are
using 9i or higher, NVL2(prd_policy_id,1,0), and change your query into a
UNIONALL with:


QUERY1....
PRD_POLICY_ID=:pPopPolicyId
....
UNIONALL
QUERY2....
NVL2(prd_policy_id,1,0) = 0
...

You must update statistics or this index will be ignored. This should get
around the full table scan provided prd_policy_id is part of a larger index.
The question is whether the performance gain is worth the other overhead.

Good Luck!
Chris

----------------------------------------------------
Chris Weiss, Blueoak Database Engineering, LLC
http://www.blueoakdb.com
PH: 517-381-9305
CELL: 517-974-5634
eFAX: 801-340-0740


-----Original Message-----
From: Justin Cave
[mailto:oracledba-ezmlmshield-x22827658.[Email address protected]
Sent: Monday, September 13, 2004 5:56 AM
To: LazyDBA Discussion
Subject: RE: index creation


It doesn't appear that a function-based index could help you here, since the
column in the table is unmodified. You can't have a function-based index
based on a function that takes arguments from the front-end, since Oracle
would have no way to build the index. Perhaps you could change the query,
though

PRD_POLICY_ID = :pPopPolicyId OR :pPopPolicyId IS NULL

Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com

-----Original Message-----
From: j [mailto:oracledba-ezmlmshield-x73243238.[Email address protected]
Sent: Monday, September 13, 2004 3:46 AM
To: LazyDBA Discussion
Subject: index creation

Dear All,

Use of NVL in the following statement is causing full table scan and
suppressing the index.

Can you please tell me how to create the function based index on the same.

PRD_POLICY_ID = NVL(:pPopPolicyId, PRD_POLICY_ID)

### pPopPolicyId is the front-end field and PRD_POLICY_ID is the column of
the table.

This means that if the front-end field is kept null then it will take the
value from the table. The table column is however not null.

Jay
DISCLAIMER: The information contained in this message is intended only and
solely for the addressed individual or entity indicated in this message and
for the exclusive use of the said addressed individual or entity indicated
in this message (or responsible for delivery of the message to such person)
and may contain legally privileged and confidential information belonging to
Tata Consultancy Services Limited. It must not be printed, read, copied,
disclosed, forwarded, distributed or used (in whatsoever manner) by any
person other than the addressee. Unauthorized use, disclosure or copying is
strictly prohibited and may constitute unlawful act and can possibly attract
legal action, civil and/or criminal.
The contents of this message need not necessarily reflect or endorse the
views of Tata Consultancy Services Limited on any subject matter. Any action
taken or omitted to be taken based on this message is entirely at your risk
and neither the originator of this message nor Tata Consultancy Services
Limited takes any responsibility or liability towards the same. Opinions,
conclusions and any other information contained in this message that do not
relate to the official business of Tata Consultancy Services Limited shall
be understood as neither given nor endorsed by Tata Consultancy Services
Limited or any affiliate of Tata Consultancy Services Limited. If you have
received this message in error, you should destroy this message and may
please notify the sender by e-mail. Thank you.



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe: see
http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree to
these terms:http://www.lazydba.com/legal.html








--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page