Re: MATERIALIZED VIEW - Need Advice

pankaj_wolfhunter_at_yahoo.co.in wrote:
> Greetings,
> In our application, we have a several tables which gets loaded with
> millions of records per day
> and we have to run reporting queries on these tables which involves lot
> of aggregations.
> I just heard of materialized views.
>
> For now our queries run on base tables.
> I am thinking of suggesting the idea to go for MATERIALIZED VIEW which
> precomputes
> the sum and increases the performance. Our is an totally Data-warehouse
> kinda application.
>
> Just want to know the comments/suggestions from experts before
> suggesting this.
>
> let me know if something else is required.
>
> Any help would be appreciated.
>
> TIA
>
> DB Version:
>
> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
> PL/SQL Release 10.2.0.1.0 - Production

On a non-production system try this:

  DECLARE
  task_name VARCHAR2(30) := 'UW Task';
BEGIN
   dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor, task_name,    'SELECT AVG(amount_sold) FROM sales WHERE promo_id=350'); END;
/

Replace my SELECT statement with one of yours.

Then ...

SELECT command, attr1, attr3, attr4
FROM user_advisor_actions
WHERE task_name = 'UW Task';

set long 100000

SELECT attr5
FROM user_advisor_actions;
WHERE task_name = 'UW Task';

exec dbms_advisor.delete_task('UW Task');

Contact your Oracle sales rep if you would like to be able to do this in production. Ask about the Tuning Pack.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org