Problem solve Get help with specific problems with your technologies, process and projects.

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.

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

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.