RE: View Mechanics
- From: "Powell, Mark" <mark.powell2@xxxxxx>
- To: Oracle-L List <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 2 Sep 2011 13:47:58 +0100
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
