Re: usage of views

Atta ur-Rehman <atta707_at_my-deja.com> wrote in message news:8an86j$49$1_at_nnrp1.deja.com...
> hi ajay,
>
> no, using a view instead of a plain sql statement doesn't have to run
> any slower. in fact, because the query definition of a view is stored
> in a parsed and compiled form, it would run faster 'cas you're saving
> the parsing time here. well, of course, if your view doesn't need to be
> recompiled for any reason, that is.
>
> i look at a view as a 'stored query', a convinient of way of storing
> long, multi-table queries that need to be done pretty frequently. or a
> way of restrictng veiw of a table, or multiple joined tables, either
> horizantolly, vertically or both ways. the execution speed in this case
> takes the secondry importance, if at all.

No! Absolutely wrong!
It's a prevalent misconception that many persons believe that Oracle stores the parsed and compiled execution plan to improve performance.

Oracle stores the "QUERY STATEMENT" (the "PLAIN TEXT") in data dictionary, and additional, the schema information (i.e. column names and datatypes...), and dependency information.

For performance issue, Oracle does NOT, and it ought NOT to store the parsed execution plan of view.
A simply example will show it:

Say, EMP table with only primary key EMPNO, no other indexes, create a view like this:

    create view accounting_emp as select * from emp where deptno=10;

If Oracle stores the parsed and compiled excution plan, it must use full table scan to retrieve data from this view. Then think about two SQLs:

    select * from accounting_emp where empno=7782;     select * from accounting_emp where job='MANAGER';

Oracle can use index access to fulfill the prior SQL, and use full table scan for the posterior SQL. Since Oracle will try to merge the definition of the view, and try to find out a more efficient execution plan. Received on Thu Mar 16 2000 - 00:00:00 CST