Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am collecting spatial point data that has an associated timestamp with it. I want to create a view of this data for the last 24 hour period. Because I am piping this spatial data through ArcGIS server, there are performance problems related to just piping in a straight view (this view can contain easily 100-200k+ points). To make a long story short, to mitigate these performance issues, I have created materialized views of the data. However, I want this data to be updated every 3 - 4 minutes.

My question is, when I add the current_timestamp to the query:

SELECT * 
FROM points 
WHERE timestamp > current_timestamp - interval '24' hour

...this no longer can be used as a fast refresh. Is there any way to correct that, or will it always be a complete refresh? If shown, what should I be mindful of when it comes to the performance of updating the materialized view (again, this dataset can easily be 100-200k points with 3 - 6 points added every second).

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

A fast-refreshable materialized view cannot contain a non-deterministic function like current_timestamp. So if you want to materialize the data from the last 24 hours in a materialized view, the materialized view would need to do a complete refresh every time.

Do you need a materialized view? Could you maintain your own staging table and create a custom job that runs every few minutes, deletes the data that is now more than 24 hours old, and inserts the new data (either by directly querying the table or creating a trigger that writes the new data to a different table)? That's likely a non-starter if you need to use the query rewrite functionality of a materialized view but if you just want the benefit of having a smaller table to query, custom code may be more efficient.

Or could you partition the table by day so that queries for the past 24 hours always just have to hit the two most recent partitions?

share|improve this answer
    
I am working on that kind of solution right now (just make a copy of the row into a table then run a delete command every few minutes). Was just wondering if I was nuts or something. Thanks! –  baens Dec 12 '12 at 20:04
2  
Make sure you consider the effect this will have on redo. –  Leigh Riffel Dec 12 '12 at 20:17
1  
Wouldn't an IOT be a valid alternative (with the timestamp as part of the primary key)? –  ypercube Dec 12 '12 at 20:53
    
@ypercube - Possibly, sure. I would expect, though, that the spatial data itself would need to be stored outside the index in the overflow segment in which case you'd still end up doing a range scan of the IOT's index and then doing a lot of single-row reads of the overflow segment. That's probably a bit more efficient than a heap-organized table but I wouldn't expect the performance difference to be large –  Justin Cave Dec 12 '12 at 21:00
add comment

You could create a materialized view containing data just from the last 24 hours. To do so, you'll need to create a single row table containing just the date after which you want data to be returned however. Your refresh process will need to update this table to the date you want before doing the fast refresh:

create table tab (x integer primary key, ins_date date);

create table last_time (datetime date primary key);

create materialized view log on tab with rowid;
create materialized view log on last_time with rowid;

insert into tab
select rownum, sysdate-level/20 from dual connect by level <= 100;

insert into last_time values (sysdate-1);

commit;

create materialized view mv
build immediate
refresh fast on demand
as
  select t.*, t.rowid tr, l.rowid lr
  from   tab t, last_time l
  where  t.ins_date >= l.datetime;

select count(*) from mv;

  COUNT(*)
----------
        20 

update last_time set datetime = sysdate;

exec dbms_mview.refresh('mv', 'F');

SELECT x FROM mv;

no rows selected

insert into tab values (101, sysdate);

exec dbms_mview.refresh('mv', 'F');

SELECT x FROM mv;

         X
----------
       101

If you find you want more (or less) data than 24 hours you can then just update your LAST_TIME table as appropriate.

It's worth looking into partitioning as well though, as Justin's suggested.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.