Re: Limitation on Views

From: <michael_bialik_at_my-deja.com>
Date: Mon, 02 Aug 1999 21:07:52 GMT
Message-ID: <7o51av$7pa$1@nnrp1.deja.com>

In article <7o4ugc$5mq$1_at_nnrp1.deja.com>,   Nandakumar <nanban_at_my-deja.com> wrote:
>
>
> I have a table with 20 different types of records and i want to
create a
> view for each type. Could anyone clarify if there is any limit on
number
> of views on a table? Are there any performance issues when views are
> more?
>
> Finally, is there any limit on number of views in a data base?
>
> --
> Nandakumar
> Systems Analyst
> New York
> (N.Kumar_at_rocketmail.com)

Hi.
 1. There is NO limit on number of view per table.
 2. There is some performance problems with such views.

    I found in one project "table of tables" :     CREATE TABLE tab_table (

      table_type    VARCHAR2(30),
      code          VARCHAR2(5),
      description   VARCHAR2(50),
      CONSTRAINT tab_table_pk PRIMARY KEY ( table_type, code));

    CREATE VIEW v1 ( code, description )
     SELECT code, description from tab_table where table_type = 'TAB1';

    ...
    CREATE VIEW vn ....

                    ... WHERE table_type = 'TABn';

    These views were joined ALWAYS by code :

    SELECT .., v1.description, ...

     FROM v1, emp_tab
     WHERE v1.code = emp_tab.rank;

   It was a bit more complicated, but you have an idea.

   Oracle optimizer was NOT smart enough to merge conditions of    view and select.
   The result - instead of UNIQUE SCAN on tab_table_pk - RANGE SCAN    was used ( with view condition only ).    Imagine what happens when you have join a table of 1M rows    and for each row use RANGE SCAN of 5-6K rows...

   I tried RBO and CBO ( Oracle 7.3.2 ) but without any success.    Eventually we split tab_table to a number of different tables.

 3. There is NO limit on number of view per database.

    Good luck. Michael.

> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Aug 02 1999 - 16:07:52 CDT