1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Performance Improvement Of Materialized View Query

Discussion in 'SQL PL/SQL' started by lokeshbao87, Jan 30, 2014.

  1. lokeshbao87

    lokeshbao87 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi Friends,


    I have a question on improving performance on a materialized view.

    We have a product with 12 Million records in it. Product ID is primary key on product table.

    There is a another column in product table called Family ID , this column will help us to group similar products.

    Say there are 10 related products, parent product will have null value in family key column and rest of 9 products will have product id of parent product as family key.

    Now I am creating a materialized view.

    select a.product_id,a.title,b.product_id,b.title from product a, product b where a.product_id=b.family_id.

    When I see the explain plan it does a full table scan of 12 M records, it's hurting badly the performance.

    Will adding an index to family Id , improve the performance ?

    Any suggestions please ???

    Thanks,
    Lokesh
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No. You have no restricting values in a where clause so adding an index won't help any:


    Code (SQL):

    SQL>
    SQL> CREATE TABLE product(
      2        product_id      NUMBER,
      3        title   varchar2(40),
      4        family_id      NUMBER,
      5        CONSTRAINT product_pk PRIMARY KEY (product_id));


    TABLE created.


    SQL>
    SQL> BEGIN
      2        FOR i IN 1..10000 loop
      3         INSERT INTO product
      4         VALUES(i, 'Product '||i, MOD(i,7739)+1);
      5        END loop;
      6  
      7        commit;
      8  END;
      9  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> EXEC dbms_stats.gather_schema_stats('GRIBNAUT');


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SET autotrace ON
    SQL>
    SQL> SELECT a.product_id,a.title,b.product_id,b.title FROM product a, product b WHERE a.product_id=b.family_id;


    PRODUCT_ID TITLE                                    PRODUCT_ID TITLE
    ---------- ---------------------------------------- ---------- ----------------------------------------
           304 Product 304                                     303 Product 303
           305 Product 305                                     304 Product 304
    ...
          2258 Product 2258                                   9996 Product 9996
          2259 Product 2259                                   9997 Product 9997
          2260 Product 2260                                   9998 Product 9998
          2261 Product 2261                                   9999 Product 9999
          2262 Product 2262                                  10000 Product 10000


    10000 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1465204614
    ------------------------------------------------------------------------------
    | Id  | Operation          | Name    | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |         | 10000 |   371K|    27   (4)| 00:00:01 |
    |*  1 |  HASH JOIN         |         | 10000 |   371K|    27   (4)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| PRODUCT | 10000 |   166K|    13   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| PRODUCT | 10000 |   205K|    13   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - access("A"."PRODUCT_ID"="B"."FAMILY_ID")

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            757  consistent gets
              0  physical reads
              0  redo SIZE
         470900  bytes sent via SQL*Net TO client
           7845  bytes received via SQL*Net FROM client
            668  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
          10000  ROWS processed


    SQL>
    SQL> SET autotrace off
    SQL>
    SQL> CREATE INDEX product_fam_idx ON product(family_id);


    INDEX created.


    SQL>
    SQL> SET autotrace ON
    SQL>
    SQL> SELECT a.product_id,a.title,b.product_id,b.title FROM product a, product b WHERE a.product_id=b.family_id;


    PRODUCT_ID TITLE                                    PRODUCT_ID TITLE
    ---------- ---------------------------------------- ---------- ----------------------------------------
           304 Product 304                                     303 Product 303
           305 Product 305                                     304 Product 304
           306 Product 306                                     305 Product 305
    ...
          2259 Product 2259                                   9997 Product 9997
          2260 Product 2260                                   9998 Product 9998
          2261 Product 2261                                   9999 Product 9999
          2262 Product 2262                                  10000 Product 10000


    10000 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 1465204614
    ------------------------------------------------------------------------------
    | Id  | Operation          | Name    | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |         | 10000 |   371K|    27   (4)| 00:00:01 |
    |*  1 |  HASH JOIN         |         | 10000 |   371K|    27   (4)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| PRODUCT | 10000 |   166K|    13   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| PRODUCT | 10000 |   205K|    13   (0)| 00:00:01 |
    ------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - access("A"."PRODUCT_ID"="B"."FAMILY_ID")

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            757  consistent gets
              0  physical reads
              0  redo SIZE
         470900  bytes sent via SQL*Net TO client
           7845  bytes received via SQL*Net FROM client
            668  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
          10000  ROWS processed


    SQL>
     

    If you restrict the input to the join it may use an index:


    Code (SQL):

    SQL> SELECT a.product_id,a.title,b.product_id,b.title FROM product a, product b WHERE a.product_id=b.family_id AND a.product_id < 701;


    PRODUCT_ID TITLE                                    PRODUCT_ID TITLE
    ---------- ---------------------------------------- ---------- ----------------------------------------
           304 Product 304                                     303 Product 303
           305 Product 305                                     304 Product 304
           306 Product 306                                     305 Product 305
    ...
           693 Product 693                                    8431 Product 8431
           694 Product 694                                    8432 Product 8432
           695 Product 695                                    8433 Product 8433
           696 Product 696                                    8434 Product 8434
           697 Product 697                                    8435 Product 8435
           698 Product 698                                    8436 Product 8436
           699 Product 699                                    8437 Product 8437
           700 Product 700                                    8438 Product 8438


    1399 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 377995378
    -------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name       | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |            |   709 | 26942 |    20   (5)| 00:00:01 |
    |*  1 |  HASH JOIN                   |            |   709 | 26942 |    20   (5)| 00:00:01 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| PRODUCT    |   700 | 11900 |     6   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | PRODUCT_PK |   700 |       |     3   (0)| 00:00:01 |
    |*  4 |   TABLE ACCESS FULL          | PRODUCT    |   905 | 19005 |    13   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - access("A"."PRODUCT_ID"="B"."FAMILY_ID")
       3 - access("A"."PRODUCT_ID"<701)
       4 - FILTER("B"."FAMILY_ID"<701)

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            145  consistent gets
              0  physical reads
              0  redo SIZE
          64356  bytes sent via SQL*Net TO client
           1542  bytes received via SQL*Net FROM client
             95  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
           1399  ROWS processed


    SQL>
     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Even if you use the family_id column to restrict the output it doesn't use the index on family_id:


    Code (SQL):

    SQL> SELECT a.product_id,a.title,b.product_id,b.title FROM product a, product b WHERE a.product_id=b.family_id AND b.family_id < 701;


    PRODUCT_ID TITLE                                    PRODUCT_ID TITLE
    ---------- ---------------------------------------- ---------- ----------------------------------------
             2 Product 2                                         1 Product 1
             3 Product 3                                         2 Product 2
             4 Product 4                                         3 Product 3
    ...
           403 Product 403                                    8141 Product 8141
           404 Product 404                                    8142 Product 8142
           405 Product 405                                    8143 Product 8143
           406 Product 406                                    8144 Product 8144
           407 Product 407                                    8145 Product 8145


    1399 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 377995378
    -------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name       | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |            |   709 | 26942 |    20   (5)| 00:00:01 |
    |*  1 |  HASH JOIN                   |            |   709 | 26942 |    20   (5)| 00:00:01 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| PRODUCT    |   700 | 11900 |     6   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | PRODUCT_PK |   700 |       |     3   (0)| 00:00:01 |
    |*  4 |   TABLE ACCESS FULL          | PRODUCT    |   905 | 19005 |    13   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - access("A"."PRODUCT_ID"="B"."FAMILY_ID")
       3 - access("A"."PRODUCT_ID"<701)
       4 - FILTER("B"."FAMILY_ID"<701)

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            145  consistent gets
              0  physical reads
              0  redo SIZE
          64356  bytes sent via SQL*Net TO client
           1543  bytes received via SQL*Net FROM client
             95  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
           1399  ROWS processed


    SQL>
     

    most likely due to the presence of the primary key index.
     
  4. lokeshbao87

    lokeshbao87 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for your reply David.

    I can't use the filters on the materialized view statement , I wanted to see every record in product table in the materialized view with a product groupings.

    Also I tried to set Refresh Fast On Demand, so that materialized view will be refreshed based on only the data changes that have occurred in the master table only.

    But it gave me error saying " can't do a fast refresh on complex query ".

    I created a materialized view log on source table too.

    Thanks,
    Lokesh
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    you can show a script of creation of MV ?