Re: How does Oracle implement views?
Date: Mon, 23 Aug 1993 09:55:53 GMT
Message-ID: <CC7I95.Fo7_at_vistachrome.com>
alacy_at_hayes.com writes:
>Does anyone know what Oracle does under the covers, when a view which uses
>more than one table (ie, join, or union and so on) is accessed? I think Oracle
>creates a temporary table in the temp tablespace, and once the select to
>the view is finished, the temporary table is deleted. Does anyone know if
>this is the case?
>--
>Allen Lacy, Principal Analyst | internet: alacy_at_hayes.com
>(404) 840-9200 (x.2131) | uucp: uunet!hayes!alacy
>Hayes Microcomputer Products | U.S.Mail: p.o.box 105203, Atlanta, GA 30348
If the WHERE clauses of your VIEW or the WHERE clauses of your SELECT statement using the VIEW do not adequately define the search, then yes, the view is based onto a temporary table which is then returned to you as the result of the view.
Unions almost always cause creation of a temporary table (I am hard pressed at o-dark-40 in the morning to think of a case where it does not). JOIN will cause one if not all columns of the select are in one table; some views such as
create view homeless_employees as
select emp.* from emp where emp.dept in (select deptno from dept where mgr=emp.empno);
actually do an internal join (bringing dept up into emp) and an equi-join based on emp.empno and dept.mgr; i don't know what happens then.
Andy
who hates system performance tuning (sun style) when the sun ain't up at
3am.
-- Andrew Finkenstadt | andy_at_{homes.com,vistachrome.com,genie.geis.com} Systems Analyst | Vista-Chrome, Homes & Land Publishing Corporation | 1600 Capital Circle SW, Tallahassee Florida 32310 +1 904-575-0189 | GEnie Postmaster, Unix & Internet RoundTables SysopReceived on Mon Aug 23 1993 - 11:55:53 CEST