Ask the Expert

Speeding up data retrieval from a view without index on base table

I understand that views cannot be indexed. Can you suggest a way to speed up data retrieval from a view without having to create an index in the base table? OLTP constraints prevent me from creating an index on the base table.

    Requires Free Membership to View

If you can't create indexes against the base table you're likely going to find this tough going. All you can do is to write the query on which the view is based as efficiently as possible. Make sure that if you run the query by itself, its execution plan is as optimized as possible. Use EXPLAIN PLAN or AUTOTRACE to review how the query will execute and then make sure that you're using the most efficient query format.

Outside of that, you might could consider a materialized view if you don't have restrictions on disk space. A materialized view is simply a snapshot (materialized view is the new term for snapshot). A brand new table is created containing the data that satisfies the query. The m-view can be created to automatically refresh as new data is added to the base table(s) and you can also index the m-view. This method keeps you away from your base tables and gives you a new table that can be consistently refreshed from data in base tables as they are updated.

For More Information

This was first published in April 2003

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: