| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Views and Performance considerations
Compare EXPLAIN results for direct select and select through view.
HTH. Michael.
In article <38FC743C.116E61B0_at_hotmail.com>,
Stephen Hurrell <hurrells_at_hotmail.com> wrote:
> This is a multi-part message in MIME format.
> --------------B8CB19264D34968E60EFBBE1
> Content-Type: multipart/alternative;
> boundary="------------626CD3C9D5B31AB322531B29"
>
> --------------626CD3C9D5B31AB322531B29
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Hello.
>
> I observe a slight difference in performance between a table and a
view
> (on the same table).
> The table has a primary key of three attributes in order (attr1,
attr2,
> date_attr3). The view restricts
> access to rows entered in the last two years as follows; (hey, works
for
> me at 2am ;-)
>
> create view current_work
> as select * from table1
> where date_attr3 > to_date('01/01/' || to_char(to_char
(SYSDATE,
> 'YYYY') - 2), 'MM/DD/YYYY')
>
> Also note that users will add additional where clauses when selecting
> from the view.
>
> 1. What is the reason for the performance overhead (ie: does the view
> have to be rerun for each select?)
> 2. Is there any way to improve performance of this view above.
> 3. Would partitioned tables be a better approach? Other approaches?
>
> --
> STeve
>
> --------------626CD3C9D5B31AB322531B29
> Content-Type: text/html; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> <!doctype html public "-//w3c//dtd html 4.0 transitional//en">
> <html>
> <tt>Hello.</tt><tt></tt>
> <p><tt>I observe a slight difference in performance between a table
and
> a view (on the same table).</tt>
> <br><tt>The table has a primary key of three attributes in order
(attr1,
> attr2, date_attr3). The view restricts</tt>
> <br><tt>access to rows entered in the last two years as follows; (hey,
> works for me at 2am ;-)</tt><tt></tt>
> <p><tt> create view
current_work</tt>
> <br><tt> as select * from
table1</tt>
> <br><tt> where date_attr3 >
to_date('01/01/'
> || to_char(to_char(SYSDATE, 'YYYY') - 2), 'MM/DD/YYYY')</tt><tt></tt>
> <p><tt>Also note that users will add additional where clauses when
selecting
> from the view.</tt><tt></tt>
> <p><tt>1. What is the reason for the performance overhead (ie: does
the
> view have to be rerun for each select?)</tt>
> <br><tt>2. Is there any way to improve performance of this view
above.</tt>
> <br><tt>3. Would partitioned tables be a better approach? Other
approaches?</tt><tt></tt>
> <p><tt>--</tt>
> <br><tt>STeve</tt>
> <br> </html>
>
> --------------626CD3C9D5B31AB322531B29--
>
> --------------B8CB19264D34968E60EFBBE1
> Content-Type: text/x-vcard; charset=us-ascii;
> name="hurrells.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Stephen Hurrell
> Content-Disposition: attachment;
> filename="hurrells.vcf"
>
> begin:vcard
> n:Hurrell;Stephen
> tel;fax:807-343-7445
> tel;work:807-343-7427
> x-mozilla-html:TRUE
> org:Office of the Registrar General
> adr:;;189 Red River Road;Thunder Bay;Ontario;P7B 6L8;Canada
> version:2.1
> email;internet:hurrells_at_ccr.gov.on.ca
> title:Sr. Technical Coordinator
> x-mozilla-cpt:;23072
> fn:Stephen Hurrell
> end:vcard
>
> --------------B8CB19264D34968E60EFBBE1--
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Apr 18 2000 - 00:00:00 CDT
 |
 |