Re: Views: How bad are they on performance?

From: Mark Rosenbaum <mjr_at_netcom.com>
Date: 1997/01/28
Message-ID: <mjrE4q6pv.6CF@netcom.com>#1/1

In article <32ED7CC3.5497_at_ix.netcom.com>, Bruce Bristol <bbristol_at_ix.netcom.com> wrote:
>The subject says it all...
>
>We have 16 tables in our database. 8 of which are base tables and 8 of
>which are incremental tables. We have views for each of the 8 base
>tables that link the incremental table to it. Basically a SELECT * from
>'base' and SELECT * from 'incremental'. Both the base & incremental
>layouts are exactly the same.
>
>Our Oracle database is 100Gb large. Every 3 months we do a 'purge' of
>data older than 18 months on our database. This is quite
>time-consuming.
>
>I want to make the purge process much quicker. My answer (so far) is to
>break down each of the 8 tables into 6 3-month pieces with an additional
>3-month 'incremental' piece.
>
>We will simply truncate the oldest 'piece' of the table and rebuild it
>larger or smaller if needed. We'll then ex/import the incremental data
>into the now trucated base piece, and then truncate the incremental.
>
>My questions (about time) are, 1) Will extending the view from 2
>(base+incremental) tables to 7 (6 base+1 incremental) slow performance
>significantly? and 2) Anyone have any better ideas than this one to
>shorten the history purge?
>
>Note that the database is updated twice a week and only hit on batch
>production runs 2-3 times a week. It's not an overly-active database,
>as in an on-line system would experience.
>
>Thanks much!
>
>-Bruce Bristol
>bbristol_at_ix.netcom.com

If you are using Oracle 7.3 you can try partitioned views. It is described in the back of the performance guide. Using constraints will allow the optimizer to eliminate tables that are not needed. There is an undocumented parameter (the name escapes me now) that is needed.

Good Luck

Mark Rosenbaum			Otey-Rosenbaum & Frazier, Inc.
mjr_at_netcom.com			Consultants in High Performance and
(303) 727-7956			Scalable Computing and Applications
POB 1397			ftp://ftp.netcom.com/pub/mj/mjr/resume/

Boulder CO 80306 Received on Tue Jan 28 1997 - 00:00:00 CST