| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Login Triggers
How about the materialized view log.
Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu
-----Original Message-----
From: Nick Gabrielatos [mailto:nickgabriel_at_usa.net]
Sent: Friday, March 12, 2004 9:39 AM
To: oracle-l_at_freelists.org
Subject: Re: Login Triggers
Thanks, that helps quite a bit.
Let me ask you this, though,
you think we should use cashed tables and materialized views instead of login triggers?
Wouldnt a login trigger have an advantage?
thanks,
maa
At 11:43 AM 3/12/2004, you wrote:
>Maryan, materialized views are pre-built queries whose results are
>stored in a table and refreshed periodically. Here is slightly modified
>example from the manuals:
>
>CREATE TABLE sales_sum_table
> (month VARCHAR2(8), state VARCHAR2(40), sales NUMBER(10,2))
> STORAGE ( BUFFER_POOL KEEP);
>
>CREATE MATERIALIZED VIEW sales_sum_table
> ON PREBUILT TABLE
> REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/(24*60)
> ENABLE QUERY REWRITE
> AS SELECT t.calendar_month_desc AS month,
> c.cust_state_province AS state,
> SUM(s.amount_sold) AS sales
> FROM times t, customers c, sales s
> WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
> GROUP BY t.calendar_month_desc, c.cust_state_province
>
>
>As it says, the view is refreshed periodically, in this case once a
>minute. Results are stored in the table, which is, in turn, cached in
>the KEEP buffer pool.
>
>On 03/12/2004 11:28:36 AM, Maryann Atkinson wrote:
> > Thanks for the response
> >
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > > > It sounds like you want to prerun the user's query. While this
> > > > is possible from a technical point of view, it is not a wise idea.
> > >
> > >Actually, it is. It is called "materialized view" and is done on
> > >regular basis.
> > >If you create it on prebuilt table which is in the "KEEP" pool,
> > >you
> get as
> > >close to pre-caching the results of the desired query as possible.
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Mar 12 2004 - 12:00:19 CST
 |
 |