Ask the Expert

Using index to avoid full table scan

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?

    Requires Free Membership to View

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.

This was first published in February 2007

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: