Simple Views on tables
Ravi B, April 13, 2016 - 2:47 am UTC
Thanks for your reply.
Do you have any recommendations how to generate ERD on views? Simple or Complex?
Thanks!
April 13, 2016 - 7:30 am UTC
I would generate ERD against the base tables. If you are doing a 1-to-1 simple view mapping, then the two would be interchangable, ie,
schema1 has tables called:
A
B
C
schema2 has views called:
A
B
C
each being "select * from schema.A" etc
Martin Rose, April 19, 2016 - 2:01 pm UTC
Connor, rather than create simple views on top of tables, just use private synonyms. This then allows a layer of re-direction if needed.
April 19, 2016 - 5:00 pm UTC
Views are a layer of re-direction too?
And they offer more power than a synonym. e.g. the ability to rename columns, change the column order, etc. without physically changing the table.
Chris
Martin Rose, April 20, 2016 - 3:18 pm UTC
> Views are a layer of re-direction too?
Well yes, they are.
> And they offer more power than a synonym. e.g. the ability to rename columns, change the column order, etc. without physically changing the table.
No, they offer less power. Synonyms can point to entirely different objects (views/tables/MVs). It's a bit OTT to add (simple) views to every table in a schema, but not so to add private synonyms.
(The private synonyms idea was actually an original Oracle Corp. idea from way back around V6).
April 20, 2016 - 4:28 pm UTC
"Synonyms can point to entirely different objects (views/tables/MVs)"
Views can point to these too? Though a simple view on a view is a bit ... daft.
When it comes to redirecting tables/MVs they do everything a private synonym does and more. Say table A in schema A has some sensitive columns. Privileged user B should see them, but not general user C. Just exclude the columns in user C's definition of the view.
You could also split a table in two and change the view to a join. Then create instead-of triggers on the view and the whole change is invisible to the app.
Obviously you can't have views on packages, etc. but that's a different to what the OP asked.
Chris
Already answered, many years ago
jOHN, April 20, 2016 - 10:13 pm UTC
I do believe that the illustrious Mr Kyte has already dealt with this one. When asked about how many indexes should be used, the response was along the lines of "have as many as you need and no more".
QFT
I suggest the same principle applies here.
April 21, 2016 - 9:32 am UTC
When it comes to simple views on tables, I'd say an all-or-nothing approach is better. Consistency makes the database & app easier to manage and develop against.
If some of the code uses simple views and some base tables then it's confusing.
Chris
A reader, April 21, 2016 - 11:35 am UTC
Chris, I'm not saying we can't have a view under the private synonym which then does all the jiggery pokery you're talking about. I'm merely the first layer of re-direction should always be a private synonym.
Synonym -> View -> Table(s)
April 22, 2016 - 2:00 pm UTC
In that case, I'd probably opt for a login trigger that does:
alter session set current_schema = ...
and point to the schema the contains the views. That way I've no synonyms to worry about.
Cheers,
Connor
Martin Rose, April 21, 2016 - 12:44 pm UTC
On a slight tangent, (just meaning to be complete, not irritating), INSTEAD OF triggers turn DMLs on any view into a row-by-row operation (Ok, not the slow-by-slow context switching row-by-row we all 'love', but row-by-row within SQL nonetheless).
If Oracle Corp. could augment this functionality to operate batch-wise, this mechanism would be better/more viable. Furthermore, there's no AFTER STATEMENT triggers which could help with this.
[Just something I've noticed in the past, and thought this would be a good opportunity to bring it up].
- Martin
April 22, 2016 - 2:22 pm UTC
I'd encourage you to log an enhancement request.
Martin Rose, April 25, 2016 - 1:13 pm UTC
One last follow-up. If you don't use private synonyms in the way I've suggested, what use is there in Oracle having them at all. Isn't that what they were initially intended for? ;-)
April 27, 2016 - 4:20 am UTC
Synonyms still have their place.
Synonyms dont *just* have to be for tables.
And similarly, the "set current schema" technique I mentioned was to address the initial post from the question, namely, remapping *all* the tables in the schema.
Cheers,
Connor