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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in April 2003