2

I've got this really nasty view that I'm trying to make faster by performing some joins ahead of time via materialized views. My problem is the most expensive joins, and therefore most worthwhile to pre-execute, don't play nice with materialized views.

Goal of the application is to provide livest data possible, so if I make mat views, they need to fast refresh on commit(maybe I haven't considered other approaches I'm unaware of). Fast refresh has limitations, specifically you must have rowid. See this thread here; but my problem is a little different as the nature of my join requires me to aggregate my join to get the right record.

Here's what I want to "pre-execute" (or optimize another genius way):

     CREATE MATERIALIZED VIEW testing
     NO LOGGING
     CACHE
     BUILD IMMEDIATE
     REFRESH FAST ON COMMIT 
     AS
       SELECT br.id, br.rowid, max(mr.id) as modifier_id --somehow fit mr.rowid in here
        FROM tableA br --base record
        LEFT OUTER JOIN tableA mr --modifier record
            ON br.external_key = mr.external_key
            AND mr.record_type_code in ('SOME','TYPE')
            AND mr.status_code in ('SOME','STATUS');

Basically, it's a self-join, because 0-*n* modifications get made to the entity, all of which are done in subsequent rows in the same table. I'm selecting the most recent of a given type. (I do this additional times for other types). To get the above working, I'd have to include rowid of both br and mr, which I can't wrap my brain around a way to do. I've considered rank() and ROWNUM instead of aggregating w/ MAX(), but can't get the logic right.

EDIT: Not sure fast refresh MV is in the cards for me as even if I make the refresh on demand and remove the aggregation entirely (assume there is exactly 1 row), oracle tells me the query is too complex for a fast refresh. So, now I'm in need of other ideas...

Community
  • 1
  • 1
GoldPaintedLemons
  • 482
  • 5
  • 9
  • Not sure if this makes any difference, but have you tried a MV on just the modifier records, e.g. `SELECT external_key, id FROM tableA WHERE record_type_code IN ('SOME','TYPE') AND status_code IN ('SOME','STATUS')`, that might cut down on the work required by the original query. – Jeffrey Kemp Jun 05 '13 at 04:54
  • Try replacing the ANSI syntax with the original Oracle join syntax. I hate the (+) syntax, but it is sometimes necessary for materialized views, see http://stackoverflow.com/a/1318667/409172 – Jon Heller Jun 05 '13 at 18:41
  • I did that earlier actually and it did get me a little further, and I was able to make a fast refreshing NON-aggregated mv based on a join, but the mv would be caching a bunch of rows I don't need, and would still have to do the `MAX()` aggregation outside of the mv, in the big nasty view, so it's less of a perf boost. – GoldPaintedLemons Jun 05 '13 at 19:51

2 Answers2

1

It might not be applicable in your situation, but possibly you could denormalize your table.

For example, if you have multiple language dependent names, you could just have named columns for each language.

For example, if your access is index-based, consider varray or nested tables.

Another idea is to use triggers: On insert/update/delete, update another table (or tables), and use that table for the query. Possibly you can pre-calculate aggregates this way as well.

Beryllium
  • 12,808
  • 10
  • 56
  • 86
0

I would look into using a materialised view to do the aggregation only, so you're just storing EXTERNAL_KEY and MAX(ID).

If you have deletes occurring on the master table then include count(*) as well.

That should give you fast refresh capability.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • That's exactly what I need I think, but I've tried simplifying my query and just can't get it compatible with the requisites of a fast refresh. If you could give me some syntax clues, this would be the answer. – GoldPaintedLemons Jun 05 '13 at 14:35
  • Have you looked at the DBMS_MView.Explain_MView procedure? If you can post the output from that it would be helpful diagnostically. http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_mview.htm#CEGGEHHC – David Aldridge Jun 05 '13 at 15:37
  • ok, so I'm gonna close this one down and award it to Beryllium, as ultimately his suggestion led me to a usable solution, but I wanted to thank you for pointing me to this util. I ran it and basically saw a whole host of issues with getting the fast refresh MV to work. One of them just being the fact that I'm using `MAX()`, which I've come to the conclusion is not worth getting around. – GoldPaintedLemons Jun 05 '13 at 18:36
  • Yes no probs -- come to think of it Max() can be tricky, but only during delete from the source table as there is not enough information present in the MView to set a new Max() value if a row that matches that value gets deleted. It all makes sense really when you consider whether MView's have got the data required for various types of fast refresh. Something to consider for the future is that it's quite valid to create an MView that is index-organised, or partitioned, or just a subset of columns, or differently indexed to the master table. – David Aldridge Jun 05 '13 at 18:47
  • and I don't even need it to support deletes (we don't delete as a rule) but I don't think there is a way to trick it into knowing it won't need to work. Explain_MView output: `8 REFRESH_FAST_AFTER_ONETAB_DML *N* MAX(EDPM.ROWID) mv uses the MIN or MAX aggregate functions 9 REFRESH_FAST_AFTER_ONETAB_DML *N* MAX(EDPM.CONTRACT_ID) mv uses the MIN or MAX aggregate functions` – GoldPaintedLemons Jun 05 '13 at 20:00
  • How about REFRESH_FAST_AFTER_INSERT? – David Aldridge Jun 06 '13 at 07:52