Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    tables and views

    Hi all:

    i have developed a basic schema for my database, but it seems there is a lot of subtype tables (child table inheriting columns of the parent tables), and i feel it's a bit difficult for programmers to query since it will take a pretty complex SELECT statements sometimes. So I am thinking about creating a set of views for programmers to query typical data. For insert/update issues i will think of something else .

    My Question is:
    is there a significant difference in performance by creating a dynamic view (with check option) as opposed to writing a SELECT statement to query multiple tables each time? or would it affect the performance of the database, since each insert/update will now affect more tables/views?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: tables and views

    There's no reason selecting from the view should be less efficient than selecting from the base tables directly - a view is just a stored query.
    Inserting into 2 or 3 tables will take longer than inserting into 1, but is unlikely to be a cause for concern unless your system inserts data at a phenomenal rate, like 1000s of rows per second.

    I would recommend the approach you are taking. Depending on your DBMS, inserting and updating against your views may be possible also - possibly requiring "INSTEAD OF" triggers to translate your DML into equivalent statements against the base tables.

  3. #3
    Join Date
    Nov 2002
    Posts
    98
    I have another problem relating to views:

    say i have a stats table with millions of records, and we usually sort out the stats by month. everytime we query it it might take 5 minutes. Not considering building a better index (which is what i think one very valid solution), if I create a view with check option, would the time it takes for dbms to sync with my view longer, shorter, or the same than trying to use the same SELECT statement each time?

    Mark

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by mchih
    I have another problem relating to views:

    say i have a stats table with millions of records, and we usually sort out the stats by month. everytime we query it it might take 5 minutes. Not considering building a better index (which is what i think one very valid solution), if I create a view with check option, would the time it takes for dbms to sync with my view longer, shorter, or the same than trying to use the same SELECT statement each time?

    Mark
    "With check option" just affects inserts and updates - e.g. it ensures that you do not insert a row into a view that you could not select back from it. It has no impact on the performance of SELECT statements. There is no reason why a view should be quicker or slower than the equivalent SELECT on the base tables - they are both doing the same thing under the covers. The DBMS doesn't have to "sync with" your view - a view is nothing more than a canned SELECT statement. The DBMS translates your "SELECT FROM view" into "SELECT FROM tables WHERE..." based on the view definition, that's all.

  5. #5
    Join Date
    Nov 2002
    Posts
    98
    i was under the impression that "with check option" means views will be dynamically updated once the tables, from which the views are derived, are inserted or updated.

    so what you mean is that for each update or insert on the tables, it means the same as running the select statement again?

    Mark

  6. #6
    Join Date
    Nov 2002
    Posts
    98
    maybe i wasn't being clear.

    say i create a view on a table that has 5mil rows. and it takes me 10 minutes to do so. so now i have a view called view1. By having "with check option" clause in view1, it ensures that everytime the table is updated or inserted, the change will reflect on the view right away, but my question is: would it take 10 minutes each time for the change to be shown in views1?

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    No, you are totally misunderstanding what a view is!!! A view is NOT a place where data is stored, it is JUST a named query. It will never take 10 minutes to create a view, it will be about 1 second (+- 1 second). It doesn't matter whether you base the view on 1 table with 1 row, or a join of 12 tables with 100 million rows each, to create the view will take about 1 second. No data is moved or copied in the process of creating a view.

    When you SELECT from a view, the DBMS translates your SELECT statement into the equivalent statement against the base tables, and runs the translated query. Typically, the SELECT against the view will run just as fast or slow as the equivalent query against the base tables.

    "with check option" has NOTHING to do with ensuring that your view sees changes to the base tables - a view is GUARANTEED to see any changes to the base tables whether you add "with check option" or not - because a view is just a query. "With check option" is a constraint on inserts and updates of views, to ensure that you can't create data via a view that you cannot query via the view.

    OK, maybe an example will help:

    CREATE TABLE dept( deptno NUMBER PRIMARY KEY, dname VARCHAR2(30) NOT NULL, loc VARCHAR2(30) NOT NULL );
    CREATE TABLE emp( empno NUMBER PRIMARY KEY, ename VARCHAR2(30) NOT NULL, deptno NUMBER NOT NULL FOREIGN KEY REFERENCES (dept) );

    INSERT INTO dept ... /* Insert 5000 rows into dept */

    INSERT INTO emp ... /* Insert 1,000,000 rows into emp */

    CREATE VIEW london_emp AS
    SELECT emp.empno, emp.ename, emp.deptno, dept.dname, dept.loc
    FROM emp, dept
    WHERE emp.deptno = dept.deptno
    AND dept.loc = 'LONDON'
    WITH CHECK OPTION;

    -- The CREATE VIEW will take about 1 second, regardless of how many of the 1,000,000 employees are based in London. The view is not "filled" with employees, the only information that gets stored in the database is the view DEFINITION, i.e. the SELECT statement.

    SELECT * FROM london_emp;

    ... (lots of output)

    58,321 rows selected in 30 seconds (or whatever).

    SELECT emp.empno, emp.ename, emp.deptno, dept.dname, dept.loc
    FROM emp, dept
    WHERE emp.deptno = dept.deptno
    AND dept.loc = 'LONDON';

    ... (lots of output)

    58,321 rows selected in 30 seconds (or whatever).

    -- The select from the view and the equivalent select from the tables take the same time to run (more or less). That's because when you typed "SELECT * FROM london_emp", the DBMS looked up the definition of london_emp and transalated your query into something like:

    SELECT * FROM
    (SELECT emp.empno, emp.ename, emp.deptno, dept.dname, dept.loc
    FROM emp, dept
    WHERE emp.deptno = dept.deptno
    AND dept.loc = 'LONDON'
    );

    -- The check option comes into effect if we try to insert or update, e.g.

    INSERT INTO dept ( deptno, dname, loc ) VALUES (999, 'HR', 'PARIS' );

    INSERT INTO london_emp( empno, ename, deptno, dname, loc )
    VALUES ( 123, 'Gelbart', 999, 'HR', 'PARIS' );

    ERROR: view check option violated

    -- Since the view can only SELECT employees based in London, you can't insert a Paris employee via the view (if it has "with check option").

    I hope this helps - I don't know how to write it any more clearly!

  8. #8
    Join Date
    Nov 2002
    Posts
    98

    Thumbs up

    couldn't be any more clearer. Thanks for the help

    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •