RE: View Mechanics -- Issue Explained
- From: "Lange, Kevin G" <kevin.lange@xxxxxxxxxx>
- To: "Oracle-L List" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 Sep 2011 15:41:28 -0500
Well, we found out what was causing all the issues.
Short explaination of the view:
The data arrives in the form of XML in various files and is loaded from here
into our tables. We process the data and then have to send it back out to the
source in the same format with the same leading or trailing flaws that the data
had when it got here. Therefore, the streams from the XML are stored in an
incoming field that holds the whole record. Various key fields are in other
fields in order to quickly find that record.
This view was created to allow the user to actively join the data from that
large field up to other tables in the application. It substrings out various
portions into separate fields suitable for joins. These substrings were
trimmed of all leading and trailing blanks in order to be able to join up to
the other tables (they are all character fields but the incoming data sometimes
has blanks on the front and end of the string which would cause no join to be
made).
Unfortunately, the developer also put trims on the select of the key fields in
the view. (yes,it was staring me in the face all this time and just actually
'saw' it today). Once the trim was taken off that field, the view joined up
correctly in the queries and all of a sudden we went from 2 minutes on the
select to .09 seconds on the select.
Why did the 'code' work great and not the view ?
Select * from VIEW_NAME where ID_FIELD = '1234';
Select trim(A.ID_FIELD) "ID_FIELD",
trim(SUBSTR(A.DATA_FIELD,1,4)) "CHUNK_ONE",
trim(SUBSTR(A.DATA_FIELD,6,33)) "CHUNK_TWO"
from TABLE_NAME A
Where A.ID_FIELD = '1234';
When selecting ID_FIELD from the view it's the trimmed value. When selecting
it directly from the view code, its pulling from the table field.
Thank you all for your help .
I really should have seen the obvious earlier.
-----Original Message-----
From: Greg Rahn [mailto:greg@xxxxxxxxxxxxxxxxxx]
Sent: Friday, September 02, 2011 1:41 PM
To: Lange, Kevin G
Cc: Oracle-L List
Subject: Re: View Mechanics
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@xxxxxxxxxx> 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
This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » RE: View Mechanics -- Issue Explained - Lange, Kevin G
