OraFAQ Forum: Performance Tuning » MV when detailed table consists of Millions of records

Home » RDBMS Server » Performance Tuning » MV when detailed table consists of Millions of records

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

MV when detailed table consists of Millions of records [message #482020]

Tue, 09 November 2010 02:43

ind9
Messages: 65
Registered: January 2009

Member

Hi All,
I'm facing performance issue in our application which is similar to employee search.

We have a screen consists of 3 major tables fields based on the user inputs we are framing dynamic queries and fetching the desired output with the help of views which is a join of all the major 3 tables. Anyways view are not useful search on employee name takes more than 15 minutes. So to gain performance I would like to implement materialized views.

view structure is

  create or replace view emp_dept_loc
     as select 
         emp.*, dept.loc_id, loc.city, loc.zip, dept.dept_name
        From employees emp, department dept, locations loc
         Where emp.dept_no = dept.dept_no
           AND dept.loc_id = loc.loc_id;

Now creating a MV as

CREATE MATERIALIZED VIEW MV_EMP_DEPT_LOC
  PARALLEL
  TABLESPACE "large" 
  PARTITION BY list (dept_no)
  (PARTITION "mv_dept_1"  VALUES (1) 
  TABLESPACE "large"  , 
 PARTITION "mv_dept_2"  VALUES (2) 
  TABLESPACE "large"  , 
 PARTITION "mv_dept_3"  VALUES (3) 
  TABLESPACE "large"  , 
 PARTITION "mv_dept_4"  VALUES (4) 
  TABLESPACE "large"  , 
 PARTITION "mv_dept_5"  VALUES (5) 
   TABLESPACE "large"  , 
 PARTITION "mv_dept_6"  VALUES (6)
  TABLESPACE "large"  , 
 PARTITION "mv_dept_7"  VALUES (7)
  TABLESPACE "large"  , 
 PARTITION "mv_dept_8"  VALUES (8)  
  TABLESPACE "large"   ) 
BUILD DEFERRED
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE AS 
SELECT emp.rowid emp_rowid, dept.rowid dept_rowid, loc.row_id loc_rowid,
         emp.*, dept.loc_id, loc.city, loc.zip, dept.dept_name
        From employees emp, department dept, locations loc
         Where emp.dept_no = dept.dept_no
           AND dept.loc_id = loc.loc_id

I'm relatively new to materialized views so could you please guide me on the following points

1. In the above detailed tables employee and departments are partitioned on department ID but locations table is not a partioned one .
So is it madatory to create a Materialized view log when the materialized view is partitioned and refresh mode is FAST ON COMMIT

2. I'm enabling query rewrite so due to this will it impact on performance?

3. what is use of parallel option here?

4. Detailed table of employee is frequently updatable so how can I improve performance?

5. As per my understanding logs are not required when PCT is enabled but when I'm MV as shown above getting error as

SQL Error: ORA-23413: table "HR"."locations" does not have a materialized view log
23413. 00000 -  "table \"%s\".\"%s\" does not have a materialized view log"
*Cause:    The fast refresh can not be performed because the master table
           does not contain a materialized view log.
*Action:   Use the CREATE MATERIALIZED VIEW LOG command to create a
           materialized view log on the master table.

Please guide me on the above points.

Appreciate your help on this.

Thanks in advance

Regards,
Ind9

Report message to a moderator

Re: MV when detailed table consists of Millions of records [message #482021 is a reply to message #482020]

Tue, 09 November 2010 02:49

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

1.Why do you think that you need MV? IMHO your problem is the query by-emp-name response time.
Post EXPLAIN plan of such a query.

2. You did define an index on emp table over emp_name column, didn't you?

Report message to a moderator

Re: MV when detailed table consists of Millions of records [message #482024 is a reply to message #482021]

Tue, 09 November 2010 02:56

ind9
Messages: 65
Registered: January 2009

Member

Thanks for your quick reply.
My problem is across the search screen not on emp-name alone. emp-name is an example that i've highlighted. When ever partitioned column i.e. dept_no is not provided in the search screen, search is going across all the partitions.

Tuning queries hasn't helped us so we thought of moving to MV.

Because of this I would like to implement MV concept which actually stores all these table information at one place. So search will be faster.

Report message to a moderator

Re: MV when detailed table consists of Millions of records [message #482041 is a reply to message #482024]

Tue, 09 November 2010 04:24

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

I still don't see the problem. Post example of such query and it's explain plan.
Post index definitions as well.

Report message to a moderator

Re: MV when detailed table consists of Millions of records [message #482045 is a reply to message #482024]

Tue, 09 November 2010 04:37

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Materialized views are normally used to store aggregate data. It is unlikely they're going to accomplish much in your case. Suggest you post the explain plan for the problem query as Michael already asked.
How many records in each of the three tables?

Report message to a moderator

Re: MV when detailed table consists of Millions of records [message #482049 is a reply to message #482045]

Tue, 09 November 2010 04:47

ind9
Messages: 65
Registered: January 2009

Member

Thanks for your information. I would consider all your suggestions and would try to implement.

Each of the table consists of

employee - 4 million
department - 2 Million
Locations - 8 Million

Anyways I've to demonstrate the performance using MVs to clients so could you please suggest on this?

Report message to a moderator

Previous Topic:	Query working very slow
Next Topic:	query problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 07:10:13 CST 2024