Re: HOW 'VIEW' WORKS

From: Brian P. Mac Lean <brian.maclean_at_teldta.com>
Date: 1996/09/23
Message-ID: <32470BF3.1C2D_at_teldta.com>#1/1

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

You are able to run the explain plan utility on views. This script should aid in this. Replace the "select * from view_name" with your top level "select" statement. If you don't have a plan_table in your user account run $ORACLE_HOME/rdbms/admin/utlxplan.sql to create it. There are many variations on the "select lpad() from plan_table" for the final report. I think you will like this one.

set pagesize 60 linesize 80 pause on feedback off  

delete from plan_table where statement_id = 'query1'; commit;  

explain plan
set statement_id = 'query1'
into plan_table
for select * from view_name;  

spool exp.lst  

list;  

col AA format a60 heading "What, How, Who"
col BB format 999 heading "ID"
col CC format 999 heading "Prnt|ID"
select unique lpad(' ', 2 * level, ' |') || operation || ' ' || options || ' ' || object_name AA,
       id BB, parent_id CC

from plan_table
connect by prior id = parent_id

    and statement_id = 'query1'
  start with id = 0
order by id, parent_id;  

spool off
delete from plan_table where statement_id = 'query1'; commit;
exit;

-- 
                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo

Received on Mon Sep 23 1996 - 00:00:00 CEST