From oracle-l-bounce@freelists.org Fri Mar 12 11:36:42 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2CHaf120483 for ; Fri, 12 Mar 2004 11:36:41 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i2CHafo20478 for ; Fri, 12 Mar 2004 11:36:41 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3505E395518; Fri, 12 Mar 2004 12:35:59 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 12 Mar 2004 12:34:31 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from amer-mta02.csc.com (amer-mta02.csc.com [20.137.2.248]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CECFA395355 for ; Fri, 12 Mar 2004 12:34:26 -0500 (EST) Received: from csc.com (va-fch34.csc.com [20.6.39.227]) by amer-mta02.csc.com (Switch-3.1.2/Switch-3.1.0) with ESMTP id i2CHes09023303 for ; Fri, 12 Mar 2004 12:40:54 -0500 (EST) Received: from computer-qd3rtm.yahoo.com ([20.4.114.43]) by csc.com (Lotus Domino Release 6.0.3) with ESMTP id 2004031212411874-10784 ; Fri, 12 Mar 2004 12:41:18 -0500 Message-Id: <5.2.1.1.2.20040312123907.00bc8028@pop.netaddress.com> X-Sender: (Unverified) X-Mailer: QUALCOMM Windows Eudora Version 5.2.1 Date: Fri, 12 Mar 2004 12:39:31 -0500 To: oracle-l@freelists.org From: Maryann Atkinson Subject: Re: Login Triggers Mime-Version: 1.0 X-MIMETrack: Itemize by SMTP Server on VA-FCH34/SRV/CSC(Release 6.0.3|September 26, 2003) at 03/12/2004 12:41:19 PM, Serialize by Router on VA-FCH34/SRV/CSC(Release 6.0.3|September 26, 2003) at 03/12/2004 12:41:19 PM, Serialize complete at 03/12/2004 12:41:19 PM Content-Type: text/plain; charset="us-ascii"; format=flowed X-archive-position: 706 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: maryann_30@yahoo.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l Sorry, I sent this from my husbands id, but it was me. maa 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. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------