using the new Index in queries

Posted on 2015-02-14
Last Modified: 2015-03-10
I created an index and the index is used in the query when I run it from the sqlplus. The application uses the same query (shows bind variable in the AWR report) but the index is not used.
The same sql runs better when the query uses  this new index(from sqlplus it is taking  this index by itself) . How can I force this  query to use this index when run from the application ?
Question by:Oranew
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 48

Assisted Solution

PortletPaul earned 100 total points
ID: 40610636
>>"(shows bind variable in the AWR report)"

bind variable -v-  values
You cannot tune a query with values and expect the same result with bind variables.  It doesn't work that way.  With a value, the optimizer can make assumptions.  Without a value (i.e. with a bind variable) it cannot make assumptions about the value.
johnsone 2015-02-13

You could try a hint to force use of that index... but that index might not suit all bind variable values (or combination of values)
LVL 35

Expert Comment

ID: 40610756
Besides needing to tune the query with bind variables as discussed in the other question.  Once the index is created, you should update statistics on the table.

Author Comment

ID: 40612185
The issue is when I am trying to tune the query without bind variables it is not running. it is asking me for the bind values in the sqlplus. What is the way to tune the query without passing the bind variables ?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 35

Expert Comment

ID: 40612313
I posted an example of how to do exactly that in your other question.  The one referenced by PortletPaul.

Author Comment

ID: 40615680
Not sure what is the issue but the index is only used when I am running the sql using values.(not bind variables). The explain plan is different when using bind variable and the plan with the new indexes is way better and also the sql actually runs from the application and it uses bind. The stats ran on all the tables but still the new index is not used
LVL 35

Expert Comment

ID: 40616449
The difference is that the optimizer knows the value it is looking for when it generates the plan.  Knowing the value helps with histograms and a better prediction of which plan would be "best".

Author Comment

ID: 40616465
Thanks, then how can I tell the optimizer to take the plan B which is a better plan.
Do you suggest to save this plan and flush the old saved plan?
I am talking about sqlplanbaseline.
LVL 35

Accepted Solution

johnsone earned 400 total points
ID: 40616514
I would put a hint on the query, use a stored outline or look at changing session parameters that relate to the optimizer (specifically OPTIMIZER_MODE).  If the optimizer isn't going to find the right plan, then you have to use one of the methods available to help it along.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
join actual table rows based on the column 25 43
Help with Oracle IF statment 5 47
Using Sum with Case When within a query 9 47
Oracle SQL-Queries on a RAC Database 3 44
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question