Using index to avoid full table scan

My query is taking too long to run. How can I use an index to reduce cost?

Published: 02 Feb 2007

Step 2 of 2:

You forgot to provide an Email Address.

This email address doesn’t appear to be valid.

This email address is already registered. Please login.

You have exceeded the maximum character limit.

Please provide a Corporate E-mail Address.
- I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.
Please check the box if you want to proceed.
- I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.
Please check the box if you want to proceed.

I have checked a query using explain plan and it was going for a full table scan in instance A, and after creating an index it is using the index and the run time is also around 40% improved. When we ran the same query after two weeks with the same index, it used the index but the cost was too high, higher than before, and when I used the same query in another instance (B) after creating the index, the explain plan shows it's going for a full table scan. Could you please let me know how to use the index with less cost?

Normally, I'd recommend to keep your statistics up to date. You may want to regularly recalculate statistics on the table and its index(es). If you still have this problem, then I'd recommend locking your statistics for this table so that even if the data distribution changes, you will get the execution plan you think is optimal. You can use the DBMS_STATS package to do more than recalculate statistics. The SET_TABLE_STATS and SET_INDEX_STATS procedures of this package can be used to manually set the optimizer statistics to the value you want. Once you have them set, you can use the LOCK_*_STATS procedure to lock those statistics down so that further recalculations will not modify their values ( * = SCHEMA or TABLE). It can be useful to lock stats when you have values you know that work for your environment. Read this document for more information on these procedures of the DBMS_STATS package.

Dig Deeper on Oracle database design and architecture

All
News
Get Started
Evaluate
Manage
Problem Solve

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

Meet all of our Oracle Database / Applications experts

View all Oracle Database / Applications questions and answers

SearchDataManagement

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Start the conversation

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.