RE: View Mechanics

I would speculate that the predicates are not be pushed into the view?

Michael Dinh

Disparity Breaks Automation (DBA)
 
NOTICE OF CONFIDENTIALITY - This material is intended for the use of the 
individual or entity to which it is addressed, and may contain information that 
is privileged, confidential and exempt from disclosure under applicable laws.  
BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION 
(PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS 
FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN, 
EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR 
OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE.  If the reader of this 
email (and attachments) is not the intended recipient, you are hereby notified 
that any dissemination, distribution or copying of this communication is 
strictly prohibited. Please notify the sender of the error and delete the 
e-mail you received. Thank you.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Lange, Kevin G
Sent: Friday, September 02, 2011 8:38 AM
To: Oracle-L List
Subject: RE: View Mechanics

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.   

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Powell, Mark
Sent: Friday, September 02, 2011 7:48 AM
To: Oracle-L List
Subject: RE: View Mechanics


 
I am not sure I understand your wording but it is not uncommon to create
a view whose SQL query would be a multiple table join of all rows and if
you run select * from the view the performance would be pretty bad.
However, when used in the application or by the end-user tool a where
clause using an indexed column is provided in the query against the view
which in turn when merged into the view code by the CBO results in a
much more selective plan/performance.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Lange, Kevin G
Sent: Thursday, September 01, 2011 6:12 PM
To: Oracle-L List
Subject: View Mechanics

I am just curious if its common for an Explain Plan of a view to have a
totally different response than an explain plan of the SQL behind the
view.
 
Reason being, we have a very simple view made of of 3 tables that, when
you explain it, it shows full table scans on all three tables.  But, in
the mean time, if you explain plan the SQL behind the view, you get
index range scans on all three tables. 
 
Just makes no sense to me (which seems to be happneing more and more
these days).
 
System: Oracle 10.2.0.4 on a Solaris 10 machine.
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.

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l



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.

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


Other related posts: