13 Replies Latest reply: Jan 25, 2013 10:16 AM by Billy~Verreynne RSS

    Creating Views for all tables

    ngac_uk
      Hi,

      We are looking at creating a view for every table in the schema. We have about 300 tables. Will this impact on performance at all?

      Also is there a way to create a view based on the tables data dictonary?
        • 1. Re: Creating Views for all tables
          Paul  Horth
          Why???
          • 2. Re: Creating Views for all tables
            ngac_uk
            The client wants a certain type or oracle license which prevents access to the tables.

            As a quick solution which satisfies oracle a one to one on the tables will occur before eventually creating more user friendly views consolodated
            • 3. Re: Creating Views for all tables
              Paul  Horth
              ngac_uk wrote:
              The client wants a certain type or oracle license which prevents access to the tables.

              As a quick solution which satisfies oracle a one to one on the tables will occur before eventually creating more user friendly views consolodated
              If it's the license I think it is, then it means NO access to the tables: including views.

              It means you provide a "packaged" solution using code you have provided to run the application. The user only sees a "front-end" to his data.
              The user is not allowed to run ad-hoc queries on the tables (and that includes views).
              • 4. Re: Creating Views for all tables
                Niket Kumar
                view will never affect the performance as they are also bringing data from tables..... view doesn't have there own data....
                • 5. Re: Creating Views for all tables
                  Manik
                  Do you mean this way?
                  SELECT    'create or replace view V_'
                         || object_name
                         || ' as select * from '
                         || object_name
                         || ';'
                            execstmt
                    FROM user_objects
                   WHERE object_type = 'TABLE';
                  Cheers,
                  Manik.
                  • 6. Re: Creating Views for all tables
                    Billy~Verreynne
                    ngac_uk wrote:
                    The client wants a certain type or oracle license which prevents access to the tables.

                    As a quick solution which satisfies oracle a one to one on the tables will occur before eventually creating more user friendly views consolodated
                    There are no such loopholes in Oracle licensing - so that client will be violating his/your Oracle license.
                    • 7. Re: Creating Views for all tables
                      BluShadow
                      ngac_uk wrote:
                      The client wants a certain type or oracle license which prevents access to the tables.

                      As a quick solution which satisfies oracle a one to one on the tables will occur before eventually creating more user friendly views consolodated
                      As others have pointed out, creating views won't get around such a licence restriction.
                      A view is a stored SQL statement, so if a user queries a view, the SQL of that view will be combined with their query and it's effectively the same as the user querying the tables directly themselves.

                      Materialized views on the other hand would be considered a "copy" of the data, but then there's a whole load of other things that would need doing to keep the data in those tables up to date if required. That may still breach the licence agreement though, so you're better of clarifying with your Oracle representative directly
                      • 8. Re: Creating Views for all tables
                        ngac_uk
                        We have confirmed this is ok with the account manager.

                        The customer has inside thier APRF a proviso where they can query the data for custom reporting purposes via odbc for crystal reports.

                        The reports are generated by the front end system but are built in the crystal builder, the customer was failing the license as they were linking via tables but the way the account manager has told us to go is to only allow access to the views.
                        • 9. Re: Creating Views for all tables
                          Billy~Verreynne
                          Sorry, I think this is bs.

                          A view is SQL. So how can this be allowed?
                          select * from( /*the view*/ select * from emp )
                          And this not allowed?
                          select * from emp
                          It smells.
                          • 10. Re: Creating Views for all tables
                            ngac_uk
                            I will speak to the account manager again. I appreciate all your input.

                            I think it relates to us giving the user pre defined views they can use in the reports. Rather than them uaing sql direct.

                            So for example we create a view of all the invoicing information. They will then use this view to drop the columns on their report rather than linking the tables themselves and seeing this data structure.
                            • 11. Re: Creating Views for all tables
                              riedelme
                              Licensing aside (back to the original questions) ...
                              We are looking at creating a view for every table in the schema. We have about 300 tables. Will this impact on performance at all?
                              Creating views for every table has disadvantages.

                              First you have more objects to keep track of. If you must create views identify them as such with a "_VW" or some other identifier indicating that the object is itself a view. Views and tables are so similar it is easy to confuse them and waste time looking for tables that do not exist, plus having to figure out what the view itself is doing

                              If you create views carefully there should not be a performance issue. However, if you take the view creation too far and start creating views of views and joining views of views to views of views there might be performance issues. I have worked on several systems where views of views of views joined to views of views of views all performing the very same security subqueries redundantly did affect performance. If you must use views keep everything as simple as you possibly can. Avoid creating views with table joins and subqueries.

                              The view issue is a bit undecided. Several OTN posters have stated there is no problem with unrestricted view usage. My own experience is different. Your mileage may vary. You can search OTN to get other input on this subject.

                              If creating the views violates your license then the views are not a viable option anyway.
                              Also is there a way to create a view based on the tables data dictionary?
                              You can probably overlay the data dictionary views with your own views but unless I had a really good reason to do so I would not try it for the reasons I already mentioned. What happens when you try?
                              • 12. Re: Creating Views for all tables
                                Billy~Verreynne
                                ngac_uk wrote:
                                I will speak to the account manager again.
                                Make sure you get an answer, signed, in black-and-white - as technically, the "+allowance to use views and not tables+" does not make any sense.

                                Typically allowance are made by ring fencing and providing reduced functionality. This is not the case with allowing SQL against views and not tables. Views actually provide increased functionality.

                                Also, Oracle (and most other db s/w products) has per-user licensing and per-CPU licensing.

                                The allowance to use views - does that translate to how many user licenses, or how many CPU licenses?

                                Make sure that the allowance you get ito additional/extra licensing is clearly documented too.

                                 
                                PS. Was in pre-sales and product management in my career - and one thing I have learned is to never trust a salesman. ;-)
                                • 13. Re: Creating Views for all tables
                                  Billy~Verreynne
                                  riedelme wrote:

                                  Creating views for every table has disadvantages.
                                  Fully agree.

                                  Which is why if views are allowed to be used, then views should be "intelligent business" objects instead of "raw business" data. This will also make writing reports in the reporting/end-user layer a lot easier.