Re: HOW 'VIEW' WORKS

HI,

I don't agree :

optimistaion is different when the view is written completely or used as a view when joining with tables;

Indexes on seperate tables are not taken as one would exspect (V7.2)

-- 
ir Danny Roosens
Genesys Belgium
Hans Forbrich <forbrich_at_tibalt.supernet.ab.ca> wrote:


>Brian P. Mac Lean wrote:

>> 

>> Alex Yau wrote:

>> >

>> > I have a question about exactly how VIEW works in ORACLE.

>> >

>> > I am working a bunch of reports which use a VIEW which is a UNION of

>> > 2 hugh tables(150000 and 100000 rows respectively.)  The report SQL

>> > statement will use this view to join with other tables to select the

>> > columns required.

>> >

>> > I would like to know how the SQL statement will be processed

>> > internally? Will the VIEW be created first(i.e. full table scan,

>> > sort/merge ...etc)  THEN join with other tables OR the create

>> > statement of the VIEW will be combined with the report SQL statement

>> > first then process in one go.

>> >

>> > Thanks,

>> >

>> > Alex

>> 

>

>The way I learned it ... although I may be getting confused

>with some other RDBMSs ...


>

>the view statement is kept semi-parsed.  The optimizer parses

>the requested statement.  It then merges the view parse-tree

>and then optimizes - before the view is processed.

>

>Alex' response about using Explain Plan should verify this.

>

>/Hans