Home » SQL & PL/SQL » SQL & PL/SQL » materialized view
|materialized view [message #190191]
||Tue, 29 August 2006 11:13
Registered: March 2005
I am struggling 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_first_name, spriden_mi, spbpers_ssn,
TO_CHAR (TRUNC ((SYSDATE - spbpers_birth_date) / 365,0)) age,
(SELECT MAX (nbrjobs_effective_date) AS max_date,
nbrjobs_pidm, nbrjobs_posn, nbrjobs_suff,
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
Registered: October 2005
Location: Melbourne, Australia
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).
Current Time: Tue Apr 25 19:51:39 CDT 2017
Total time taken to generate the page: 0.13241 seconds