Re: Limitation on Views

You have your PK defined the wrong way round.

If you have a 'table of tables', where one column defines the table type, (such as the generic lookup tables in Oracle Applications) then the 'table type' column should not be the first column of the index, and should probably be the last column of the index.

(Actually, that's just following the 'most selective column leads' rule).

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

michael_bialik_at_my-deja.com wrote in message <7o51av$7pa$1_at_nnrp1.deja.com>...

> 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...
Received on Tue Aug 03 1999 - 03:00:11 CDT