Home » SQL & PL/SQL » SQL & PL/SQL » materialized view
materialized view [message #190191] Tue, 29 August 2006 11:13 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member

all --

I am struggling Sad Our main query is running very slow (we have optimized it all we could)...So, I am thinking to create a materialized view to increase
the performance. The thing is the query is complicated and it needs to be refreshed as frequenlty as maybe every hour or so. From the documentation it looks like there are limitations to refreshing a view that uses a complex query. Here is my query:

CREATE MATERIALIZED VIEW webserviceEmplData REFRESH FAST AS
SELECT DISTINCT spriden_pidm, spriden_id,
spriden_last_name,
spriden_first_name, spriden_mi, spbpers_ssn,
spbpers_name_prefix, spbpers_name_suffix,
spbpers_birth_date,
spbpers_ethn_code, spbpers_sex,
TO_CHAR (TRUNC ((SYSDATE - spbpers_birth_date) / 365,0)) age,
pebempl_adj_service_date, pebempl_current_hire_date,
pebempl_empl_status, pebempl_orgn_code_home,
pebempl_ecls_code, pebempl_bcat_code,
pebempl_current_hire_date, pebempl_adj_service_date
FROM spriden,
spbpers,
nbrbjob,
pebempl,
nbbposn,
(SELECT MAX (nbrjobs_effective_date) AS max_date,
nbrjobs_pidm, nbrjobs_posn, nbrjobs_suff,
nbrjobs_ecls_code
FROM nbrjobs
WHERE nbrjobs_effective_date <= SYSDATE
GROUP BY nbrjobs_pidm, nbrjobs_posn,
nbrjobs_suff, nbrjobs_ecls_code) last_job

WHERE spriden_pidm = spbpers_pidm
AND spriden_pidm = pebempl_pidm
AND spriden_change_ind IS NULL
AND nbrbjob_pidm = pebempl_pidm
AND (nbrbjob_end_date IS NULL OR
nbrbjob_end_date >= SYSDATE)
AND nbrbjob_posn = last_job.nbrjobs_posn
AND nbrbjob_suff = last_job.nbrjobs_suff
AND nbrbjob_pidm = last_job.nbrjobs_pidm
AND nbbposn_posn = last_job.nbrjobs_posn

What options of materialized view do I use to accomplish the refresh of say every hour or so? I keep hitting ORA-12015 Cannot create a fast refresh materialized view from complex query. Also, the documentation states that if you have a complex query, you need to create some logs first. Which logs?

thank you for any intput!
Re: materialized view [message #190256 is a reply to message #190191] Tue, 29 August 2006 22:34 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Forget a Fast Refresh MV. It's not going to happen with that Inline Query in the FROM clause. Back to tuning.

It looks like you want all of the spriden rows with NULL spriden_change_ind.
- How may rows is that normally?
- What proportion of the table does it represent?
- How fast does it run now?
- How fast does it need to run? ie. What is the slowest possible performance you will accept?

If there are fewer than 100,000 NULL spriden_change_ind, then I think we will be able to tune it up a bit.

Answer the questions above, AND post the Explain Plan for the query taken from your production database (or wherever it is running slow).

Ross Leishman
Previous Topic: " put_line " Sisters :-)
Next Topic: can we update CLOB column?
Goto Forum:
  


Current Time: Wed Dec 07 14:50:33 CST 2016

Total time taken to generate the page: 0.07090 seconds