Re: View Mechanics

From: Greg Rahn <greg_at_structureddata.org>
Date: Fri, 2 Sep 2011 11:41:05 -0700
Message-ID: <CAGXkmisEOg5avLMJoX4Hfx1vPiG4N_z8dSBXE0=7YBiJ+4tZzg_at_mail.gmail.com>



The best way to triage this is to get 10053 traces for both and diff them in a visual diff tool to see where things divert. If you are keen with query planning choices it may be visible from looking at the full set of plan directives using dbms_xplan with advanced/+outline format options.
dbms_xplan.display_cursor(format=>'advanced') or dbms_xplan.display_cursor(format=>'+outline')

Generally speaking
  select * from my_view where c1 = 'foo' would be transformed into the same statement as   select * from (my_view_text) my_view where c1 = 'foo'

On Fri, Sep 2, 2011 at 8:37 AM, Lange, Kevin G <kevin.lange_at_ppoone.com> wrote:
> What we have seen is this:
>
>  In the application, running a select from the view in the form of
>
>      Select * from app_view where field_a = '123';
>
>  Run a lot slower than running the EXACT code that makes up the view
> adding the line
>
>      and field_a = '123'
>
> In the underlying tables field_a is an indexed column.  And the columns
> that join Table_1 to Table_2 to Table_3 are all indexed.   Therefore I
> would expect the view to use the same path the sql behind the view when
> selecting from it.
>
> What we saw when explaining the select from the view is that all three
> tables are doing Full Table Scans.  When running the sql outside the
> view, the appropriate indexes are being used.
>
> I just thought that, all things equal, the view would use the same
> methods to get the data that the SQl behind the view did.  I had not
> seen this drastic a difference before.

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Sep 02 2011 - 13:41:05 CDT

Original text of this message