(if that email address didn't require changing)
>Thank you very much for your detail explanation. I still have some
>: A simple view, as I describe above "select c1, c2, c3 from T" will not affect
>: performance (except perhaps during the initial parse phase of the query but that
>: will be neglible).
>: the 'problem' people sometimes see with views is that they create join or
>: aggregate views. Then, they create join or aggregate views on top of these and
>: so on... Then, people take 2 or 3 of these views at runtime and query them
>: together. the optimizer has a hard time merging these views together and
>: pushing predicates down. A 'hand written' query with no views in this case will
>: probably be more performant.
>: It can be hard to tune systems with lots of complex underlying views and queries
>: against these views. I'm not suggesting that however, far from it.
>If I have a query with a lot of joints and it may be called by a group of
>people e.g. developers for billing related process, will it be benefit to
>put it into a view?
Lets think about it this way -- you use the views DBA_*, USER_*, ALL_* all of
the time to query the data dictionary. Just for grins, why do you:
SQL> set long 5000
SQL> select text from all_views where view_name = 'ALL_OBJECTS';
that view is pretty large and does lots of stuff (all_objects not only presents
you with data but enforces a security policy -- you can only see objects that
you either OWN or you have privelege to see via a grant or a role)....
Was it of benefit to put that in a view? YES, in this case it was. It serves
many purposes. First and firemost, it answers a frequently asked question (what
objects are they and what are their types). It does this in a way that doesn't
make everyone learn the entire Oracle schema and join tables themselves (tedious
and error prone). Secondly, it 'optimizes' the display of data by turning
internal codes into meaningful names. We use decode to do that. So, it can
reformat data for display as well as make it easy to get the data to display.
Lastly, it enforces a pretty strict security policy -- you see what you are
allowed to see and nothing more.
So, the entire data dictionary is nothing more then a big set of views (and some
extremely complex ones at that). The nice thing is that as you upgrade the
oracle versions, you get the same set of views over time -- even though the base
tables might change (their column names might change, their size and shape and
values change over time).
Long story short: Views are a tool just like stored procedures are and triggers
are. You can and should use them were you deem appropriate. There will be
times when a view will 'let you down' since what you want to do with it won't
perform very well -- thats when the developers will tune that one query. Rather
then have a blanket rule that says "No views", have a rule that says "use the
views unless they don't perform, then we will look at it". I've written my own
views against the base tables in the data dictionary to do things they can't or
don't (or just perform too slowly). I think twice before doing so cause every
time you do that, you add another piece that might break when you upgrade --
they same would be true of your application -- every time you query against the
base tables, you have linked your application to that schema, if you feel the
need to change the schema, you have to fix the application. Not too hard if you
do everything in pl/sql as the dependency mechanism makes it easy to find all of
the broken code, but if you have a large app where some of the sql lives in the
application, it can get harder.
>: In your case, your main problem is the grants. Using simple:
>: SQL> create or replace view my_view as select * from t;
>: with no joins, no aggregates, etc should not have any impact on the query plans
>: (i've never seen any with simple views).
>Yes, this one very clear. I got it.
>Once again, tahnk you veyr much for your time.
Oracle Service Industries
Reston, VA USA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you. Any bounced
email will be treated the same way i treat SPAM-- I delete it.