I have a table that holds information about different events, for example
CREATE TABLE events (
id int not null primary key,
event_date date, ...
)
I realized that 90% of all queries access only today events; the older rows are stored for history and eventually moved to an archive table.
However, events table is still large, and I wonder if I can improve the performance by creating a materialized view that has something like WHERE event_date = trunc(sysdate)
and maybe index on event_date
? Is it allowed at all?
Thanks
yes this is allowed see "primary key materialized view":
Primary key materialized views may contain a subquery so that you can
create a subset of rows at the remote materialized view site
and "complex materialized view"
If you refresh rarely and want faster query performance, then use
Method A (complex materialized view).
If you refresh regularly and can sacrifice query performance, then use Method B (simple materialized view).
at http://download.oracle.com/docs/cd/B10500_01/server.920/a96567/repmview.htm
In your example chances are good IMHO that this is not a "complex materialized view":
CREATE MATERIALIZED VIEW events_today REFRESH FAST AS
SELECT * FROM EVENT WHERE event_date = trunc(sysdate);
Just try it and see if Oracle accepts it with the REFRESH FAST
clause.
EDIT - another option:
Depending on your DB Edition (Enterprise + Partitioning) and Version (11gR2) you could use a new Oracle feature called INTERVAL partitioning to define "daily partitions" within the existing table. This way most of your queries get alot faster without effectively duplicating the data - see http://www.oracle.com/technetwork/database/options/partitioning/twp-partitioning-11gr2-2009-09-130569.pdf