| 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
If you had a login trigger that ran a 6 hour query, the user would, by default, not finish logging in until that query was done. You could have the login trigger submit a job that would run this query in the background, but then the user would have to know where to go to look for the results. If users ever do something other than run this query, you would be spending a lot of resources running the query when no one wanted the results.
Materialized views are much, much easier to deal with in this situation. In addition to the fact that they're available to everyone all the time, they get refreshed automatically, you don't have to teach users where to go to get the data, and you don't execute huge queries when you don't need the results.
Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nick Gabrielatos
Sent: Friday, March 12, 2004 10: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 - 11:52:34 CST
 |
 |