Creating view & performance in the database.,

IF a create thousands of view, Does it hamper the database performance. I mean is there any problem with creating thousands of view in开发者_如何学编程 oracle. Please explain as I am new in this area...I am using oracle...

The simple existence of these views shouldn't harm performance at all. However, once those views start being used it's possible that there will be some negative performance impact. Oracle tries to "remember" the plan for each statement that it sees, but it compares statements by comparing the source code (the SQL). Your thousands of views will all be named differently since you can't have multiple views with the same name, and thus each time one of them is used Oracle is going to have to do a full parse of the SQL, even if it's something as basic as

SELECT * FROM VIEW_1;

and

SELECT * FROM VIEW_2;

All these re-parses will certainly take some time.

What's different about each of these views? I think it might be a good idea to step back and consider other possibilities. Questions I'd ask include

1. What is to be accomplished here?
2. Why are thousands of different views needed?
3. Is there some other way to accomplish what needs to be done without creating all these views?

I don't know the answers to 1 and 2, but I'm reasonably sure that the answer to #3 is "Yes".

Good luck.

Oracle views are an encapsulation of a complex query and must be used with care. Here are the key facts to remember:

Views are not intended to improve SQL performance. When you need to encapsulate SQL, you should place it inside a stored procedure rather than use a view. Views hide the complexity of the underlying query, making it easier for inexperienced programmers and end users to formulate queries. Views can be used to tune queries with hints, provided that the view is always used in the proper context.

source: Guard against performance issues when using Oracle hints and views

View is as heavy to run as the select that creates it but Oracle loads balance and single select can't harm the DB. If you have thousands concurrent selects going then you might have a problem. The amount of views is not important but how heavy they are and how much you use them.

You would actually need to show the views code and tell what you are actually trying to do.

View should not affect performance, if optimizer is smart enough. I remember cases with other DB engines when Views do harm performance. As in many performance cases - I suggest to measure your particular case.