Re: Views and Performance considerations

Stephen Hurrell wrote:
>
> 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
>
>
> Name: hurrells.vcf
> Part 1.2 Type: text/x-vcard
> Encoding: 7bit
> Description: Card for Stephen Hurrell

Certainly ranged partitions are designed for rolling date ranges...

Also note that "trunc(sysdate,'YYYY')" gives the start of the current year (which will make your view defn simpler)

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse