All Full table scans in Database

Below Query will give Query text for which Full table scan occurs. This query will give result based on last 5 days.

select s.sql_text,db.operation,db.options,cpu_cost
  from dba_hist_sql_plan db ,dba_hist_sqltext s
      where  db.operation =  'TABLE ACCESS'
      and db.options = 'FULL'
      and s.sql_id = db.sql_id
      and db.object_owner = 'USER'
 --    order by db.timestamp desc
      and trunc(db.timestamp)  >= trunc(sysdate) -5
      order by cost desc;

Materialized view refreshing methods.

Mview Refresh Methods:

1) Fast: This method is for Increment refresh. We need to create log on base table to implement this refresh. DML changes on base tables are stored in these logs.

If you specify refresh fast without creating log on base table ,create statement on the base table will be failed.

         CREATE MATERIALIZED VIEW product_sales_mv

         REFRESH FAST

         AS

         SELECT p.prod_name,SUM(amount_sold) AS dollar_sales

         FROM sales s, products p

         WHERE s.prod_id = p.prod_id

         GROUP BY p.prod_name;

2) Complete: It will refresh mview with whole table.

                  CREATE MATERIALIZED VIEW product_sales_mv

         REFRESH COMPLETE

         AS

         SELECT p.prod_name,SUM(amount_sold) AS dollar_sales

         FROM sales s, products p

         WHERE s.prod_id = p.prod_id

         GROUP BY p.prod_name;

3) FORCE: It is default value if you haven’t specified Complete, Fast or Force. It will perform FAST Refresh if possible otherwise it will do complete refresh.

                CREATE MATERIALIZED VIEW product_sales_mv

         REFRESH FORCE

         AS

         SELECT p.prod_name,SUM(amount_sold) AS dollar_sales

         FROM sales s, products p

         WHERE s.prod_id = p.prod_id

     GROUP BY p.prod_name;

4) ON COMMIT: Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. This clause may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.

                   

         CREATE MATERIALIZED VIEW product_sales_mv

         REFRESH FAST ON COMMIT

         AS

         SELECT p.prod_name,SUM(amount_sold) AS dollar_sales

         FROM sales s, products p

         WHERE s.prod_id = p.prod_id

GROUP BY p.prod_name;

5) ON DEMAND: Specify ON DEMAND to indicate that the materialized view will be refreshed on demand by calling one of the three DBMS_MVIEW refresh procedures. If you omit both ON COMMIT and ON DEMAND, ON DEMAND is the default.

                  CREATE MATERIALIZED VIEW product_sales_mv

         REFRESH COMPLETE ON DEMAND

         AS

         SELECT p.prod_name,SUM(amount_sold) AS dollar_sales

         FROM sales s, products p

         WHERE s.prod_id = p.prod_id

         GROUP BY p.prod_name;

You can specify start with or NEXT clause in case of ON COMMIT or ON Demand.

 

If you omit START WITH or NEXT clause then Mview will be refreshed only once, at the time of creation. If you omit NEXT clause MVIEW will not be refreshed further. If you omit START WITH clause, mview will be refreshed from the date specified in NEXT clause.

WITH PRIMARY KEY:

These clauses are used in creating materialized logs.

In this case Mview query should contain all primary key of base table. It cannot use any function on primary key in defining query of the mview.

CREATE MATERIALIZED VIEW LOG ON customers

WITH PRIMARY KEY;

WITH ROWID :

It is useful when defining query doesn’t include all primary key of base table. We cannot use WITH ROWID if defining query is using SET OPERATORS, DISTINCT or Aggregate functions, group by, connect by,subqueries or joins.

CREATE MATERIALIZED VIEW LOG ON customers

WITH ROWID;