This discussion is archived
6 Replies Latest reply: Feb 20, 2013 10:40 AM by phanimarella RSS

Materialized view Indexes questions..

phanimarella Newbie
Currently Being Moderated
Hi all..


I created a ""FAST REFRESH"" materialized view on a ""remote database table"" which has 100 million records.
Previously i have a query that joins the local_db table and remote_table via db_link.
After i created the MV, i am using MV to joing in this query.



1) The performance of the query didn't change much after i used MV . I was in the impression that the MV stays locally
and improves the peformace. Is there anything that i can do?

2) I am planning to create indexes on the MV. Does it improves the performance?
Do i need to drop and create the MV, if i create indexes on it?
I don't want to drop and create the MV all the time. Is there any other options that i have?


EXPLAIN PLAN WITH MV:
====================
SELECT STATEMENT, GOAL = ALL_ROWS      198190  45  2070
 FILTER          
  SORT AGGREGATE        1  23
   TABLE ACCESS BY INDEX ROWID  VP_OWNER  VIOLATORS  4  1  23
    INDEX RANGE SCAN  VP_OWNER  VLR_PLATE  3  1  
 HASH GROUP BY      198190  45  2070
  VIEW  SNALLADB    198189  45  2070
   HASH UNIQUE      198189  45  7245
    WINDOW SORT      198189  45  7245
     HASH JOIN OUTER      198187  45  7245
      PARTITION RANGE SINGLE      4520  44  6028
       TABLE ACCESS FULL  VP_OWNER  VIOLATIONS  4520  44  6028
      MAT_VIEW ACCESS FULL  VP_OWNER  PLATES_MV  193637  8310922  199462128
EXPLAIN PLAN WITH JOINING TO REMOTE TABLE:
==========================================
SELECT STATEMENT, GOAL = ALL_ROWS      4699  44  2024
 FILTER          
  SORT AGGREGATE        1  23
   TABLE ACCESS BY INDEX ROWID  VP_OWNER  VIOLATORS  4  1  23
    INDEX RANGE SCAN  VP_OWNER  VLR_PLATE  3  1  
 HASH GROUP BY      4699  44  2024
  VIEW  SNALLADB    4698  44  2024
   HASH UNIQUE      4698  44  7348
    WINDOW SORT      4698  44  7348
     NESTED LOOPS OUTER      4696  44  7348
      PARTITION RANGE SINGLE      4520  44  6028
       TABLE ACCESS FULL  VP_OWNER  VIOLATIONS  4520  44  6028
      REMOTE    PLATES  4  1  30
QUERY:
=====
 
 select viol_date, dmv_sts, lane_id, count(1) cnt, sum(rev) revenue
   from (select violation_id,
                trunc(viol_date) viol_date,
                lane_id,
                rev,
                'LP-' || (case
                  when dmv_sts in ('NDMV', 'NV-TIME') then
                   dmv_sts
                  when exists (select count(1)
                          from violators vr
                         where vr.lic_plate_nbr = vt.lic_plate_nbr
                           and vr.lic_plate_state = vt.lic_plate_state
                           and vt.viol_date between vr.usage_begin_date and
                               nvl(vr.usage_end_date, sysdate)
                         having count(1) > 1) then
                   'M-VLTR'
                  else
                   'OTHER'
                end) dmv_sts,
                business_type
           from (SELECT DISTINCT v.violation_id,
                                 v.viol_date,
                                 v.lane_id,
                                 v.toll_due rev,
                                 v.lic_plate_nbr,
                                 v.lic_plate_state,
                                 DECODE(v.origin_type,
                                        'F',
                                        'Z',
                                        v.origin_type) business_type,
                                 MIN(case
                                       when p.lic_plate_nbr is null THEN
                                        'NDMV'
                                       when p.start_date is not null and
                                            v.viol_date between p.start_date and
                                            nvl(p.end_date, sysdate) THEN
                                        'IN-DMV'
                                       ELSE
                                        'NV-TIME'
                                     end) over(PARTITION BY violation_id) dmv_sts
                   FROM violations v
                   LEFT OUTER JOIN --plates@home_dmv.world
                 vp_owner.plates_mv p -- *** I am using the MV over there
                     ON v.lic_plate_nbr = p.lic_plate_nbr
                    AND v.lic_plate_state = p.lic_plate_state
                  WHERE v.viol_date >= to_date('7/5/2007', 'MM/DD/YYYY')
                    AND v.viol_date < to_date('7/31/2007', 'MM/DD/YYYY') + 1
                    AND v.viol_status IN ('ZH', 'WJ', 'A')
                    and v.violator_id is null 
                    and v.lic_plate_state = 'TX') vt)
  group by viol_date, dmv_sts, lane_id, business_type
Thank u
  • 1. Re: Materialized view Indexes questions..
    phanimarella Newbie
    Currently Being Moderated
    Can anyone please help me with this issue..
  • 2. Re: Materialized view Indexes questions..
    rp0428 Guru
    Currently Being Moderated
    You've posted more than enough times to know that you need to provide your 4 digit Oracle version.
    >
    1) The performance of the query didn't change much after i used MV . I was in the impression that the MV stays locally
    and improves the peformace. Is there anything that i can do?
    >
    Yes - drop the MV. Why did you create an MV if your testing showed it didn't provide any benefit?
    >
    2) I am planning to create indexes on the MV. Does it improves the performance?
    Do i need to drop and create the MV, if i create indexes on it?
    I don't want to drop and create the MV all the time. Is there any other options that i have?
    >
    Why are you 'planning to create indexes on the MV'? Your statement in #1 is telling you that the MV isn't providing any benefit.
    >
    Can anyone please help me with this issue..
    >
    What issue? You haven't presented any issue to help with.

    You need to STOP focusing on a solution and get back to determining what the problem is or if you even have a problem.

    1. Determine and document that a problem exists.
    2. Determine the cause of the problem
    3. Identify possible solutions that will eliminate/mitigate the problem
    4. Select one or two solutions for further evaluation and testing
    5. Implement your 'best' solution

    You seem to be on step #5 but haven't posted anything that indicates you have done steps 1 thru 4.

    Post detailed information about steps 1 and 2.
  • 3. Re: Materialized view Indexes questions..
    phanimarella Newbie
    Currently Being Moderated
    Hi rp..

    My database version. Sorry about it, i didn't mentioned it in the previous post.
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    rp.. I have alread did my analysis before posting over here,and gone through several blogs and material.
    I have a query which is acsessing a ""remote table".
    If i remove that part, the query is so fast. So i thought joining to the remote table is causing the issue.
    To avoid joining to a remote table, i created a materialized view locally. But it doesn't help much. My query is doing a
    "FULL table scan" on the MV. That's why i am asking whether if i add indexes to MV might help me or not?
    I read it from some blogs, that indexes on MV's becomes stale as they get refreshed everyday.

    {code}
    What issue? You haven't presented any issue to help with.
    {code}
    I am trying to improve the performance of my query rp and MV.

    anyway..thanks a ton for the reply..
  • 4. Re: Materialized view Indexes questions..
    rp0428 Guru
    Currently Being Moderated
    >
    I have alread did my analysis before posting over here,and gone through several blogs and material.
    >
    Well, where is it? We can only comment based on what you post. You didn't provide any of that info or provide any links to other threads that have that info.
    >
    I have a query which is acsessing a ""remote table".
    If i remove that part, the query is so fast. So i thought joining to the remote table is causing the issue.
    {quote}
    Terms like 'fast' and 'slow' don't mean anything. We can only go by information about actual time and numbers of rows involved and you didn't post any of that. You also didn't post any information about any difference in time for your 'fast' versus 'slow' queries.
    {quote}
    To avoid joining to a remote table, i created a materialized view locally. But it doesn't help much. My query is doing a
    "FULL table scan" on the MV. That's why i am asking whether if i add indexes to MV might help me or not?
    I read it from some blogs, that indexes on MV's becomes stale as they get refreshed everyday.
    {quote}
    An MV is a table so adding indexes to an MV can have the same benefits, and the same problems, as adding an index on a table.

    Indexes don't become 'stale'. And, again, we can't comment on 'some blogs' or even try to explain what was meant since you didn't provide the link to any blog so we could read them.

    If you have indexes on an MV and then do a complete refresh of the MV you will have the same performance issues for the refresh as you would for any other table that has indexes that you insert a lot of data into. The row-by-row update of the indexes will slow down the load process.

    So, just as you might for a regulat table, you might want to consider dropping the indexes before the complete refresh and then rebuilding them afterward.

    You will need to perform testing to see which is better for your use case and number of indexes.
  • 5. Re: Materialized view Indexes questions..
    phanimarella Newbie
    Currently Being Moderated
    Thank u rp.. I will try to add the indexes and see how it changes the performance..
  • 6. Re: Materialized view Indexes questions..
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    to begin with, what is what in the plan you posted? which column is the cost, and which is cardinality? and what is the third column -- bytes? Also, does your plan give a realistic idea of the cost (is it close to the number of consistent gets the query takes? you can find this information in autotrace output or by querying V$SQL).

    Regarding indexes: yes, I think a B-tree index on (lic_plan_state, lic_plan_nbr) would be helpful, because that way you can replace the HASH JOIN with a NESTED LOOP driven by a hopefully smaller dataset (rows from "violations" table after applying numerous predicates). However, in order to say for sure we need more information, e.g. dbms_xplan.display_cursor output for the query after running it with STATISTICS_LEVEL = ALL or gather_plan_statistics hint.

    Best regards,
    Nikolay

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points