| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Getting Role and Permissions from DB
A copy of this was sent to suisum_at_ecn.ab.ca ()
(if that email address didn't require changing)
On 3 Jan 99 18:19:30 GMT, you wrote:
>Hi Thomas:
>
>Please explain further. I have the following questions.
>
>Thomas Kyte (tkyte_at_us.oracle.com) wrote:
>
>: - use "create or replace" as opposed to drop/create whenever possible
>
>: - use "views" instead of tables as the thing you grant on. Don't grant on
>: tables, don't access table. Views can be 'created or replaced', tables cannot.
>: You might also find you have to drop columns/rebuild tables much less frequently
>: (you can save them up) as you can simply default the column value in the real
>: table, remove the column from the view and the apps will be none the wiser.
>: Same with adding columns and so on (these can be altered into the table and
>: added to the right views).
>
>Some people don't like to use view because they said that it could be very
>slow. In my previous job, the project manager didn't allow people to
>create view. But some people are in favour of using views. They created
>views almost on everything.
>
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.
>So, I'm very confused. Would you mind to guide us how to use view
>effectively, please?
>
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).
>Best regards,
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
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 - 17:04:32 CST
 |
 |