Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Getting Role and Permissions from DB

Re: Getting Role and Permissions from DB

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 04 Jan 1999 01:40:00 GMT
Message-ID: <3698197f.15695999@192.86.155.100>


A copy of this was sent to suisum_at_ecn.ab.ca () (if that email address didn't require changing) On 4 Jan 99 00:16:14 GMT, you wrote:

>Hi Thomas:
>
>Thank you very much for your detail explanation. I still have some
>questions.
>
>Thomas Kyte (tkyte_at_us.oracle.com) wrote:
>
>: 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.
>
>Best regards,
 

Thomas Kyte
tkyte_at_us.oracle.com
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. Received on Sun Jan 03 1999 - 19:40:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US