Q

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

This Content Component encountered an error

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.

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

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

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close