Oracle View limitations

Question

I am converting sybase stored procedures into oracle views. This is what they want and this is not my first choice. My question is: Are there any limitations in oracle views? Total number of columns? Can you create indexes for views? Since I will be creating subviews and joining views within views, are there any limitations on how many layers of views you can do? Thanks

Answer 1

Are there any limitations in oracle views? Total number of columns? are there any limitations on how many layers of views you can do

All covered here: http://docs.oracle.com/database/121/REFRN/GUID-685230CF-63F5-4C5A-B8B0-037C566BDA76.htm#REFRN0043

The number of columns in a view has the same limits as the number of columns in a table.

Can you create indexes for views?

No you cannot. However you cate create a materialized view, that can be indexed

Answer 2

There aren't any significant limitations on the complexity of views.

The Oracle cost-based optimizer does a decent (if inscrutable) job of figuring out how to carry out queries to views using the indexes of the underlying tables.

If that performance isn't enough, you might consider looking into materialized views.

The usual way of doing the kind of project you're doing is to get the views working, then use EXPLAIN PLAN to do the optimization.

Answer 3

You're limited to 1000 columns in a table. It wouldn't shock me if there was a similar limit for views. But if you're creating a view with 1000 columns, you're probably doing something very wrong.

You cannot create indexes on views but you can create indexes on the underlying tables that queries against views can use. You can index materialized views since, as the name implies, they materialize the data into a separate structure. But then you have to deal with refreshing the materialized view on commit, which adds overhead to transactions, or tolerate stale data and refresh the materialized view on some schedule.

There is no limit to the number of layers of views you can have. Depending on the Oracle version, the complexity of the views, and things like the presence of constraints, you can end up with queries that either force Oracle to do extra work (i.e. joining in additional tables in view layers that your end query doesn't actually need) or that are too complex for the optimizer to find a decent plan.