Re: Login Trigger

From: Mladen Gogala <mladen_at_wangtrading.com>
Date: Fri, 12 Mar 2004 11:43:24 -0500
Message-ID: <20040312164324.GA2311@mladen.wangtrading.com>

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.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
> Now I am lost... can you perhaps provide some steps?
>
> Thanks,
> maa
>
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>

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 - 10:40:34 CST